Nested joins duplication

[Michael Brickenstein]

Hi!

SQLAlchemy is really awesome (I really love it)
and I am still working on the RUM web frontend for it.

I have the problem, that I would like to make some nested join:

query=session.query(Address)
query=query.join(User, Address.user)
query=query.join(Group, User.group)

Is it legal to the join this way?

The clauses seem to be duplicated:

SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
FROM addresses
JOIN users ON users.id = addresses.user_id
JOIN users ON users.id = addresses.user_id
JOIN groups ON groups.id = users.group_id
JOIN groups ON groups.id = users.group_id

I attach a full example to this mail.
I got the same behaviour with all versions I tried (0.5.8 (the example is for 0.6.x), 0.6.1, and 0.6.3.

Thank you very much in advance.
Michael

[Michael Bayer]

On Aug 10, 2010, at 10:13 AM, Michael Brickenstein wrote:

> Hi!
>
> SQLAlchemy is really awesome (I really love it)
> and I am still working on the RUM web frontend for it.
>
> I have the problem, that I would like to make some nested join:
>
> query=session.query(Address)
> query=query.join(User, Address.user)
> query=query.join(Group, User.group)
>
> Is it legal to the join this way?

its legal but not what you're intending. your intention is:

query=session.query(Address)
query=query.join((User, Address.user))
query=query.join((Group, User.group))

but you really only need:

query=session.query(Address)
query=query.join(Address.user)
query=query.join(User.group)

we've recently added checking to detect a common error of query.join(User, User.id==Address.user_id), but yours above is a less common variant of that.

>
> The clauses seem to be duplicated:
>
> SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.user_id AS addresses_user_id
> FROM addresses
> JOIN users ON users.id = addresses.user_id
> JOIN users ON users.id = addresses.user_id
> JOIN groups ON groups.id = users.group_id
> JOIN groups ON groups.id = users.group_id
>
> I attach a full example to this mail.
> I got the same behaviour with all versions I tried (0.5.8 (the example is for 0.6.x), 0.6.1, and 0.6.3.
>
> Thank you very much in advance.
> Michael
>
>

> -------------------------------------------
> Michael Brickenstein
> Mathematisches Forschungsinstitut Oberwolfach gGmbH
> Schwarzwaldstr. 9 - 11
> 77709 Oberwolfach
> Tel.: 07834/979-31
> Fax: 07834/979-38
>
> <doublejoin.py>--
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Michael Brickenstein]

Hi Mike!

Thank you very much for your help.
It hasn indeed solved my problem.

On Aug 10, 4:23 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:

> query=session.query(Address)
> query=query.join((User, Address.user))
> query=query.join((Group, User.group))

Yes, and I indeed need this form to explicitly specify the resource.
In fact I use automatically generated aliases in my RUM application.
Users can specify abitrary nested query parameters in RUM like
occasion.type.name
and RUM generates them automatically.

http://hg.python-rum.org/RumAlchemy/rev/08e2338b4729

I am happy, that SA is such a very precise tool.

Chees,
Michael

[Michael Brickenstein]

just for the typo

> It hasn indeed  solved my problem.

It has solved my problem.
Everything works fine now :-).

Cheers,
Michael