Need a SQL guru for complex query

Question

I have several tables from which I am trying to get some data, and I am very close, but cannot close the deal.

I have the following tables:

• EVENT
• USER
• FRIEND
• USER__FRIEND
• EVENT__INVITATION

USER and FRIEND are linked via table USER__FRIEND (which contains USER_ID and field FRIEND_ID)

EVENT__INVITATION associates an event with FRIENDS (it has EVENT_ID and INVITEE_ID)

I am trying to get all EVENTS where:

• I am the creator of EVENT ($ myUserID = EVENT.CREATOR_ID)
• or I'm being invited to an event ($ myUserID = EVENT__INVITATION.INVITEE_ID)
• or one of my OTHERS is the creator of EVENT ($ myUserID = USER__FRIEND.USER_ID AND EVENT.CREATOR_ID IN (my friends list))
• or one of my FRIENDS is invited to an event ($ myUserID = USER__FRIEND.USER_ID AND EVENT__INVITATION.INVITEE_ID IN (list of my friends))

There are other WHERE conditions around other parameters, but I think I can sort them out myself.

Currently, the only way to get this to work is with the UNION, which I think should be compensated, and if I had the best chops, I could use it.

So the question is, can this be done with a single low-cost query that does not use UNION?

Here is what I have done so far, with the exception of the EVENT events that my friends have invited (23 is the user ID in this case):

SELECT e.* FROM event e LEFT JOIN event__invitation ei ON ei.event_id = e.id LEFT JOIN user__friend uf ON uf.friend_id = ei.invitee_id LEFT JOIN friend f ON f.id = uf.friend_id WHERE (ei.invitee_id = 23 OR e.creator_id = 23 OR uf.user_id = 23 OR f.user_id = e.creator_id) AND e.start_time >= 1348000000 

and this is a query with UNION:

 SELECT e.* FROM event e INNER JOIN event__invitation ei ON ei.event_id = e.id INNER JOIN user__friend uf ON uf.friend_id = ei.invitee_id WHERE (e.creator_id = 23 OR ei.invitee_id = 23 OR uf.user_id = 23) UNION SELECT e1.* FROM event e1 WHERE e1.creator_id IN ( SELECT f1.user_id FROM friend f1 INNER JOIN user__friend uf1 ON uf1.friend_id = f1.id WHERE uf1.user_id = 23 AND f1.user_id IS NOT NULL ); 

The request contains more requests that make using UNION undesirable. I have a complex trigger calculation, which I do mainly select, and order the results by this value. I think it can ruin the result set.

Thanks for any help!

Answer 1

How about the following:

 -- take only distinct events SELECT DISTINCT e.* -- start with the event FROM event e -- expand to include all invitees and their user_friend info LEFT JOIN event__invitation ei ON ei.event_id = e.id LEFT JOIN user__friend invitee ON invitee.friend_id = ei.invitee_id -- now we join again to user_friend to get the friends of the invitees/the creator LEFT JOIN user__friend invitedFriend ON invitedFriend.user_id = invitee.user_id OR invitedFriend.user_id = e.creator_id -- finally we match on whether any of these friends of friends are myself LEFT JOIN friend myselfAsAFriend ON myselfAsAFriend.id = invitedFriend.friendId AND myselfAsAFriend.userID = 23 WHERE ( -- (1) I am the creator of the event e.creator_id = 23 -- (2) I am invited to the event OR invitee.user_id = 23 -- (3 and 4) for this join to match a friend of mine must be invited or the creator OR myselfAsAFriend.id IS NOT NULL ) AND e.start_time >= 1348000000 

Answer 2

Currently, I am not very good at query settings, so I would just ask for something like this and ask the optimizer to make every effort to figure out the best way to get the results:

 SELECT DISTINCT e.* FROM event e INNER JOIN event__invitation ei ON ei.event_id = e.id INNER JOIN ( SELECT friend_id FROM user__friend WHERE user_id = $myUserID UNION ALL SELECT $myUserID ) u ON u.friend_id IN (e.creator_id, ei.invitee_id) ; 

If this is not efficient enough, you can always go with the @ChaseMedallion suggestion, as this may indeed turn out to be the best for your case.