mysql - Querying to get objects from a many-to-many relationship -


i have user objects stored in users table.

i have permission objects stored in permissions table.

i have mtm_userspermissions table maps multiple user objects multiple permission objects. if user.id = 1 , permission.id = 10, , user has permission, there record in mtm_userspermissions id_a = 1 , id_b = 10.

i want permissions associated given user, knowing user id.

i have functional query: select permissions.id, permissions.name, permissions.title, permissions.description, permissions.owneruri permissions join otm_userspermissions on otm_userspermissions.id_b = permissions.id join users on otm_userspermissions.id_a = :user_id;

(where user_id id of user)

this seem retrieving permissions associated each user. however, each permission duplicated number of users present in users table. e.g., if user has 1 permission assigned, there 5 users total, correct permissions retrieved, there 5 of each.

i don't have experience join statements. have tried use answer working solution, something's still lacking. clean way foreign key objects in php mysql query

using mysql 5.5.

what missing?

ilmiont

you don't have join users table, otm_userspermissions table consists user id used filtering result:

select permissions.id, permissions.name, permissions.title, permissions.description, permissions.owneruri permissions join otm_userspermissions on otm_userspermissions.id_b = permissions.id otm_userspermissions.id_a = :user_id; 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -