我正在尝试实现以下查询来处理SQLAlchemy中的嵌套集（请参阅此处）。我正在努力的是最后如何在子句depth中的主SELECT查询（取决于子SELECT查询）中使用标记的计算HAVING。
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
HAVING depth <= 1
ORDER BY node.lft;
我觉得使用时非常接近：
node = aliased(Category)
parent = aliased(Category)
sub_parent = aliased(Category)
sub_tree = DBSession.query(node.name,
(func.count(parent.name) - 1).label('depth')).\
filter(node.lft.between(parent.lft, parent.rgt)).\
filter(node.name == category_name).\
group_by(node.name).\
order_by(node.lft).subquery()
children = DBSession.query(node.name,
(func.count(parent.name) - (sub_tree.c.depth + 1)).label('depth')).\
filter(node.lft.between(parent.lft, parent.rgt)).\
filter(node.lft.between(sub_parent.lft, sub_parent.rgt)).\
filter(sub_parent.name == sub_tree.c.name).\
group_by(node.name).having(depth <= 1).\
order_by(node.lft).all()
但是，我最终得到了错误：
NameError: global name 'depth' is not defined
哪种有意义。如果我代替having(depth <= 1)用having(func.count('depth') <= 1，我结束了以下HAVING生成子句返回任何结果（其中％s占位符（“深度”，1））：
HAVING count(%s) <= %s
我真正需要它来读取的内容完全是这样的：
HAVING depth = 1
有人有什么想法吗？
我的最后一招是实际执行原始查询，而不是通过ORM层，但是我真的不愿意，因为我是如此接近...
提前致谢。
编辑：
我也尝试了以下代码，但是它没有返回正确的结果（好像“ depth”标签始终为0）：
node = aliased(Category)
parent = aliased(Category)
sub_parent = aliased(Category)
sub_tree_depth = (func.count(parent.name) - 1).label('depth')
depth = (func.count(parent.name) - (sub_tree_depth + 1)).label('depth')
sub_tree = DBSession.query(node.name,
sub_tree_depth).\
filter(node.lft.between(parent.lft, parent.rgt)).\
filter(node.name == category_name).\
group_by(node.name).\
order_by(node.lft).subquery()
children = DBSession.query(node.name,
depth).\
filter(node.lft.between(parent.lft, parent.rgt)).\
filter(node.lft.between(sub_parent.lft, sub_parent.rgt)).\
filter(sub_parent.name == sub_tree.c.name).\
group_by(node.name).having(depth <= 1).\
order_by(node.lft).all()
由此HAVING生成的子句看起来像（原始查询中的categories_2 == parent）：
HAVING count(categories_2.name) - ((count(categories_2.name) - 1) + 1) <= 1
编辑：
我认为包括生成的SQL可能会有所帮助。
SQL炼金术
node = aliased(Category)
parent = aliased(Category)
sub_parent = aliased(Category)
sub_tree = DBSession.query(node.name,
(func.count(parent.name) - 1).label('depth')).\
filter(node.lft.between(parent.lft, parent.rgt)).\
filter(node.name == category_name).\
group_by(node.name).\
order_by(node.lft).subquery()
depth = (func.count(parent.name) - (sub_tree.c.depth + 1)).label('depth')
children = DBSession.query(node.name, depth).\
filter(node.lft.between(parent.lft, parent.rgt)).\
filter(node.lft.between(sub_parent.lft, sub_parent.rgt)).\
filter(sub_parent.name == sub_tree.c.name).\
group_by(node.name).having(depth <= 1).\
order_by(node.lft)
生成的SQL
'SELECT categories_1.name AS categories_1_name, count(categories_2.name) - (anon_1.depth + %s) AS depth
FROM categories AS categories_1, categories AS categories_2, (SELECT categories_1.name AS name, count(categories_2.name) - %s AS depth
FROM categories AS categories_1, categories AS categories_2
WHERE categories_1.lft BETWEEN categories_2.lft AND categories_2.rgt AND categories_1.name = %s GROUP BY categories_1.name ORDER BY categories_1.lft) AS anon_1, categories AS categories_3
WHERE categories_1.lft BETWEEN categories_2.lft AND categories_2.rgt AND categories_1.lft BETWEEN categories_3.lft AND categories_3.rgt AND categories_3.name = anon_1.name GROUP BY categories_1.name
HAVING count(categories_2.name) - (anon_1.depth + %s) <= %s ORDER BY categories_1.lft' (1, 1, u'Institutional', 1, 1)
您的SQL查询使用隐式联接，在SQLAlchemy中，您需要显式定义它们。除此之外，您的第二次尝试几乎是正确的：
node = aliased(Category)
parent = aliased(Category)
sub_parent = aliased(Category)
sub_tree = DBSession.query(node.name,
(func.count(parent.name) - 1).label('depth')).\
join(parent, node.lft.between(parent.lft, parent.rgt)).\
filter(node.name == category_name).\
group_by(node.name).\
order_by(node.lft).subquery()
depth = (func.count(parent.name) - (sub_tree.c.depth + 1)).label('depth')
children = DBSession.query(node.name, depth).\
join(parent, node.lft.between(parent.lft, parent.rgt)).\
join(sub_parent, node.lft.between(sub_parent.lft, sub_parent.rgt)).\
join(sub_tree, sub_parent.name == sub_tree.c.name).\
group_by(node.name, sub_tree.c.depth).\
having(depth <= 1).\
order_by(node.lft).all()
如果HAVING生成的SQL中的子句将重复完整的表达式而不是其别名，请不要感到惊讶。那是因为那里不允许使用别名，这只是MySQL的扩展，而SQLAlchemy努力生成在大多数情况下都可以使用的SQL。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句