2

Im wondering how can I achieve a successful query for a hierarchy structure i have with my users. I had posted a similar question but the structure changed where now only Executives can have costumers assigned to them.

There are this levels of user:

  1. Director
  2. Manager
  3. Executive

Example table USERS:

ID    username    privilege   parent_ID
1     Director1   1           null
2     Director2   1           null
3     Manager1    2           1
4     Manager2    2           1
5     Manager3    2           2
6     Executive1  3           3
7     Executive2  3           3
8     Executive3  3           4
9     Executive4  3           4
10    Executive5  3           5
11    Executive6  3           5

And they will have their "costumers".

Example table COSTUMERS

ID  name  User_ID
1   c1    11
2   c2    10
3   c3    10
4   c4    9
5   c5    8
6   c6    7
7   c7    6

My problem is into what kind of join should i make so that every user will be able to see only the costumers that they are allowed to, which the rule would be that they can only see costumers from the executives below them, and executives will be able to see their own costumers only.

Diagram

For example in the diagram above, if users was to check his costumers he should see:

Director1: C7,C6,C5,C4

Director2: C3,C2,C1

Manager1: C7,C6

Manager2: C5,C4

Manager3: C3,C2,C1

and the executives only would see their own costumers.

Improve this question

1 Answer 1

Reset to default
2

The proper way to solve this is with a recursive CTE query, which are coming in MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/with.html

WITH RECURSIVE h AS (
  SELECT ID FROM USERS WHERE ID = ?
  UNION
  SELECT ID FROM USERS AS u JOIN h ON u.parent_ID = h.ID
)
SELECT c.*
FROM h
JOIN COSTUMERS AS c ON c.User_ID = h.ID;

If you're still using MySQL 5.7 or older, you need to do it more awkwardly. You have one advantage, that your hierarchy has a fixed maximum depth.

SELECT c.*
FROM (
    SELECT e.ID FROM USERS AS e
    WHERE e.ID = ?
    UNION ALL 
    SELECT e.ID FROM USERS AS e 
    JOIN USERS AS m ON e.parent_ID = m.ID
    WHERE m.ID = ?
    UNION ALL 
    SELECT e.ID FROM USERS AS e 
    JOIN USERS AS m ON e.parent_ID = m.ID
    JOIN USERS AS d ON m.parent_ID = d.ID
    WHERE d.ID = ?
) AS h
JOIN COSTUMERS AS c ON c.User_ID = h.ID;

I'm assuming that restructuring your hierarchy into another design like Closure Table isn't a possibility. But if you're interested in other designs, see my answer to What is the most efficient/elegant way to parse a flat table into a tree?

Or my presentation https://www.slideshare.net/billkarwin/models-for-hierarchical-data

Or my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, do you happen to know the advatnages on using MYSQL 8? should i use the dev version for this? is the version 5.7 too resource consuming that it might be a good idea on using it or even waiting for 8.0?
also, im reading your presentation, it isn't outside of the scope to restructure anything since i am in a phase where im structuring all my database, so i can still make changes. would closure table be better?
MySQL 8.0 is still in Release Candidate status currently. It's too early to tell when it will be ready for production use, so you should test carefully. There's a running joke in the MySQL community to avoid adopting a new major version until it reaches its ".20" release, i.e. MySQL 8.0.20. Then it should have major bugs and rough bits resolved. This rule comes from the former Director of Development for MySQL: flamingspork.com/blog/2013/08/01/stewarts-dot-twenty-rule
As for Closure Table, I like that design, and I have used it successfully. But each design has pros and cons. Like any architecture design, you need to use the one that supports your application the best. I can't answer that for you.
Thanks for your answer, i just noticed i never accepted it as so. Since I have a the fixed depth and i'm sure it will never change i will go with the second query structure you proposed. Do you happen to know a way into how i could manage to check if a user has permission to edit a customer? i am doing an API out of this with slim3 framework and oauth2 included

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.