Wow great ;) It really works. Thanks. One more thing, is it possible to map this in a Model Class perhaps as a python @property name level and make it query-able? Like passing it to a filter clause
e.g. nested_category.query.filter(nested_category.c.level=='3') On Sat, Jan 30, 2010 at 8:48 AM, Gunnlaugur Briem <[email protected]>wrote: > 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 <[email protected]> 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]<sqlalchemy%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
