Web2py multiple table joins

[mfarees...@gmail.com]

Hi

I want to write a query in web2py. The purpose of this query is to return all users that belong to a user group specified in the query. The additional data I want with each user is his organization (if any), managing_countries (if any), and shops (if any). Note that a user might either own a shop or work in an organization, but not both. A person working in an organization will also have a record in the managing_countries table.

Here is an ERD:

I wrote a query but I did not get the desired results. It is returning data even when it is not present, so I think a left outer join is required here.

auth_managing_countries = db(db.managing_countries.user_id == auth.user.id).select().first().countries

user_role_clause = ((db.auth_group.role == UserGroups.employee) |

            (db.auth_group.role == UserGroups.owner))

auth_id_clause= user_role_clause &

            (db.auth_group.id == db.auth_membership.group_id) &

            (db.auth_membership.user_id == db.auth_user.id)

managing_countries_clause = (db.managing_countries.user_id == db.auth_user.id) &

                          (db.managing_countries.countries.belongs(auth_managing_countries))

shop_country_clause = ((db.shop.country.belongs(auth_managing_countries)) &

           (db.user_shop.shop_id == db.shop.id) &

           (db.user_shop.user_id == db.auth_user.id))

query = auth_id_clause & managing_countries_clause | shop_country_clause

db(query).select()

Thanks

[Val K]

Maybe try to replace db.auth_user.id  with  auth.user.id or just auth.user_id