Yes, you can map a class against an arbitrary select, not just a table, see:
http://www.sqlalchemy.org/docs/mappers.html#mapping-a-class-against-arbitrary-selects So above you have a query that you could map a class against (you might want to add the other nested_category columns to it). No need to declare the level property specially, it's just a column like any other, in the query you're mapping against. - Gulli On Jan 30, 3:31 pm, Juan Dela Cruz <juandelacru...@gmail.com> wrote: > 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 <gunnlau...@gmail.com>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 <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<sqlalchemy%2bunsubscr...@googlegrou > > ps.com> > > . > > 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 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.
