Gabriel Fiel Gabriel Fiel - 3 months ago 8
PHP Question

Zend DB multiple joins don't work

I have the following code, this way it does not work.

$query = $this->select()
->from(array('c' => 'contrato'),
array('*'))
->setIntegrityCheck(false)
->join(array('ch' => 'contrato_host'),'ch.id_host = '.$id_host.' and ch.id_contrato = c.id_contrato', array())
->joinUsing('contrato_tipo', 'id_contrato_tipo', 'ds_contrato_tipo')
->joinUsing('fornecedor', 'id_fornecedor', 'razao_social')
->where('id_cliente = ?', $id_cliente)
->order(array('id_contrato DESC'));




But when I use just it works



$query = $this->select()
->from(array('c' => 'contrato'),
array('*'))
<br/>->setIntegrityCheck(false)
<br/>->join(array('ch' => 'contrato_host'),'ch.id_host = '.$id_host.' and ch.id_contrato = c.id_contrato', array())
<br/>->where('id_cliente = ?', $id_cliente)
<br/>->order(array('id_contrato DESC'));





I don't know why, but when I did the code by hand it works



SELECT
contrato.*, contrato_tipo.ds_contrato_tipo, fornecedor.razao_social
FROM [action].[dbo].[contrato]
INNER JOIN [action].[dbo].[contrato_host] ON [contrato_host].id_host = 14 and [contrato_host].id_contrato = [contrato].id_contrato
INNER JOIN [action].[dbo].[contrato_tipo] ON contrato_tipo.id_contrato_tipo = contrato.id_contrato_tipo
INNER JOIN [action].[dbo].fornecedor ON fornecedor.id_fornecedor = contrato.id_fornecedor
WHERE ([contrato].id_cliente = '1') ORDER BY "id_contrato" DESC

Answer
$query = $this->select()
->from(array('c'=>'contrato'),
        array('*'))
        ->setIntegrityCheck(false)
        ->join(array('ch'=>'contrato_host'),'ch.id_host='.$id_host.' and ch.id_contrato = c.id_contrato', array())
        ->join('contrato_tipo','id_contrato_tipo','ds_contrato_tipo',array())
        ->join('fornecedor','id_fornecedor', 'razao_social',array())
->where('id_cliente= ?',$id_cliente)
->order(array('id_contrato DESC'));