[Resolvido] Erro Join Db-Table

[Rafael Beber 0]

Olá Pessoal,

 

Sou iniciante em Zend e estou com o seguinte erro no SQL:

 

Column not found: 1054 Unknown column 'nome_razao' in 'where clause'

 

Criei o select da seguinte forma:

$select = $this->_db->select()

->setIntegrityCheck(false)

->from('fin_conta_pagar')

->join('adm_pessoa',('adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id'),array('nome_razao'))

->where($where)

->order($order)

->limit($limit,$start);

 

$resultSet = $this->_db->fetchAll($select)->toArray();

 

O sql gerado pelo zend:

 

SELECT `fin_conta_pagar`.*, `adm_pessoa`.`nome_razao` FROM `fin_conta_pagar` INNER JOIN `adm_pessoa` ON adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id WHERE (adm_condominio_id = 10 and (nome_razao like('%rafae%') )) ORDER BY `nome_razao` ASC LIMIT 25

 

Seu rodar esse SQL direto no WorkBeach ele rada se erro algum.

 

Estou utilizando MySQL

 

Obrigado.

[Gutoffline 1]

Segue exemplo de como fiz o meu, veja se ajuda:

 

$local = 'L';
$select = $db->select()
->from(array('a' => 'contatos'), '*')
->joinLeft(array('b' => 'fotos'), 
	'a.id = b.id_contato',array('b.id as IdDaFoto') )
->where('a.id_conta=?',$this->id_conta)
->where('a.local=?',$local)
->where('b.id<>?','')
->where('a.ativo=1')
->group('a.id')
->order('rand()')
->limit(4)
;
$stmt = $select->query();
$rows = $stmt->fetchAll();
$this->view->assign('ListaContatos', $rows);

[Rafael Beber 0]

Olá Gutto,

 

O código funciona, se eu remover nome_razao like('%rafae%') da clausulá where.

 

Não consigo fazer a busca pelo nome da pessoa, atravez do controller de contas a pagar.

 

Não sei se ficou claro.

[Gutoffline 1]

Putz cara, desculpa mas não intendi, se puder explicar novamente pf

 

Cara não intendi muito bem, se puder explicar novamente.

[Mayron Ceccon 5]

Tenta usar assim o join

 

->join(array('adm_pessoa'), 'adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id', array('nome_razao'))

 

e posta o que você está passando para sua variável $where

[Rafael Beber 0]

Funciona assim:

 

tenho a tabela Contas a Pagar que possui uma ligação com a tabela PESSOA, cada conta tem uma pessoa vinculada.

 

tenho o grid que lista as contas, neste grid tem uma busca por nome do fornecedor, que é o campo nome_razao na tabela PESSOA.

 

Mayron:

O código abaixo Funciona perfeitamente

$select = $this->_db->select()
                      ->setIntegrityCheck(false)
                      ->from('fin_conta_pagar')
                      ->join('adm_pessoa', 'adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id', array('nome_razao'))
                      ->where('adm_condominio_id = 8')
                      ->order('id_fin_conta_pagar ASC')
                      ->limit(25,0);

SQL gerado

SELECT `fin_conta_pagar`.*, `adm_pessoa`.`nome_razao` FROM `fin_conta_pagar` INNER JOIN `adm_pessoa` ON adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id WHERE (adm_condominio_id = 8) ORDER BY `id_fin_conta_pagar` ASC LIMIT 25

 

O Codigo Abaixo gera o erro :SQLSTATE[42S22]: Column not found: 1054 Unknown column 'nome_razao' in 'where clause'

 

$select = $this->_db->select()
                      ->setIntegrityCheck(false)
                      ->from('fin_conta_pagar')
                      ->join('adm_pessoa', 'adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id', array('nome_razao'))
                      ->where('adm_condominio_id = 8 and (nome_razao like('%rafa%'))')
                      ->order(`id_fin_conta_pagar` ASC)
                      ->limit(25,0);

SQL gerado

SELECT `fin_conta_pagar`.*, `adm_pessoa`.`nome_razao` FROM `fin_conta_pagar` INNER JOIN `adm_pessoa` ON adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id WHERE (adm_condominio_id = 8 and (nome_razao like('%rafa%') )) ORDER BY `id_fin_conta_pagar` ASC LIMIT 25

 

Ficou mais claro?

 

Obrigado

[Matias Rezende 50]

Troque isto

->where('adm_condominio_id = 8 and (nome_razao like('%rafa%'))')

por isto

->where('adm_condominio_id = ?', 8)->where('nome_razao like ?', '%rafa%')

 

Se não funcionar, poste o script utilizado para a criação das tabelas.

 

Carlos Eduardo

[Rafael Beber 0]

Olá Matias,

 

Sua solução funcionou perfeitamente.

 

Porém, como comentei antes, isso faz parte dos filtros de uma lista, semelhante ao filtro do excel, onde posso ter uma ou mais colunas sendo filtradas, preciso montar a clausula WHERE de acordo com os filtros.

 

Eu estava usando só com 1 ->where() e gerando a string com os campos que estão sendo filtrados, estava funcionando, até utilizar o join.

 

Olhei na documentação do Zend e não encrontrei que me ajude a resolver isso.

 

Tem como montar a expressão dinamicamente?

[Rafael Beber 0]

Depois de pesquisar um pouco mais descobri com resolver, utilizando um loop e adicionado a clausula where a cada laço.

 

$select = $this->_db->select()
                      ->setIntegrityCheck(false)
                      ->from('fin_conta_pagar')
                      ->join('adm_pessoa','adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id',array('nome_razao'))
                      ->where($where)
                      ->order($order)
                      ->limit($limit,$start);

               foreach($queryBusca as $key => $value){
                   $select->orWhere($key,$value);   
               }

               $resultSet = $this->_db->fetchAll($select)->toArray();

 

Só que agora apreceu outro problema.

 

neste momento o sql gerado é este:

SELECT `fin_conta_pagar`.*, `adm_pessoa`.`nome_razao` 
FROM `fin_conta_pagar`
LEFT JOIN `adm_pessoa` ON adm_pessoa.id_adm_pessoa = fin_conta_pagar.adm_pessoa_id 
WHERE (adm_condominio_id = 10) OR (adm_pessoa.nome_razao like '%rafa%') OR (fin_conta_pagar.valor like '%rafa%')  
ORDER BY `id_fin_conta_pagar` ASC
LIMIT 25

 

preciso que a clausula where fique conforme abaixo:

 

WHERE (adm_condominio_id = 10 AND (adm_pessoa.nome_razao like '%rafa%' OR fin_conta_pagar.valor like '%rafa%'))  

[Matias Rezende 50]

Esta coluna (adm_condominio_id) fica em qual tabela?

 

Tenho a impressão que a melhor forma de construir isto no Zend é adicionar esta condição ((adm_condominio_id = 10)) no join, deixando no where apenas as condições do orWhere mesmo.

 

Carlos Eduardo

[Rafael Beber 0]

Segue estrutura tabelas utilizadas:

CREATE TABLE `adm_condominio` (
 `id_adm_condominio` int(11) NOT NULL AUTO_INCREMENT,
 `cnpj` varchar(18) NOT NULL,
 `nome` varchar(200) NOT NULL,
 `endereco` varchar(300) NOT NULL,
 `dt_cadastro` datetime NOT NULL,
 `dt_alteracao` datetime NOT NULL,
 `sis_administradora_id` int(11) NOT NULL,
 PRIMARY KEY (`id_adm_condominio`),
 KEY `administradora` (`sis_administradora_id`),
 CONSTRAINT `fk_c_administradora` FOREIGN KEY (`sis_administradora_id`) REFERENCES `sis_administradora` (`id_sis_administradora`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE `fin_conta_pagar` (
 `id_fin_conta_pagar` int(11) NOT NULL AUTO_INCREMENT,
 `valor` decimal(12,2) DEFAULT NULL,
 `dt_cadastro` datetime DEFAULT NULL,
 `dt_alteracao` datetime DEFAULT NULL,
 `adm_condominio_id` int(11) NOT NULL,
 `adm_pessoa_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id_fin_conta_pagar`),
 KEY `condominio` (`adm_condominio_id`),
 KEY `fk_cp_condominio` (`adm_condominio_id`),
 KEY `fk_cp_pessoa` (`adm_pessoa_id`),
 CONSTRAINT `fk_cp_condominio` FOREIGN KEY (`adm_condominio_id`) REFERENCES `adm_condominio` (`id_adm_condominio`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_cp_pessoa` FOREIGN KEY (`adm_pessoa_id`) REFERENCES `adm_pessoa` (`id_adm_pessoa`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE `adm_pessoa` (
 `id_adm_pessoa` int(11) NOT NULL AUTO_INCREMENT,
 `cpf_cnpj` varchar(18) NOT NULL,
 `nome_razao` varchar(255) DEFAULT NULL,
 `endereco` varchar(255) DEFAULT NULL,
 `dt_cadastro` datetime DEFAULT NULL,
 `dt_alteracao` datetime DEFAULT NULL,
 `sis_administradora_id` int(11) DEFAULT NULL,
 `tipo_pessoa` char(1) NOT NULL,
 PRIMARY KEY (`id_adm_pessoa`),
 KEY `fk_p_administradora` (`sis_administradora_id`)
)

 

Preciso fazer a busca por nome de pessoa pelo controller fin_contas_pagar, toda conta tem uma pessoas e um condominio.

 

Nesse caso estou listando todas as contas do condominio 10, mas tambem quero filtrar pelo nome da pessoa.

[Matias Rezende 50]

Tenta fazer assim:

 

$select = $this->_db->select()
                      ->setIntegrityCheck(false)
                      ->from('fin_conta_pagar')
                      ->join('adm_pessoa',array('adm_pessoa.id_adm_pessoa' => 'fin_conta_pagar.adm_pessoa_id', 'fin_conta_pagar.adm_condominio_id' => $varComOIdDoCondominio),array('nome_razao'))
                      ->order($order)
                      ->limit($limit,$start);

               foreach($queryBusca as $key => $value){
                   $select->orWhere($key,$value);   
               }

               $resultSet = $this->_db->fetchAll($select)->toArray();

 

Como eu disse, acho que a melhor forma para contornar esta limitação do Zend (que eu nunca consegui resolver) é adicionar este valor na cláusula where.

 

Carlos Eduardo

[Rafael Beber 0]

Olá Matias,

 

Consegui resolver de outra forma.

 

Segue:

A função abaixo monta e retorna o sql sem filtros e busca

protected function setQuery($id){
       $select = $this->select()
               ->setIntegrityCheck(false)
               ->from($this->_name)
               ->join("adm_pessoa","adm_pessoa.id_adm_pessoa = adm_imovel.adm_pessoa_id",array("nome_razao"))
               ->where("$this->_name.adm_condominio_id = ?", $id);
       return $select;
   }

 

A função abaixo monta e retorna o sql com os parametros de busca.

Nesse caso é montado um sql para cada campo e no final utilizo o UNION para agrupar os resultados.

 

Dessa forma consigo a simulação da clausula where(adm_condominio = 10 and (nome_razao like '%rafa%' or endereco like '%rafa%'))

protected function setQuerySearch($id, $fields, $query){
       for($i = 0; $i < sizeof($fields); $i++){
           //Mapeia campos com tabelas
           switch($fields[$i]){
               case 'nome_razao':
                   $querySearch["adm_pessoa.$fields[$i] like ?"] = "%$query%";
                   break;
               default:
                   $querySearch["$this->_name.$fields[$i] like ?"] = "%$query%";
                   break;
           }
       }
       $selects = array();
       foreach($querySearch as $key => $value){
           $select = $this->setQuery($id);            
           $select = $select->where($key,$value);
           array_push($selects, $select);
       }
       $select = $this->select()->union($selects);
       return $select;
   }

 

A função abaixo monta e retorna o sql com os parametros dos filtros.

protected function setQueryFilters($id, $filters){
       $select = $this->setQuery($id);
       foreach ($filters as $filter) {
           $colunm = NULL;
           //Mapeia campos com tabelas
           switch($filter['field']){
               case 'nome_razao':
                   $colunm = "adm_pessoa.".$filter['field'];
                   break;
               default:
                   $colunm = $this->_name.".".$filter['field'];
                   break;
           }
           //Define operador padrao
           $operator = 'like';
           //Mapeia Operador
           if(isset($filter['data']['comparison'])){
               switch($filter['data']['comparison']){
                   case 'gt':
                       $operator = ' >=';
                       break;
                   case 'lt':
                       $operator = ' <=';
                       break;
                   case 'eq':
                       $operator = '=';
                       break;
               }
           }
           //Cria 1 SQL para cada campo
           switch($filter['data']['type']){
               case 'string':
                   $select = $select->where("$colunm like ?", '%'.$filter['data']['value'].'%'); 
                   break;
               case 'date':
                   $colunm = "cast($colunm as date)";
                   $select = $select->where("$colunm $operator ?", $filter['data']['value']);
                   break;
               case 'numeric':     
                   $select = $select->where("$colunm $operator ?", $filter['data']['value']);
                   break;
               case 'list':
                   $values = explode(',',$filter['data']['value']);
                   $select = $select->where("$colunm in (?)", $values);
                   break;
           }
       }
       return $select;
   }

 

Aqui a função que é chamado no controller, que utiliza as funções acima.

public function read($id,$query,$fields,$filters,$order,$limit,$start){
      if(count($filters) > 0){
           //Query Filters
           $select = $this->setQueryFilters($id, $filters);
           $select = $select->order($order)->limit($limit,$start);
           $resultSet = $this->fetchAll($select)->toArray();

       }else if(strlen($query) > 0){
           //Query Search
           $select = $this->setQuerySearch($id, $fields, $query);
           $select = $select->order($order)->limit($limit,$start);
           $resultSet = $this->fetchAll($select)->toArray();
       }else{
           //No Query
           $select = $this->setQuery($id);
           $select = $select->order($order)->limit($limit,$start);
           $resultSet = $this->fetchAll($select)->toArray();
       }
       return $resultSet;
   } 

 

Por equanto o codigo acima resolveu meu problema. Se houver algum outro problema posto aqui.

 

Obrigado por enquanto.