使用Zend DB编写子查询 [英] Writing a subquery using Zend DB

问题描述

在将下面的SQL转换为Zend Db查询时遇到一些问题.

I am having some problems turning the SQL below into a Zend Db query.

$select = ' SELECT s.id, i.id as instance_id, i.reference, i.name, i.sic_code, i.start_date
                FROM sles s
                JOIN sle_instances i
                ON s.id = i.sle_id
                WHERE i.id = (  SELECT MAX(id) 
                                FROM sle_instances 
                                WHERE sle_id = s.id
                                )
                ORDER BY i.name ASC';

我之前已经了解了代码-但Zend Db不能正确生成查询.谁能告诉我我想念的东西吗?

I have got as far as the code before - but Zend Db isn't producing the query correctly. Can any one show me what I missing??

$select = $db->select() ->from('sles', array(   'id',
                                                'instance_id'   => 'sle_instances.id',                                                                                          
                                                'reference'     => 'sle_instances.reference',           
                                                'name'          => 'sle_instances.name',
                                                'sic_code'      => 'sle_instances.sic_code',
                                                'start_date'    => 'sle_instances.start_date'
                                             )
                              )
                        ->join('sle_instances', 'sles.id = sle_instances.sle_id')
                        ->where('sles.id = (SELECT MAX(id) FROM sle_instances WHERE sle_id = sles.id)')
                        ->order('sle_instances.name ASC');

SQL确实可以正常工作.我正在使用Zend Db重写它，因为我希望使用Zend Paginator功能.

The SQL does work by the way. I am rewriting it using Zend Db as I wish to use the Zend Paginator functionality.

非常感谢您的帮助.

PJ

推荐答案

此:

    $select = $db->select()->from(array("s" => "sles"), array("s.id","i.id as instanceid","i.reference","i.name","i.sic_code","i.start_date"))
                                 ->join(array('i' => "sle_instances"),"s.id = i.sle_id",array())
                                 ->where("i.id = (select max(id) from sle_instances where sle_id = s.id)")
                                 ->order('i.name asc');

为此:

"SELECT `s`.`id`, `i`.`id` AS `instanceid`, `i`.`reference`, `i`.`name`, `i`.`sic_code`, `i`.`start_date` FROM `sles` AS `s`
 INNER JOIN `sle_instances` AS `i` ON s.id = i.sle_id WHERE (i.id = (select max(id) from sle_instances where sle_id = s.id)) ORDER BY `i`.`name` asc"

这篇关于使用Zend DB编写子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！