The trouble with mapping against an arbitrary select is that you can't insert/update/delete through the mapped class. Two ways around that:
1) if your database engine supports it, create a view from the join query and define insert/update/delete rules on the view in your RDBMS, and in SQLAlchemy map a class against the view, as if it were a table. PostgreSQL documentation for this feature: http://www.postgresql.org/docs/8.4/static/rules-update.html 2) instead of mapping against a query, map against the table normally but add a column property with a correlated subquery: http://www.sqlalchemy.org/docs/mappers.html#sql-expressions-as-mapped-attributes http://www.sqlalchemy.org/docs/reference/orm/mapping.html#sqlalchemy.orm.column_property You can filter on this column property, but such a query would presumably be less efficient (correlated subquery vs. join). However, queries that *don't* filter on this property will be faster (single- table query vs. join) if you make the property deferred. Regards, - Gulli On Jan 31, 12:13 am, Gunnlaugur Briem <gunnlau...@gmail.com> wrote: > 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-a... > > 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.
