Re: [sqlalchemy] joining to a from_statement
Hi, thank you for your help, and the effort at writing a good piece of code. however, while playing around with, and trying to execute it in PGAdmin, I stumbled upon some problems the q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id) generates a SQL like (I've inserted real values): SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = 4 UNION SELECT groups_recursive.id AS id, rank + 1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id it chokes on : ERROR: invalid reference to FROM-clause entry for table groups_recursive LINE 3: (SELECT groups_recursive.id AS id, 1 ^ HINT: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. ** Error ** ERROR: invalid reference to FROM-clause entry for table groups_recursive SQL state: 42P01 Hint: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. Character: 242 obviously, there is a problem at the JOIN stage ... SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE I tried s = select([groups.c.id, column(1)],from_obj=groups) to no better luck many thanks again 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/-/4YkzRT9s2bAJ. 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.
Re: [sqlalchemy] joining to a from_statement
On Aug 12, 2011, at 6:00 AM, NiL wrote: Hi, thank you for your help, and the effort at writing a good piece of code. however, while playing around with, and trying to execute it in PGAdmin, I stumbled upon some problems the q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id) generates a SQL like (I've inserted real values): SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = 4 UNION SELECT groups_recursive.id AS id, rank + 1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id it chokes on : ERROR: invalid reference to FROM-clause entry for table groups_recursive LINE 3: (SELECT groups_recursive.id AS id, 1 ^ HINT: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. ** Error ** ERROR: invalid reference to FROM-clause entry for table groups_recursive SQL state: 42P01 Hint: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. Character: 242 obviously, there is a problem at the JOIN stage ... SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE I tried s = select([groups.c.id, column(1)],from_obj=groups) to no better luck OK so I'd need you to wrestle with the PG side here, and figure out exactly what query PG accepts on this. I've little experience with CTEs, not sure if perhaps the WITH always needs to be the outermost expression, etc. Perhaps the query for the full span of mapped columns needs to be where we're putting the select * from all_parents part, and it joins to all_parents right there. The CTE construct will ultimately have to work that way anyway, in that you can put any SELECT you want at that point. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. 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.
Re: [sqlalchemy] joining to a from_statement
hi again, after playing a while with PG, here is a SQL statement that outputs the expected result SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( SELECT groups_recursive.id AS groups_recursive_id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = 4 AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id UNION SELECT groups_recursive.id, rank+1 FROM all_parents, groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = all_parents.id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id; and FYI, here is what the code is issuing (not functionnal) SELECT groups_recursive.id AS groups_recursive_id FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = :groupid UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id we have problems such as column 1 does not exists if we add groups_recursive in both FROM clauses regards 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/-/cTkUMo18h_IJ. 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.
Re: [sqlalchemy] joining to a from_statement
sure, couple of small adjustments, attached On Aug 12, 2011, at 10:44 AM, NiL wrote: hi again, after playing a while with PG, here is a SQL statement that outputs the expected result SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( SELECT groups_recursive.id AS groups_recursive_id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = 4 AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id UNION SELECT groups_recursive.id, rank+1 FROM all_parents, groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = all_parents.id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id; and FYI, here is what the code is issuing (not functionnal) SELECT groups_recursive.id AS groups_recursive_id FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = :groupid UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id we have problems such as column 1 does not exists if we add groups_recursive in both FROM clauses regards 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/-/cTkUMo18h_IJ. 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. 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. On Aug 12, 2011, at 10:44 AM, NiL wrote:hi again,after playing a while with PG, here is a SQL statement that outputs the expected resultSELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_nameFROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( SELECT groups_recursive.id AS groups_recursive_id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = 4 AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id UNION SELECT groups_recursive.id, rank+1 FROM all_parents, groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = all_parents.id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id )SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id;and FYI, here is what the code is issuing (not functionnal)SELECT groups_recursive.id AS groups_recursive_id FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ((SELECT groups_recursive.id AS id, "1" FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = :groupid UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id))SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.idwe have problems such as column "1" does not existsif we add groups_recursive in both FROM clausesregardsNil -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view
Re: [sqlalchemy] joining to a from_statement
thank you so much Michael !! much better few last things are WITH RECURSIVE all_parents(id, rank) AS SELECT groups_recursive.id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children quotes around the 1, this leads to ERROR: column 1 does not exist + the param :rank_1 feels weird, it is generated by the rank + 1 in union( select([groups.c.id, rank + 1]).\ but it feels really close to the solution best 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/-/Q0jzhkVnW6gJ. 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.
Re: [sqlalchemy] joining to a from_statement
its at the point where you should be able to tweak it using documented processes. column() applies quotes for example, whereas literal_column(1) would not. Same for an expression x + 1 will turn the 1 into a bind, would not if you again use literal_column() http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.column http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.literal_column On Aug 12, 2011, at 11:27 AM, NiL wrote: thank you so much Michael !! much better few last things are WITH RECURSIVE all_parents(id, rank) AS SELECT groups_recursive.id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children quotes around the 1, this leads to ERROR: column 1 does not exist + the param :rank_1 feels weird, it is generated by the rank + 1 in union( select([groups.c.id, rank + 1]).\ but it feels really close to the solution best 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/-/Q0jzhkVnW6gJ. 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. 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.
Re: [sqlalchemy] joining to a from_statement
yes, many thanks
I now have a method in my Group class (still in elixir syntax)
def hierarchy_ng(self):
with CommonTableExpression.create(
all_parents, [id, rank]) as all_parents:
rank = literal_column(rank)
groups = Group.table
groups_assoc =
Group._descriptor.find_relationship('parents').table
s = select([groups.c.id, literal_column(1)]).\
where(groups.c.id==groups_assoc.c.parents_id).\
where(groups_assoc.c.children_id==bindparam(groupid)).\
correlate(None).\
union(
select([groups.c.id, rank +
literal_column(1)]).\
where(groups_assoc.c.children_id==
all_parents.c.id).\
where(groups_assoc.c.parents_id==groups.c.id
).\
correlate(None)
).params(groupid=self.id)
all_parents = SelectFromCTE(all_parents, s)
all_parents = all_parents.alias()
q = Group.query.join((all_parents, all_parents.c.id==Group.id))
return q.all()
and it's ok now !
--
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/-/ji9w6r2l09IJ.
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.
Re: [sqlalchemy] joining to a from_statement
On Aug 11, 2011, at 12:31 PM, NiL wrote: 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 I think everything is on the mark here except you need a way to have your WITH RECURSIVE query as a FromClause, that is something with a .c. on it, rather than a TextClause which is just a string. We'd like to support WITH RECURSIVE directly but we need to make a decision on what to do about Oracle's CONNECT BY, i.e. do we just ignore it, or try to come up with a syntax that encompasses both.This problem needs to be approached carefully and I need to make sure I know every single corner of a CTE before making API decisions, which requires more commitment than I've had to tackle it fully. This is ticket #1859 at http://www.sqlalchemy.org/trac/ticket/1859 . But anyway right now I think the job is to make yourself a WITH RECURSIVE SQL element, so that you can join to it. So attached is a quick version of a CTE half hardcoded to the query you want to do. It's using with in Python too at the moment which seems very nice though studying the full syntax on PG's docs has me wondering if it actually makes sense. The ultimate SELECT query is mostly hardcoded for now. It renders the query I think you're looking for, though. I'd need to play with it some more to get it to be flexible enough to produce the example query at http://www.postgresql.org/docs/8.4/interactive/queries-with.html , but this should give you some building blocks to work with at least. 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. 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. thanksNiL -- 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. from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import FromClause class CommonTableExpression(FromClause): Represent the 'inside' of a common table expression.
