Help with complicated MySQL query

Question

I asked an earlier question about modeling MySQL data modeling help. The recommendation was the diagram labeled 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?

I changed the store proc in my answer. Please try it again !!! — RolandoMySQLDBA
– RolandoMySQLDBA, Commented May 28, 2011 at 22:02
I added another stored procedure that does not use the UNION clause. — RolandoMySQLDBA
– RolandoMySQLDBA, Commented May 29, 2011 at 22:07

Derek Downey · Accepted Answer · 2011-05-25 13:54:27Z

3

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)

answered May 25, 2011 at 13:54

Derek Downey

23.6k11 gold badges79 silver badges104 bronze badges

Add a comment |

RolandoMySQLDBA · Accepted Answer · 2011-05-29 22:05:44Z

Here is your original UNION query

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}'

You need to make three changes.

Count the distinct userids
Refactor each subquery on the inline subquery for users
Index the tables to better support the term_to columns

Here is the Refactored Query:

SELECT DISTINCT id
FROM (
SELECT u.id
  FROM (SELECT id FROM user WHERE netid='{$netid}') 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'
UNION
  SELECT u.id
  FROM (SELECT id FROM user WHERE netid='{$netid}') 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()           
UNION
  SELECT u.id
  FROM (SELECT id FROM user WHERE netid='{$netid}') 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()
UNION
  SELECT u.id
  FROM (SELECT id FROM user WHERE netid='{$netid}') 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()
) userids ORDER BY id;

Here are the Indexes You Need to Speed Up the Subqueries:

ALTER TABLE user_event_role ADD INDEX (event_id,term_to,role);
ALTER TABLE user_project_role ADD INDEX (term_to);
ALTER TABLE user_organization_role ADD INDEX (term_to);

Interestingly, @DTest gave a good idea in his answer. Make a Stored Function. However you have to pass in three parameters: NetID,UserID,EventID. Here is that Stored Function DoesUserHaveEditPrivs using the new refactored query:

DELIMITER $$

DROP FUNCTION IF EXISTS DoesUserHaveEditPrivs $$
CREATE FUNCTION DoesUserHaveEditPrivs
(GivenNetID INT,GivenUserID INT,GivenEventID INT) RETURNS INT
BEGIN

  DECLARE rv INT;
  DECLARE UserID_Tag VARCHAR(32);
  DECLARE UserID_CSV VARCHAR(1024);

  SET rv = 1;
  SELECT idlist INTO UserID_CSV FROM (
    SELECT CONCAT('.',GROUP_CONCAT(DISTINCT id SEPARATOR '.'),'.') idlist
    FROM (
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) AS u
      JOIN user_event_role AS uer ON (uer.user_id=u.id)
      WHERE
        uer.event_id=GivenEventID AND
        uer.term_to >= CURRENT_DATE() AND
        uer.role != 'volunteer'
    UNION
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) 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=GivenEventID AND
        upr.term_to >= CURRENT_DATE()
    UNION
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) 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=GivenEventID AND
        uor.term_to >= CURRENT_DATE()
    UNION
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) 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=GivenEventID AND
        uor.term_to >= CURRENT_DATE()
    ) userids_unioned ORDER BY id
  ) userids;

  SET UserID_Tag = CONCAT('.',GivenUserID,'.');

  IF LOCATE(UserID_Tag,UserID_CSV) THEN
    SET rv = 0;
  END IF;

  RETURN rv;

END $$

DELIMITER ;

Here is what the Stored Function Does: It first creates a period separated list of Unique UserIDs from the Refactored Query called UserID_CSV using the GROUP_CONCAT function. An extra period is prepended and appended to UserID_CSV. It then creates another variable called UserID_Tag which has a string contains the GivenUserID surrounded by periods. I then use the LOCATE function to get the index position of the UserID_Tag as located in UserID_CSV. If LOCATE returns 0, function returns 0. Otherwise, it returns 1.

Give it a Try !!! (@DTest, +1 for suggesting the Stored Function)

UPDATE 2011-05-28 18:00

I find your error unusual since I create the storted procedure in MySQL Query Browser with no compiler errors. I changed the code to put the UNION queries in another subquery. Try it again, please !!!

UPDATE 2011-05-28 17:52

I have an alternate solution via the stored procedure. I eliminated the use of UNION and piled up unique ids in a temp table:

DELIMITER $$

DROP FUNCTION IF EXISTS DoesUserHaveEditPrivs $$
CREATE FUNCTION DoesUserHaveEditPrivs
(GivenNetID INT,GivenUserID INT,GivenEventID INT) RETURNS INT
BEGIN

  DECLARE rv INT;
  DECLARE UserID_Tag VARCHAR(32);
  DECLARE UserID_CSV VARCHAR(1024);

  DROP TABLE IF EXIST idlist;
  CREATE TEMPORARY sfTABLE idlist (id INT,PRIMARY KEY (ID)) ENGINE=MEMORY;
  SET rv = 1;
  INSERT IGNORE INTO idlist
  SELECT id FROM (
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) AS u
      JOIN user_event_role AS uer ON (uer.user_id=u.id)
      WHERE
        uer.event_id=GivenEventID AND
        uer.term_to >= CURRENT_DATE() AND
        uer.role != 'volunteer'
  );
  INSERT IGNORE INTO idlist
  SELECT id FROM (
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) 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=GivenEventID AND
        upr.term_to >= CURRENT_DATE()
  );
  INSERT IGNORE INTO idlist
  SELECT id FROM (
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) 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=GivenEventID AND
        uor.term_to >= CURRENT_DATE()
  );
  INSERT IGNORE INTO idlist
  SELECT id FROM (
      SELECT u.id
      FROM (SELECT id FROM user WHERE netid=GivenNetID) 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=GivenEventID AND
        uor.term_to >= CURRENT_DATE()
  ;
  SELECT CONCAT('.',GROUP_CONCAT(id SEPARATOR '.'),'.') FROM idlist;

  SET UserID_Tag = CONCAT('.',GivenUserID,'.');

  IF LOCATE(UserID_Tag,UserID_CSV) THEN
    SET rv = 0;
  END IF;

  RETURN rv;

END $$

DELIMITER ;

@DTest, thanks for suggesting a Stored Function. That should speed it up a lot. — dac
– dac, Commented May 26, 2011 at 23:26
thanks for your work in refactoring the query and then converting it to a Stored Function. Something I'm wondering about: The UserID parameter to the function. That information isn't available until the query finds the UserId for the user who has the NetID. — dac
– dac, Commented May 27, 2011 at 0:47
I tried it and got the following error: "Incorrect usage of UNION and INTO". — dac
– dac, Commented May 27, 2011 at 2:37
@dac Did the error come from making the stored function or running it ? What version of MySQL are you running ? — RolandoMySQLDBA
– RolandoMySQLDBA, Commented May 27, 2011 at 2:50

Michael Riley - AKA Gunny · Accepted Answer · 2011-05-25 13:40:26Z

2

This is the perfect situation for using Views. I'd create a view called viewEditableEvents which filters only those events that can be edited by leaders. Then you can write your SQL query against the view.

answered May 25, 2011 at 13:40

Michael Riley - AKA Gunny

9452 gold badges7 silver badges20 bronze badges

this may solve an upcoming problem. If I create a view for everything a user can edit (events, projects, organizations), then I check the view to see whether this user can edit the particular event, project, or organization. A view brings all the data together to make queries easy from both directions: (starting with netid only or with netid and one of the following--event_id, project_id, or organization_id). Maybe use it with a stored function that passes in the relevant id's.

dac
– dac

2011-05-27 02:52:29 +00:00
Commented May 27, 2011 at 2:52

Add a comment |

Stack Exchange Network

Help with complicated MySQL query

3 Answers 3

Your Answer

Linked

Hot Network Questions

Help with complicated MySQL query

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions