joining to a from_statement

[NiL]

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

[Michael Bayer]

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.

[NiL]

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

[Michael Bayer]

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.

[NiL]

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

[Michael Bayer]

sure, couple of small adjustments, attached

[NiL]

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

[Michael Bayer]

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

--
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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[NiL]

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 !