How can I best combine "get categories" and "has user access"? 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
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 !!!