Malthe Borch <[email protected]> wrote:
> On Fri Jan 30 2015 at 4:42:42 PM Jonathan Vanasco <[email protected]> > wrote: > > This should generate your second query: > > q = s.query(Foo)\ > .join( > Boo, > Foo.id == Boo.id > )\ > .join( > Bar, > Boo.id == Bar.id > )\ > .first() > > But I already have relationships set up so I'd like to not have to manually > write out these joins, but also have the relevant columns loaded into my > relationship: > > q = s.query(Foo).options(joinedload(Foo.boo)) okey doke so yes, if you need the non-nested join just once somewhere, you can write it out manually as above. otherwise, if you want to flatten out the join in all cases, you’d write a @compiles decorator for Select which rewrites the joins out flattened. A good API to use for this is the visitor API. An example follows below, which illustrates a simple case. I don’t know what the edges would be for when this becomes more complicated, if any. If the implementation here could be proven error free in all cases, it could become a feature of the default compiler; the compiler already does join rewriting in the case to suit SQLite which does not support any kind of nesting of joins, but it does it the other way, rewriting them as subqueries which is much more complicated (because the OUTER JOIN case must be supported). Note that if you were using SQLAlchemy more than a year ago, you wouldn’t even get “a join (b join c)”, for many years it had to be “a join (SELECT * FROM b join c)”, so the nested joins you see here from SQLAlchemy’s POV are already “flat”. from sqlalchemy import select, table, column from sqlalchemy import sql from sqlalchemy.ext.compiler import compiles a = table('a', column('id')) b = table('b', column('id'), column('a_id')) c = table('c', column('id'), column('b_id')) j1 = b.join(c, b.c.id == c.c.b_id) j2 = a.join(j1, a.c.id == b.c.a_id) s1 = select([a, b, c]).select_from(j2) @compiles(sql.Select, "postgresql") def rewrite_select(element, compiler, **kw): def replace_froms(sub_element): if isinstance(sub_element, sql.Join) and \ isinstance(sub_element.right, sql.selectable.FromGrouping): # probably want to check the “isouter” flag here for False, as # we only want to flatten inner joins right_join = sub_element.right.element return sql.join( sub_element.left, right_join.left, sub_element.onclause).join( right_join.right, right_join.onclause) element = sql.visitors.replacement_traverse(element, {}, replace_froms) return compiler.visit_select(element, **kw) from sqlalchemy.dialects import postgresql print s1.compile() # default print s1.compile(dialect=postgresql.dialect()) # PG -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
