Maybe try to replace db.auth_user.id with auth.user.id or just auth.user_id
On Friday, December 9, 2016 at 9:13:27 AM UTC+3, [email protected] wrote: > > 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: > > > <https://lh3.googleusercontent.com/-yyy8pQFdA5g/WEpNdsNwCbI/AAAAAAAAABE/LRjpfs-dJzA8taK7zUZSk0IZXQ7hW4-dwCLcB/s1600/User%2BQuery.png> > > > 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 > -- 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.
