Double left join syntax

[Lieven Van Acker]

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

[Richard]

Here some progress : https://github.com/web2py/pydal/issues/521

[Anthony]

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)],

Looks like the cross join might be a separate issue. But regarding the left joins, if using aliases, you will have to manually write the SQL for the "ON" clause rather than using DAL syntax:

db.Contact.with_alias('Owner').on('Owner.id = OnResource.Contact')

Anthony

[Anthony]

Correction, you can also do something like:

db.Contact.with_alias('Owner').on(db.Owner.id == db.OnResource.Contact)

Anthony