Correct syntax for SELECT and INNER JOIN in Zend

Question

Correct syntax for SELECT and INNER JOIN in Zend

Navigation

#1 by (1 votes)

4

I think it's a simple question, but because I do not know 50% of Zend I'm breaking my head, so here it 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 repeated logging results.

In the JOIN part the poi nickname enters the query syntax as shown in print ($ sql); I did:

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

Instead of 'poi' should be 'cli' , but the way syntax is in Zend I can not find a solution to modify this without ruining the rest .

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

mysql select zend

asked by anonymous 19.11.2014 / 20:38

1 answer

Read serial port values using Pyserial How to count the frequency of each letter in a string?

score 1 · Accepted Answer

The third parameter of the join tells which table values you want to bring. If you do not spend any value, it brings all. So there is poi.* in the final result.

If you do not want to bring any information from the fc_log_pointing table, 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');