0

I have a table of records that are only viewable by users with the correct group permission. The records may be viewable by multiple groups. Users may also be in multiple groups.

record

+----+-------------------+
| id |    record_data    |
+----+-------------------+
|  1 | Some sample data  |
|  2 | More data         |
|  3 | Some more data    |
+----+-------------------+

groups

+----+-------------+
| id | description |
+----+-------------+
|  1 | Group A     |
|  2 | Group B     |
|  3 | Group C     |
|  4 | Group D     |
+----+-------------+

user

+----+------+
| id | name |
+----+------+
|  1 | Levi |
|  2 | Bill |
|  3 | Sam  |
|  4 | Jen  |
|  5 | Amy  |
+----+------+

I have created lookup tables to match the record to the groups that have permission to view them. I have also created a lookup table that matched the users with the groups they are members of.

record_group

+----+-----------+----------+
| id | record_id | group_id |
+----+-----------+----------+
|  1 |         1 |        2 |
|  2 |         1 |        4 |
|  3 |         2 |        1 |
|  4 |         3 |        2 |
|  5 |         3 |        3 |
|  6 |         3 |        4 |
+----+-----------+----------+

So in the table above, record one is viewable by users in group 2 and 4. Record 2 is viewable by users in group 1, and record 3 is viewable by users in group 2,3 and 4.

user_group

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
|  1 |       1 |        1 |
|  2 |       1 |        2 |
|  3 |       1 |        4 |
|  4 |       2 |        1 |
|  5 |       2 |        4 |
+----+---------+----------+

Above, User 1 is in group 1,2 and 4. User 2 is in group 1 and 4.

I am trying to create a query that will show all the records that a particular user has the correct group permissions to view. Example. I want to list all the records that user ID 2 can view and ignore the others.

The query for user 2 should return record 2,3 and 6

I need to do this in a single query as it will be plugged into an existing framework.

My understanding is that I can do this with the MySQL IN function but I cant seem to get it to work with two sets, example (Select id from member_groups) IN ()

So my Question, how do I build a MySQL query that selects all the records user one has permissions to view?

I'm sorry I cant provide more information or example query, I'm not sure how to start.

SQL Fiddle http://sqlfiddle.com/#!9/e40ee6/1

1
SELECT DISTINCT record.id AS id, details from record 
LEFT JOIN record_group ON record.id = record_group.record_id
WHERE record_group.group_id IN (SELECT group_id from user_group WHERE user_id = 2)

http://sqlfiddle.com/#!9/e40ee6/12

0
0

I would use only joins as conditions of the form WHERE column IN (SELECT ... FROm other_table) are not fully optimized in older versions (before 5.6 - 5.7):

SELECT DISTINCT 
    r.id, r.details 
FROM record AS r
  JOIN record_group AS rg ON r.id = rg.record_id
  JOIN user_group AS ug ON rg.group_id = ug.group_id
WHERE ug.user_id = 2 ;

or this (you may need to disable the ONLY_FULL_GROUP_BY sql mode setting):

SELECT  
    r.id, r.details 
FROM record AS r
  JOIN record_group AS rg ON r.id = rg.record_id
  JOIN user_group AS ug ON rg.group_id = ug.group_id
WHERE ug.user_id = 2 
GROUP BY r.id ;

This would also work but I wouldn't expect improved efficiency:

SELECT r.id, r.details 
FROM record AS r
WHERE EXISTS 
      ( SELECT 1
        FROM record_group AS rg 
          JOIN user_group AS ug ON rg.group_id = ug.group_id
        WHERE ug.user_id = 2 
          AND r.id = rg.record_id
       ) ;

A composite index on user_group (user_id, group_id) would help any of these queries (including yours).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.