Hi list, I'm using SA 0.6.7, Python 2.6.5 and Postgres 8.4 on Ubuntu 10.04 LTS
I'm trying to optimize my code against postgres by using the recursive statements CTE as documented here : http://www.postgresql.org/docs/8.4/interactive/queries-with.html My model is a group graph ( many to many relation on itself) I've pasted a self running test of my code : http://pastebin.com/1Vc2PFLx the syntax is elixir's but that is not relevant the "pure SQL" query only includes id and rank (in my real life object, I have many fields, and they evolve) as seen in comment of the code, when I get detailed information on the result, a new SELECT query is issued for every attribute not yet loaded What I want to achieve : have all the attributes eager loaded without having to explicitely declare them in the PG specific query (for code maintenability) An approach I can't finalize : the idea was to run the PG select and have the result stored in a 2 columns temp table (how ?) and then query the groups (to have the ORM) while joining to this temp table. something like hierarchy_q = session.query(Group.id, 'rank').from_statement(group_all_groups).params(GROUPID=self.id).subquery() session.query(Group).join((hierarchy_q, Group.id==hierarchy_q.c.id) but : *** AttributeError: 'Annotated_TextClause' object has no attribute 'alias' Ideally, I would like a way to have a session.query(Group,'rank') where all the groups' attributes are loaded. Moreover, I wish to have a way of joining this query through relationships. For instance, Groups will have users, I would like to efficiently be able to do something like session.query(User).join(hierarchy_query).order_by("rank") to get all the users of the group, ordered by the rank of the group they belong to. I've read https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/VAttoxkLlXw but I don't feel my question is exactly the same, as I wish to keep the rank information (only available in the text query) any enlightening idea would be very welcome thanks NiL -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/OIgzgCxD-rgJ. To post to this group, send email to sqlalchemy@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.
