Mysql – Help with complicated MySQL query

MySQLquery

I asked an earlier question about modeling MySQL data modeling help. The recommendation was the diagram labeled Multiple Tables for Role and Sponsor. Multiple Tables for Role and Sponsor

Now I'm trying to write the queries and struggling. It's the point where I kept giving up and looking at maybe easier database models, but now I know that won't work and I need some advice.

When I have an event_id and a user_id, I need to check whether this leader is allowed to edit this event. They are allowed if

  • Are the user_id and event_id is in the user_event_role table?
  • If this event also has a project_id, is the user in the user_project_role table for the project that runs this event?
  • Is the user in the user_organization_role table for the organizations that sponsor this project (check the project_sponsor table)?
  • Is the user in the user_organization_role table for the organizations that sponsor this event (check the event_sponsor table)?

If this user_id is found in any of these leader positions for the event, project, or organizations sponsoring the event or project, then it's okay for this user/leader to edit.

I'm not sure how to write such a complicated query. The following query insists that the user_id be present in all three user_role tables when it only has to be present in one. 

SELECT count(*)
FROM user AS u
JOIN user_event_role AS uer ON (uer.user_id=u.id)
JOIN user_project_role AS upr ON (upr.user_id=uer.id)
JOIN user_organization_role AS uor ON (uor.user_id=upr.user_id)
JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
JOIN project_sponsor AS ps ON (ps.project_id=upr.project_id)
WHERE
  uer.term_to >= CURRENT_DATE() AND
  upr.term_to >= CURRENT_DATE() AND
  uor.term_to >= CURRENT_DATE() AND
  u.netid='{$netid}'

Do I need to do something as complicated as this union:

  SELECT count(*)
  FROM user AS u
  JOIN user_event_role AS uer ON (uer.user_id=u.id)
  WHERE
    uer.event_id=$event_id AND
    uer.term_to >= CURRENT_DATE() AND
    uer.role != 'volunteer' AND
    u.netid='{$netid}'
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_project_role AS upr ON (upr.user_id=u.id)
  JOIN event AS e ON (e.project_id=upr.project_id)
  WHERE
    e.id=$event_id AND
    upr.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'                      
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN event_sponsor AS es ON (es.organization_id=uor.organization_id)
  WHERE
    e.id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'      
UNION
  SELECT count(*)
  FROM user AS u
  JOIN user_organization_role AS uor ON (uor.user_id=u.id)
  JOIN project_sponsor AS ps ON (ps.organization_id=uor.organization_id)        
  WHERE
    uer.event_id=$event_id AND
    uor.term_to >= CURRENT_DATE() AND
    u.netid='{$netid}'

Is this the best approach? Is there a way to simplify it?

Best Answer

Have you considered using a Stored Function ?

You can split the union into your individual queries and if it passes any one of the three, return count(*). Otherwise, return 0

Some restrictions on stored functions

I recommended function over procedure because it's invoked like:

SELECT myfunc(userID, eventID);

and your query doesn't look like it needs a resultset returned, just a single value. If it needs a resultset, you would need a procedure (as noted in the restrictions documentation)

Related Question