需要SQL专家进行复杂的查询 [英] Need SQL guru for a complex query

问题描述

我有几个表正在尝试从中获取一些数据，我已经很接近了，但是不能完全达成交易.

I have several tables that I am trying to get some data out of, and I am very close, but cannot quite close the deal.

我有下表:

• 事件
• USER
• 朋友
• USER__FRIEND
• 事件__邀请

USER和FRIEND通过USER__FRIEND表(包含USER_ID和FRIEND_ID字段)进行链接

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

EVENT__INVITATION将一个事件与一个朋友链接(它具有EVENT_ID和INVITEE_ID)

EVENT__INVITATION links an EVENT with a FRIEND (it has EVENT_ID and INVITEE_ID)

我正在尝试获取所有活动，其中:

I am trying to get all EVENTS where:

• 我是活动创建者($ myUserID = EVENT.CREATOR_ID)
• 或者我被邀请参加活动($ myUserID = EVENT__INVITATION.INVITEE_ID)
• 或者我的一个朋友是事件的创建者($ myUserID = USER__FRIEND.USER_ID和EVENT.CREATOR_ID IN(我的朋友的列表))
• 或我的一位朋友被邀请参加活动($ myUserID = USER__FRIEND.USER_ID和EVENT__INVITATION.INVITEE_ID IN(我的朋友列表))

围绕其他参数还有其他一些WHERE条件，但我认为我可以自己解决这些问题.

There are some other WHERE conditions around other parameters, but I think I can sort those out on my own.

现在，我唯一能使它起作用的方法是与UNION一起工作，我认为这应该是一个解决方案，如果我有更好的排骨，我可以解决这个问题.

Right now the only way I could get this to work was with a UNION, which I think must be a cop-out, and if I had better chops I could get around using it.

因此，问题是，这可以通过不使用UNION的单个廉价查询来完成吗?

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

到目前为止，这里是我所能做的，除了邀请我的朋友参加的活动(在这种情况下，23是传入的userID)之外，它可以完成所有工作:

Here is what I have so far, which accomplishes everything except the EVENTs that my FRIENDs are invited to (23 is the passed in userID 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

这是带有UNION的查询:

and this is the query with the 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
);

查询中还有更多内容使UNION的使用不受欢迎.我在主选择中执行了一个复杂的触发计算，并根据该值对结果进行排序.我认为可能会弄乱结果集.

There is more to the query that makes the use of the UNION undesireable. I have a complex trig calculation that I am doing in the main select, and am ordering the results by that value. I think may mess up the result set.

感谢您的帮助！

推荐答案

有关以下内容:

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

这篇关于需要SQL专家进行复杂的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！