Zend_Db 子查询 [英] Zend_Db subquery

问题描述

我一直在尝试用 ZendFW 构建一个 sql 查询，但我似乎无法让它像我想要的那样运行(或根本无法运行).这是我尝试使用 zend_db select() 构建的有效查询

I've been trying to construct a sql query with ZendFW, but I cant seem to get it to function like I want to (or function at all). This is the query that works that I'm trying to build with zend_db select()

SELECT tc.trip_title, td.ID, td.trip_id, 
  (SELECT count(*) FROM 'trips_invites' ti 
   WHERE ti.destination_id=td.ID AND ti.accepted ='NR') AS "pending_invites" 
FROM `trips_current` AS `tc`, `trips_data` AS `td` 
WHERE (tc.ID=td.trip_id) AND (tc.creator_id = '1') 
ORDER BY `trip_id` ASC 

我不知道如何在那里正确获取该子查询，但我尝试的任何方法似乎都不起作用.

What I can't figure out is how to properly get that subquery in there, and nothing I try seems to work.

任何帮助将不胜感激！

谢谢！

编辑/回答:如果有人遇到过类似的问题，根据下面的建议，我按以下方式重新查询:

Edit/Answer: If anyone will ever have a similar problem, based on the suggestion below I re-worked by query in the following way:

SELECT `tc`.`trip_title`, `td`.`ID`, `td`.`trip_id`, count(TI.ID)
FROM `trips_current` AS `tc` 
INNER JOIN `trips_data` AS `td` ON td.trip_id = tc.ID 
LEFT JOIN trips_invites AS TI ON ti.destination_id = td.id
WHERE tc.creator_id = 1  AND ti.accepted='NR'
GROUP BY td.id
ORDER BY `trip_id` ASC

我使用 ZendFW 以这种方式创建的:

which using ZendFW I created this way:

$select = $this->dblink->select() 
->from(array('tc' => 'trips_current'),
      array('trip_title'))
->join(array('td' => 'trips_data'), 
'td.trip_id = tc.id',                   
      array('ID','trip_id'))
->joinLeft(array('ti'=>'trips_invites'),
     'ti.destination_id = td.id',
     array('COUNT(ti.id)'))
->where('tc.creator_id =?',1)
->group('td.id')
->order('trip_id');

推荐答案

你不需要子查询，你可以用 GROUP BY 做到这一点:

You don't need a subquery, you can do this with GROUP BY:

$select = $db->select()
  ->from(array("tc"=>"trips_current"), array("trip_title"))
  ->join(array("td"=>"trips_data"), "td.trip_id = tc.ID", array("ID", "trip_id"))
  ->joinLeft(array("ti"=>"trips_invites"), "ti.destination_id = td.ID", array("COUNT(*)")
  ->where("tc.creator_id = ?", 1)
  ->group(array("tc.ID", "td.ID"))
  ->order("trip_id");

我假设您使用的是 MySQL.由于 MySQL 允许的非标准行为，group-by 更简单.

I'm assuming you're using MySQL. The group-by is simpler that way because of MySQL's permissive nonstandard behavior.

我将上述查询更改为对 ti 使用 joinLeft().这是为了防止给定目的地不存在邀请，正如您在评论中提到的那样.

edit: I change the above query to use joinLeft() for ti. This is in case no invites exist for a given destination, as you mention in your comment.

如果您确实需要使用子查询，可以单独创建它，然后将其插入到主查询的选择列表中:

If you really need to use a subquery, you can create it separately and then interpolate it into the select-list of your main query:

$subquery = $db->select()
  ->from(array("ti"=>"trips_invites", "COUNT(*)")
  ->where("ti.destination_id = td.ID");

$select = $db->select()
  ->from(array("tc"=>"trips_current"), array("trip_title", "($subquery)"))
  ->join(array("td"=>"trips_data"), "td.trip_id = tc.ID", array("ID", "trip_id"))
  ->where("tc.creator_id = ?", 1)
  ->order("trip_id");

Zend_Db_Select 知道在选择列表中命名的列中查找括号并跳过对这些列的分隔.

Zend_Db_Select knows to look for parentheses in the column named in your select-list and skip delimiting such columns.

我还想指出，您不必仅仅因为 Zend_Db_Select 就使用它.当您需要使用依赖于变量或应用程序逻辑的部分构建查询时，该类最适合.如果您知道完整的 SQL 查询并且它不依赖于应用程序条件，那么将其写在字符串中会更清楚——就像您在原始问题中写的一样.

Also I would like to point out that you don't have to use Zend_Db_Select just because it's there. That class is best for when you need to build up a query with parts that depend on variables or application logic. If you know the full SQL query and it doesn't depend on application conditions, it's more clear to just write it out in a string -- just like you wrote out in your original question.

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