Explicit CROSS JOIN; or, left joining the cartesian product of two unrelated tables to a third

[Daniel Grace]

I've been banging my head against the wall for hours trying to figure out how to do this in sqlalchemy.  After discussing on #sqlalchemy, it was suggested I ask here so here goes.

I'm trying to get SQL roughly equivalent to the below, but using the ORM:

SELECT .... FROM a CROSS JOIN b LEFT JOIN c ON c.a_id=a.id AND c.b_id=b.id

What I can't do is something like this:

# session.query(model_a, model_b).outerjoin(model_c, model_c.a_id==model_a.id & model_c.b_id==model_b.id)

because the resultant SQL becomes this:

SELECT .... FROM a, b LEFT JOIN c ON c.a_id=a.id AND c.b_id=b.id

which fails (in Postgres and, IIRC, newer MySQL versions) due to explicit joins' binding tighter than the comma operator:

ERROR: invalid reference to FROM-clause entry for table "a"
SQL state: 42P01
Hint: There is an entry for table "a", but it cannot be referenced from this part of the query.
Character: 342

A few notes:

I'm using the ORM, but I can't actually use the Many to Many/Association Object patterns here because 'c' actually depends on three tables, not two.  (In this particular case, I'm only interested in cases of c related to one particular entry in the third table, so there's no need to query on that particular relationship).

I can't merely rewrite the query to "c INNER JOIN a... INNER JOIN b" because I'm also interested in the combinations of (a,b) for which there are no c.  (I could technically union it against another query that uses NOT EXISTS, but this smells of bad hackery).

If I could get parenthesis around the a, b, the query would work as intended: SELECT .... FROM (a, b) LEFT JOIN c ON c.a_id=a.id AND c.b_id=b.id

Any ideas on how to solve this?

-- Daniel Grace

[Michael Bayer]

according to wikipedia, a "CROSS JOIN" is just a straight up cartesian product.  So why not do "a JOIN b on 1 == 1" ?

query(model_a).join(model_b, literal(1) == 1).outerjoin(model_c, ...)

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

[Daniel Grace]

I wasn't having any luck doing the equivalent of inner join ... on true earlier, but apparently I was just doing it wrong.  That works.   (In fact, I can just use "literal(True)" instead of "literal(1) == 1")

It still feels a little hackish, but at least it works.  Thanks!

-- Daniel