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.
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import FromClause
class CommonTableExpression(FromClause):
"""Represent the 'inside' of a common table
expression."""
def __init__(self, name, colnames):
self.name = name
self.colnames = colnames
def _populate_column_collection(self):
self._columns.update(
(name, column(name))
for name in self.colnames
)
@classmethod
def create(cls, name, colnames):
target = CommonTableExpression(name, colnames)
class ctx(object):
def __enter__(self):
return target
def __exit__(self, *arg, **kw):
pass
return ctx()
class SelectFromCTE(FromClause):
"""Represent the 'outside' of the CTE.
Ultimately this would be integrated into Select
itself, since we just want a Select with an
extra clause on top. "CommonTableExpression" objects
would be pulled from the FROM clause
and rendered on top.
"""
def __init__(self, inner_thing, stmt):
self.inner_thing = inner_thing
self.stmt = stmt
def _populate_column_collection(self):
for name, c in zip(self.inner_thing.colnames, self.stmt.c):
c._make_proxy(self, name)
@compiles(CommonTableExpression)
def _recur_inner_thing(element, compiler, **kw):
return element.name
@compiles(SelectFromCTE)
def _recur_outer_thing(element, compiler, **kw):
text = (
"WITH RECURSIVE %s(%s) AS (\n"
"%s\n"
")\n"
"SELECT * FROM %s" % (
element.inner_thing.name,
", ".join(element.inner_thing.colnames),
compiler.process(element.stmt, **kw),
element.inner_thing.name
)
)
if kw.get('asfrom'):
text = "(%s)" % text
return text
if __name__ == '__main__':
from sqlalchemy import select, Integer
from sqlalchemy.sql.expression import table, column, FromClause, bindparam, literal_column
groups = table('groups_recursive', column('id', Integer))
groups_assoc = table('groups_recursive_parents__groups_recursive_children',
column('parents_id'), column('children_id'))
with CommonTableExpression.create("all_parents", ["id", "rank"]) as all_parents:
rank = literal_column("rank")
s = select([groups.c.id, column("1")]).\
where(groups.c.id==groups_assoc.c.parents_id).\
where(groups_assoc.c.children_id==bindparam("groupid")).\
union(
select([groups.c.id, rank + 1]).\
where(groups_assoc.c.children_id==all_parents.c.id).\
where(groups_assoc.c.parents_id==groups.c.id)
)
all_parents = SelectFromCTE(all_parents, s)
# take a look
print all_parents
# make sure cols line up, typing info is transferred, etc.
assert all_parents.c.id.shares_lineage(s.c.id)
assert isinstance(all_parents.c.id.type , Integer)
print "\n-------------------------------------\n\n"
from sqlalchemy.orm import Session, mapper
class Group(object):
pass
mapper(Group, groups, primary_key=[groups.c.id])
all_parents = all_parents.alias()
q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id)
print q
