MySQL 网站有一个优秀的教程 http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/在数据库中存储分层数据。我正在尝试编写返回节点的直接子节点的查询。我不想只是从 MySQL 网站复制/粘贴查询,因为我试图以与数据库无关的方式处理此问题。
这是我尝试 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()
方法,如下:
$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`
如您所见,它会自动添加INNER JOIN
当你添加第二个时from()
- 但我认为,可以将您的查询重写为用户联接而不是多语法。所以你应该使用joinInner()
方法,因为这样您就可以将连接条件指定为它的第二个参数。
请注意,该子查询与主查询类似,因此您可以构建主查询,将其克隆为子查询,这要归功于Zend_Db_Select
可能性删除不必要的部分(reset()
方法)并替换它们:
$mainQuery = $db->select(); //and rest
$subQuery = clone $mainQuery;
$subQuery->reset(Zend_Db_Select::WHERE);
$subQuery->where(); // and add valid conditions for subquery
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)