Here some progress : https://github.com/web2py/pydal/issues/521
Le mercredi 8 avril 2015 08:40:26 UTC-4, Lieven Van Acker a écrit :
>
>
> Hello all,
>
>
> I'm trying to perform a double left join, but can't seem to figure out the
> right syntax. Maybe someone could shed some light...
>
>
> This is what should be the resulting MySQL syntax:
>
>
> SELECT *
> FROM
> OnResource
> LEFT JOIN Contact Owner ON Owner.id = OnResource.Contact
> LEFT JOIN Contact Proxy ON Proxy.id = OnResource.Proxy
> ORDER BY
> Owner.LastName, Owner.FirstName
>
>
>
>
> I tried this DAL select syntax
>
>
> timesheet_owners = db().select(db.OnResource.ALL,
> db.Contact.with_alias('Owner').ALL,
> db.Contact.with_alias('Proxy').ALL,
> left=[db.Contact.with_alias('Owner').on(db.Contact.id ==
> db.OnResource.Contact),
> db.Contact.with_alias('Proxy').on(db.Contact.id ==
> db.OnResource.Proxy)],
> orderby=db.Contact.with_alias('Owner').LastName +
> db.Contact.with_alias('Owner').FirstName)
>
>
>
> But this results in following MySQL Query:
>
>
> SELECT
> OnResource.id, OnResource.Contact, OnResource.Proxy, OnResource.Manager,
> OnResource.CID, OnResource.UserID, OnResource.Category,
> OnResource.Department, OnResource.ResourcePool, OnResource.StartDay,
> OnResource.EndDay, OnResource.JobDescription,
> OnResource.EducationDegree, Owner.id, Owner.Title, Owner.Gender,
> Owner.FirstName, Owner.LastName, Owner.EMail, Owner.ContactLocation,
> Proxy.id, Proxy.Title, Proxy.Gender, Proxy.FirstName, Proxy.LastName,
> Proxy.EMail, Proxy.ContactLocation
> FROM
> OnResource
> CROSS JOIN Contact
> LEFT JOIN Contact AS Owner ON (Contact.id = OnResource.Contact)
> LEFT JOIN Contact AS Proxy ON (Contact.id = OnResource.Proxy)
> ORDER BY
> CONCAT(Owner.LastName,Owner.FirstName)
>
>
> Could you please advise how to get rid of the cross join?
>
> Thanks,
>
> Lieven
>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
