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 web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
