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

share|improve this question
    
I changed the store proc in my answer. Please try it again !!! –  RolandoMySQLDBA May 28 '11 at 22:02
    
I added another stored procedure that does not use the UNION clause. –  RolandoMySQLDBA May 29 '11 at 22:07
add comment

3 Answers 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)

share|improve this answer
add comment

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.

share|improve this answer
    
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 May 27 '11 at 2:52
add comment

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.

  1. Count the distinct userids
  2. Refactor each subquery on the inline subquery for users
  3. 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 ;
share|improve this answer
    
@DTest, thanks for suggesting a Stored Function. That should speed it up a lot. –  dac May 26 '11 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 May 27 '11 at 0:47
    
I tried it and got the following error: "Incorrect usage of UNION and INTO". –  dac May 27 '11 at 2:37
    
@dac Did the error come from making the stored function or running it ? What version of MySQL are you running ? –  RolandoMySQLDBA May 27 '11 at 2:50
add comment

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.