For starters, look closely. db(db.family_members.user_id == auth.user_id and db.family_members.connected_to==db.auth_user.id).select(db.family_members.connected_to, db.auth_user.last_name, db.auth_user.first_name)
Also read the text here http://web2py.com/books/default/chapter/29/06#Logical-operators On Friday, March 29, 2013 7:34:55 AM UTC-4, Dragan Matic wrote: > > I have a table that shows family connections for users (I need one way > connections) defined as: > > db.define_table('family_members', > Field('user_id', 'integer'), > Field('connected_to', 'integer')) > > I need to read and to show all users that are connected to a logged user, > basically an inner join between auth_user and family_members table. This > select is giving me the following sql query: > > family_members_set = db(db.family_members.user_id == auth.user_id and db. > family_members.connected_to==db.auth_user.id).select(db.family_members. > connected_to, db.auth_user.last_name, db.auth_user.first_name) > > SELECT family_members.connected_to, auth_user.last_name, auth_user.first_name > > FROM auth_user, family_members > WHERE (family_members.connected_to = auth_user.id); > > According to documentation I think it should have created this query: > > SELECT family_members.connected_to, auth_user.last_name, auth_user.first_name > > FROM auth_user, family_members > WHERE (family_members.connected_to = auth_user.id and family_members. > user_id=2) > > > Obviously it is ignoring that I want to select only rows for current_user. > > When I explicitly say it to use join, it generates the correct sql: > > family_members_set = db(auth.user_id==db.family_members.user_id).select(db > .family_members.connected_to, db.auth_user.last_name, db.auth_user. > first_name, > join=db.family_members.on(db.family_members.connected_to==db. > auth_user.id)) > > SELECT family_members.connected_to, auth_user.last_name, auth_user.first_name > FROM auth_user JOIN family_members ON (family_members.connected_to =auth_user > .id) WHERE (family_members.user_id = 2); > > What am I doing wrong in the first query? > > > -- --- 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/groups/opt_out.
