In my model I have a company table and a person table and a
combination table that links the two together. Some companies allow
there people to maintain a profile text, in addition some companies
allow there people to also upload a picture of themselves. To meet
these requirements I defined a table profiletext and a table avatar
referencing the person table.
In my view I would like to list the people that work for a company,
and in case they wrote a profile text and/or uploaded a picture show
that picture. The problem is, I am struggling with the left join
needed to accomplish this. I tried:
print
db((db.companyperson.person_id==db.person.id)&(db.companyperson.bedrijf_id==2))
\
._select(db.person.first_name,db.person.last_name,db.profiletext.text,db.avatar.link,
\
left=((db.avatar.on(db.companyperson.person_id==db.avatar.person_id)&\
(db.profiletext.on(db.companyperson.person_id==db.profiletext.person_id))))
\
,orderby=db.profiletext.order)
Running this code results in the following error:
Traceback (most recent call last):
File "/Library/Python/2.5/site-packages/web2py_1.87.3/gluon/
restricted.py", line 188, in restricted
exec ccode in environment
File "/Library/Python/2.5/site-packages/web2py_1.87.3/applications/
demo/controllers/default.py", line 72, in <module>
File "/Library/Python/2.5/site-packages/web2py_1.87.3/gluon/
globals.py", line 96, in <lambda>
self._caller = lambda f: f()
File "/Library/Python/2.5/site-packages/web2py_1.87.3/applications/
demo/controllers/default.py", line 26, in print_team
(db.profieltekst.on(db.bedrijfcontactpersoon.contactpersoon_id==db.profieltekst.contactpersoon_id))))
\
TypeError: unsupported operand type(s) for &: 'SQLJoin' and 'SQLJoin'
This is the SQL query:
SELECT *
FROM companyperson JOIN person ON companyperson.person_id=person.id
LEFT JOIN profiletext ON companyperson.person_id=profiletext.person_id
LEFT JOIN
avatar ON companyperson.person_id=avatar.person_id
What is the correcet syntax for this query in web2py?
Kind regards,
Annet
