On Feb 5, 2008, at 11:36 AM, Bernhard wrote:
>
> Hello all
>
> This must be a silly problem, but I couldn't find the answer in this
> group, so excuse me if I didn't search thoroughly enough.
>
> I need to do multiple joins from a table to a single other table.
> SQLAlchemy generates this statement:
>
> SELECT transaction.id, attr1.value, attr2.value, attr3.value
> FROM transaction
> LEFT OUTER JOIN attribute AS attr1 ON attr1.transaction_id =
> transaction.id,
> transaction LEFT OUTER JOIN attribute AS attr3 ON attr3.transaction_id
> = transaction.id,
> transaction LEFT OUTER JOIN attribute AS attr2 ON attr2.transaction_id
> = transaction.id
>
> which fails in MySQL. It should read:
>
> SELECT transaction.id, attr1.value, attr2.value, attr3.value
> FROM transaction
> LEFT OUTER JOIN attribute AS attr1 ON attr1.transaction_id =
> transaction.id
> LEFT OUTER JOIN attribute AS attr3 ON attr3.transaction_id =
> transaction.id
> LEFT OUTER JOIN attribute AS attr2 ON attr2.transaction_id =
> transaction.id
>
> How can I get SA to do what I want? The current query code looks like
> this:
>
> attr1 = attributes.alias('attr1')
> attr2 = attributes.alias('attr2')
> attr3 = attributes.alias('attr3')
>
> s = select([transactions.c.id])
>
> s = s.column(attr1.c.value)
> s = s.column(attr2.c.value)
> s = s.column(attr3.c.value)
>
> s = s.select_from(transactions.outerjoin(attr1, attr1.c.transaction_id
> == transactions.c.id))
> s = s.select_from(transactions.outerjoin(attr2, attr2.c.transaction_id
> == transactions.c.id))
> s = s.select_from(transactions.outerjoin(attr3, attr3.c.transaction_id
> == transactions.c.id))
>
> What is it I'm doing wrong here?
>
the SQL you want is a string of connected LEFT OUTER JOINs, so you
have to specify them that way, i.e. in a "string":
s =
s
.select_from
(transactions
.outerjoin(attr1, ...).outerjoin(attr2, ...).outerjoin(attr3, ...))
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
