Correct syntax for SELECT and INNER JOIN in Zend

Question

I believe it’s a simple question, but because I don’t know 50% of Zend I’m racking my brain, so here goes:

$sql =  $db->select()
    ->distinct()
    ->from(array('cli' => 'fc_cblcli'),array('codigo','tipo','nome'))
    ->join(array('poi' => 'fc_log_pointing'),'cli.codigo = poi.codigo')
    ->where('poi.data > ?',$diaIntervalo) //Pega a data em 'yyyy-MM-dd hh:mm:ss'
    ->where('poi.codigo = cli.codigo')
    ->order('poi.codigo ASC')
    ->order('cli.tipo ASC');

I’m doing a SELECT DISTINCT but because of the nickname poi is bringing back repeated log results.

In the JOIN part the nickname poi enter the query syntax as shown in print($sql); that I made:

SELECT DISTINCT `cli`.`codigo`, `cli`.`tipo`, `cli`.`nome`, `poi`.* 
FROM `fc_cblcli` AS `cli` 
INNER JOIN `fc_log_pointing` AS `poi` ON cli.codigo = poi.codigo WHERE (poi.data > '2014-11-19 17:16:28') 
    AND (poi.codigo = cli.codigo) 
ORDER BY `poi`.`codigo` ASC, `cli`.`tipo` ASC

In place of 'poi' should be 'cli', but the way the syntax is in Zend I can’t find a solution to modify it without ruining the rest.

If someone can explain to me the logic of why bulhufas is taking this nickname of JOIN in SELECT I thank!

Answer 1

The third parameter of Join tells you what table values you want to bring. If you don’t pass any value, it brings them all. That’s why it exists poi.* in the final result.

If you don’t want to bring any table information fc_log_pointing, you can pass array() as the third parameter of join, as follows

$sql =  $db->select()
    ->distinct()
    ->from(array('cli' => 'fc_cblcli'),array('codigo','tipo','nome'))
    ->join(array('poi' => 'fc_log_pointing'),'cli.codigo = poi.codigo',array())
    ->where('poi.data > ?',$diaIntervalo) _//Pega a data em 'yyyy-MM-dd'_
    ->where('poi.codigo = cli.codigo')
    ->order('poi.codigo ASC')
    ->order('cli.tipo ASC');