Hi Juan,
this will do it in version 10.5.8 (and probably earlier:
nested_category = Table(
'nested_category',
MetaData(),
Column('category_id', Integer, primary_key=True),
Column('name', Text, nullable=False),
Column('lft', Integer, nullable=False),
Column('rgt', Integer, nullable=False)
)
node = nested_category.alias('node')
parent = nested_category.alias('parent')
query = select([node.c.name, (func.count(node.c.name) - text
('1')).label('level')],
from_obj=join(node, parent,
node.c.lft.between(parent.c.lft, parent.c.rgt)
)
).group_by(node.c.name)
str(query) will show that it is correct (it uses a JOIN expression
instead of the WHERE condition, but that's equivalent and more
explicit)
The text('1') instead of just 1 is so that the literal constant 1 is
not needlessly replaced by a bind param. It works either way though.
Regards,
- Gulli
On Jan 29, 8:53 am, Juan Dela Cruz <juandelacru...@gmail.com> wrote:
> Can someone please help me to figure out the equivalent of this sql query to
> sqlalchemy
>
> This my nested_category table:
>
> +-------------+----------------------+-----+-----+
> | category_id | name | lft | rgt |
> +-------------+----------------------+-----+-----+
> | 1 | ELECTRONICS | 1 | 20 |
> | 2 | TELEVISIONS | 2 | 9 |
> | 3 | TUBE | 3 | 4 |
> | 4 | LCD | 5 | 6 |
> | 5 | PLASMA | 7 | 8 |
> | 6 | PORTABLE ELECTRONICS | 10 | 19 |
> | 7 | MP3 PLAYERS | 11 | 14 |
> | 8 | FLASH | 12 | 13 |
> | 9 | CD PLAYERS | 15 | 16 |
> | 10 | 2 WAY RADIOS | 17 | 18 |
> +-------------+----------------------+-----+-----+
>
> SELECT node.name, (COUNT(node.name)-1) AS level
> FROM nested_category AS node, nested_category AS parent
> WHERE node.lft BETWEEN parent.lft AND parent.rgt
> GROUP BY node.name;
>
> The result will be:
> +----------------------+-------+
>
> | name | depth |
> +----------------------+-------+
> | ELECTRONICS | 0 |
> | TELEVISIONS | 1 |
> | TUBE | 2 |
> | LCD | 2 |
> | PLASMA | 2 |
> | PORTABLE ELECTRONICS | 1 |
> | MP3 PLAYERS | 2 |
> | FLASH | 3 |
> | CD PLAYERS | 2 |
> | 2 WAY RADIOS | 2 |
> +----------------------+-------+
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
