Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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 !!!

share|improve this answer
    
It's not exactly what I was looking for but you put me in the right direction. Thanks :) –  Niels Kootstra Dec 6 '11 at 23:29
    
Please specify exactly what kind out output are you looking for. I can try to adjust my queries accordingly. –  RolandoMySQLDBA Dec 6 '11 at 23:30
    
Hey, Thank you for accepting. Nonetheless, please edit the show me an example of the kind of output you are looking for, along with any inputs you want the query to have. –  RolandoMySQLDBA Dec 6 '11 at 23:32
    
Sometimes it's a lost cause. You asked, he's seen it in the dropdown if he's logged back in. Good work on giving him new direction and letting him sort it out. –  jcolebrand Dec 7 '11 at 0:47
    
gist.github.com/6b58d79f290353b4d59d The first query is for users (i used 'user_id' 2) and the second one is for guests (the ones that don't have any roles) –  Niels Kootstra Dec 7 '11 at 14:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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