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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.