How can I best combine “get categories” and “has user access”?

Question

I was wondering if it's possible to create a query for the following problem. I only want to select categories from the (MySQL) database (if the user has access).

• A user has multiple roles;
• A category has multiple roles.

I have the following tables:

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT ,
  email VARCHAR(127) NULL ,
  username VARCHAR(16) NULL ,
  password VARCHAR(64) NULL ,
  PRIMARY KEY (id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE roles_users (
  user_id INT(11) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (user_id, role_id)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE forum_categories (
  id INT(4) NOT NULL AUTO_INCREMENT ,
  name VARCHAR(30) NULL ,
  description VARCHAR(70) NULL ,
  image VARCHAR(20) NULL ,
  parent_id INT(4) NULL ,
  PRIMARY KEY (id) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

CREATE TABLE forum_categories_roles (
  category_id INT(4) NOT NULL ,
  role_id INT(11) NOT NULL ,
  PRIMARY KEY (category_id, role_id) ,
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

I removed the relations

Example:

• User A has role_id A
• User B has role_id B
• User C has role_id A and C

---

• category A has role_id A
• category B has role_id B and C
• category C has role_id C and B

---

• user A has access to category A

• user B has access to category B and C

• user C has access to category B and C

Answer 1

If you have the user id upfront, say id 12, then your query is

SELECT u.name username,GROUP_CONCAT('[',fc.name,']') categories
FROM (SELECT * FROM users WHERE id=12) u
INNER JOIN roles_users            ru  ON u.id = ru.user_id
INNER JOIN forum_categories_roles fcr ON ru.rol_id = fcr.rol_id
INNER JOIN forum_categories       fc  ON fcr.category_id = fc.id
GROUP BY u.name;

If you want to see all the categories for every user, then here is that query:

SELECT u.name username,GROUP_CONCAT('[',fc.name,']') categories
FROM users u
INNER JOIN roles_users            ru  ON u.id = ru.user_id
INNER JOIN forum_categories_roles fcr ON ru.rol_id = fcr.rol_id
INNER JOIN forum_categories       fc  ON fcr.category_id = fc.id
GROUP BY u.name;

Both of these queries return a comma-separated list of categories

I would recommend you also add an additional unique index

CREATE TABLE forum_categories_roles (   
  category_id INT(4) NOT NULL ,   
  role_id INT(11) NOT NULL ,   
  PRIMARY KEY (category_id, role_id) ,   
  UNIQUE KEY (role_id,category_id)
ENGINE = InnoDB   
DEFAULT CHARACTER SET = utf8;   

Give it a Try !!!