如何将复杂的 SQL 查询转换为 Zend_Db_Select 语句? [英] How does one convert complex SQL queries into Zend_Db_Select statements?

问题描述

MySQL 网站有一个关于存储分层数据的优秀教程在一个数据库中.我正在尝试编写返回节点的直接子节点的查询.我不想只是从 MySQL 网站复制/粘贴查询，因为我正在尝试以与数据库无关的方式处理此问题.

The MySQL website has an excellent tutorial on storing hierarchal data in a database. I'm trying to write the query that returns a node's immediate children. I don't want to just copy/paste the query from the MySQL website, because I'm trying to work on this in a database agnostic manner.

这是我正在尝试 Zend_Db_Select-ify 的查询

This is the query I'm trying to Zend_Db_Select-ify

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'PORTABLE ELECTRONICS'
        GROUP BY node.name
        ORDER BY node.lft
    )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
    AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
    AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;

推荐答案

您可以尝试将查询拆分为两个 Zend_Db_Select 语句 - 父查询和子查询.您可以使用 Zend_Db_Select 对象作为 from() 方法的参数，如下所示:

You can try spliting your query into two Zend_Db_Select statements - parent query and subquery. You can use Zend_Db_Select object as param of from() method, like below:

$mainQuery = $db->select();
$mainQuery->from('user');

$sub = $db->select();
$sub->from('company');

$mainQuery->from(array('subquery' => $sub));

你会得到这样的查询:

SELECT `user`.*, `sub`.* FROM `user` 
    INNER JOIN (
        SELECT `company`.* FROM `company`
    ) AS `sub`

如您所见，当您添加第二个 from() 时，它会自动添加 INNER JOIN - 但我认为，可以将您的查询重写为用户连接而不是多从语法.所以你应该使用 joinInner() 方法，因为这样你就可以指定连接条件作为它的第二个参数.

As you see, it automatically adds INNER JOIN when you add second from() - but I think, it's possible to rewrote your query to user joins instead of multi from syntax. So you should use joinInner() method, because then you can specify join condition as second param of it.

注意，该子查询类似于主查询，因此您可以构建主查询，将其克隆为子查询，并感谢 Zend_Db_Select 可能性删除不必要的部分(reset() 方法)并替换它们:

Notice, that subquery is similar to main query, so you could build main query, clone it as subquery, and thanks to Zend_Db_Select possibilities remove unnecessary parts (reset() method) and replace them:

$mainQuery = $db->select(); //and rest
$subQuery = clone $mainQuery;
$subQuery->reset(Zend_Db_Select::WHERE);
$subQuery->where(); // and add valid conditions for subquery

这篇关于如何将复杂的 SQL 查询转换为 Zend_Db_Select 语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！