mysql join 5 tables

Question

Well guys, I have five tables here [sessions, users,users_2_groups,permissions_attribute and permissions]. For simplifying let's consider:

• sessions table has two columns: sid and userid
• users table has two columns : userid and user_type_id
• users_2_groups has two columns : userid and usergroupid
• permissions_attribute has two columns : perm_att_id and perm_att_label
• permissions table has five columns:permissionid,perm_att_id,perm_appendto,perm_appendid and permission_value

i have create a query to check if user have the right permission by his id[userid],group[usergroupid] and type[user_type_id] and it's work perfectly,but for one permission and i want to modify my query to support checking for one or more permissions. here is my query that support one permissions:

SELECT p.permissionid,f.userid,f.user_type_id,t.perm_att_id,t.perm_att_label,p.permission_value 
FROM 
(SELECT s.userid AS userid,u.user_type_id FROM sessions s,users u WHERE s.sid='f7b24e6cc4f9325c946d7c4522411ab1' AND u.userid=s.userid) AS f 
JOIN permissions_attribute AS t ON t.perm_att_label='show-dashboard'
JOIN permissions AS p ON (p.perm_att_id=t.perm_att_id AND p.perm_appendto='user' AND p.perm_appendid=f.userid AND p.permission_value=1)
OR (p.perm_att_id=t.perm_att_id AND p.perm_appendto='type' AND p.perm_appendid=f.user_type_id AND p.permission_value=1)
OR (p.perm_att_id=t.perm_att_id AND p.perm_appendto='group' AND p.perm_appendid IN (SELECT usergroupid FROM users_2_groups  WHERE userid=f.userid ) AND p.permission_value=1)

UPDATE: the permissions module work like this:

• select userid from sessions table by session id [sid]
• get the permissions that we need to check from permissions_attribute table where perm_att_label column hold the name of permissions [in my code its support only one,but i need to make it support more than one.

• we can assigned the permissions to user by three way:

  • by assigned permissions to the user directly [userid]
  • by assigned permissions to the group [usergroupid]
  • by assigned permissions to the user type [user_type_id] when i check for a permissions, query shall be search for them into all three way [user,group and type]. now i need to pass tow or more permissions label [perm_att_label] to check if its assigned or not?

Have I been clear? Thanks in advance for any help.

Answer 1

The readability of your query is pretty low. A couple of things to keep in mind when writing sql queries:

1. Don't use implicit joins (like sessions s,users u). This syntax is generally frowned upon.

2. Avoid SELECT (...) FROM (SELECT ... FROM) constructs. These are terrible for readability. If you must select something from another query, use WITH. In this case however, I don't see any reason you need this.

3. When joining tables, usually only add the joining columns in the ON clause. Use the WHERE clause for more general constraints like s.sid='f7b24e6cc4f9325c946d7c4522411ab1'

4. Try to avoid unnecessary duplicate code as much as possible. In this query, you duplicate a lot of conditions, for example p.permission_value=1

With this in mind, I rewrote your query. However, the solution to your question seems rather trivial. Is there any reason why this wouldn't work?

SELECT p.permissionid, u.userid, u.user_type_id, t.perm_att_id, t.perm_att_label, p.permission_value
FROM sessions s
INNER JOIN users u ON u.userid=s.userid
INNER JOIN permissions_attribute t ON t.perm_att_label='show-dashboard'
INNER JOIN permissions AS p ON p.perm_att_id=t.perm_att_id
WHERE s.sid='f7b24e6cc4f9325c946d7c4522411ab1'
AND p.permission_value IN (1,2,3,...) -- <- have you tried this?
AND ((p.perm_appendid=u.userid AND p.perm_appendto IN ('user','type'))
  OR (p.perm_appendto='group' AND p.perm_appendid IN 
        (SELECT usergroupid FROM users_2_groups WHERE userid = u.userid);