All geek questions in one place

Using Labels in HAVING () in SQLAlchemy

I am trying to implement the following query to handle nested sets (see here ) in SQLAlchemy. What I'm struggling with is using depth labels in the main SELECT query (which depends on the sub SELECT query) in the HAVING at the end.

 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;

I feel very close when using:

 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()

But in the end I get an error message:

 NameError: global name 'depth' is not defined

Which view makes sense. If I substitute having(depth <= 1) into having(func.count('depth') <= 1 , I get the following HAVING that returns no results (Where the placeholders are% s ('depth', 1)):

 HAVING count(%s) <= %s

What I really need is exactly like this:

 HAVING depth = 1

Does anyone have any ideas?

My last resort is to actually execute the raw request, rather than go through the ORM layer, but I would really not want to, since I'm so close ...

Thanks in advance.

Edit:

I also tried the following code, but it does not return the correct results (as if the "depth" label is always 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()

The HAVING generated from this is similar to (categories_2 == parent in the original request):

 HAVING count(categories_2.name) - ((count(categories_2.name) - 1) + 1) <= 1

Edit:

I figured it would be helpful to include the generated 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')).\ 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)

Generated 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)
+6
source share
1 answer

Your SQL query uses implicit joins, in SQLAlchemy you need to explicitly define them. Also, your second attempt is almost correct:

 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()

Do not be surprised if the full expression is repeated in the HAVING expression in the generated SQL instead of its alias. This is because aliases are not allowed there, it is only a MySQL extension, while SQLAlchemy aims to generate SQL, which works in most cases.

+5
source

Source: https://habr.com/ru/post/971638/

More articles:


All Articles