2

I'm building a tasks system wich has 4 tables:

tasks

id | user_id | end_date
-------------------------
2  | 1       | 2011-02-10

users

id | username
--------------
1  | johndoe
--------------
2  | janedoe

roles

id | role_name
--------------
1  | coordinator

and tasks_roles_users

id | task_id | user_id | role_id
---------------------------------
1  | 2       | 2       | 1

Each task has a creator (ie: johndoe is the owner of task #2), and each task has several users with different roles on that task, in my example "janedoe" is the task #2 coordinator. I'm stuck trying to show to "janedoe" and "johndoe" how many due tasks they have, and I'm having this problem since "johndoe" hasn't a role in the task, he's just the task owner. So how can I tell to both they have 1 task due?

Improve this question

4 Answers 4

Reset to default
2

You can accomplish this by doing a LEFT JOIN

SELECT u.id, u.username, 
    IFNULL(t.Cnt,0) OwnCount,
    IFNULL(tr.Cnt,0) RoleCount
    IFNULL(t.Cnt,0) + IFNULL(tr.Cnt,0) TotalCount
FROM users u LEFT JOIN (
    SELECT user_id, COUNT(*) cnt
    FROM tasks
    GROUP BY user_id
  ) t ON u.id = t.user_id
  LEFT JOIN (
    SELECT user_id, COUNT(*) cnt
    FROM tasks_roles_users
    GROUP BY user_id
  ) tr ON u.id = tr.user_id
WHERE t.user_id IS NOT NULL OR tr.user_id IS NOT NULL
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I believe that fetches exactly the information @richardocasares was looking for in a single query. Nice.
@ricardocasares Ur welcome! You can remove the GROUP BY on the last line, that was there by accident. Also, if the table grows, adding a index on user_id in both the tasks and tasks_roles_users will help.
@The Scrum Meister thank you! Have another one, if I want to filter by WHERE end_date < NOW(), to get due tasks, where's the best palce to put it?
place them into the 2 inner queries
@ricardocasares Since there is not end_date in the tasks_roles_users table, you will need to join the tasks table in the 2nd subquery
0

A simple way to do this is to add an owner role and treat it like any other role. Another way would be to use a UNION.

Improve this answer

Comments

0 
SELECT COUNT(*) FROM tasks_roles_users WHERE user_id = "2"

That will get all tasks that "janedoe" has a roll in.

If there aren't very many rolls (say, less that 8), you might want to save the rolls as constants in your code, instead of making SQL queries for such a small thing.

define('ROLL_COORDINATOR', 1); // just an integer unique from other roll constants
Improve this answer

5 Comments

That will return a row from tasks_roles_users so you'll get a task_id but not a row from tasks.
@MDaubs: good point, using mysql_num_rows could be avoided by a SQL change.
Yes, I need to get the records where the tasks owner has not a role in. I think the best is to: a) add a task_owner role b) use a UNION as jswolf19 suggest, i'm trying to achieve this now
Wait, you want to get the number of tasks a user owns, but doesn't have a roll in? Oops, I thought it was the other way around.
No, I need both count. I'm trying to get how many due tasks each user has, so I've tried this: SELECT COUNT(*) FROM tasks JOIN tasks_roles_users ON tasks_roles_users.user_id = 1 WHERE tasks.user_id = 1 AND tasks.end_date < NOW() GROUP BY tasks.id, but I feel there's something wrong with it
0 
SELECT COUNT(*) FROM tasks_roles_users WHERE user_id IN (SELECT id FROM users WHERE username = 'johndoe')
SELECT COUNT(*) FROM tasks_roles_users WHERE user_id IN (SELECT id FROM users WHERE username = 'janedoe')
Improve this answer

Comments

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.