On Oct 31, 2010, at 3:43 AM, Michael Elsdörfer wrote:
> session.query(m1, m2)
> but of course, that doesn't do an actual join, and I need to use
> filter() instead of an onclause. While in this particular case I don't
> really care, I would imagine that there probably are scenarios where
> one would, so my first question is: Can the same result (multiple
> mappers returned) be achieved while generating an actual sql JOIN
> statement?
absolutely.
session.query(m1, m2).join((m2, m1.some_col==m2.some_col))
> Now specifically, the query i am using is between three models, Sale,
> Item, and an m2m SoldItem. I need to join all three tables, and want
> to return Sale and SoldItem objects.
>
> If I use this query:
>
> session.query(Sale, SoldItem)\
> .join((Item, SoldItem.item_id==Item.id)).\
> .filter(...sale/solditem condition...)
>
> it actually generates invalid SQL (Unknown column 'solditem.item_id'
> in 'on clause'"):
>
> SELECT ... FROM solditem, sale INNER JOIN item ON
> solditem.item_id = item.id
That SQL is actually valid, but some platforms such as MySQL don't accept it.
If your platform requires a single FROM clause if JOIN is used, then you would
use join() to ensure that sale->solditem and solditem->item all participate in
a single JOIN.
s.query(Sale, SoldItem).join((SoldItem,
SoldItem.sale_id==Sale.id)).join((Item, SoldItem.item_id==Item.id))
If you were using relationship() in your mappings, this becomes less verbose:
s.query(Sale, SoldItem).join(Sale.sold_items).join(SoldItem.item)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
