如何将这两个复杂的查询合并为一个没有联合的查询？

Question

(SELECT posts.id FROM posts 
INNER JOIN discussions ON discussions.post_id = posts.id 
INNER JOIN companies ON discussions.company_id = companies.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = companies.id AND subscriptions.subscribable_type = 'Company' 
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6)

UNION 

(SELECT posts.id FROM posts 
INNER JOIN users users_2 ON posts.analyst_id = users_2.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = users_2.id AND subscriptions.subscribable_type = 'User'
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6)

很明显，两个查询中的最后一个连接是相同的。只是不确定如何“或”在一起连接。

Answer 1

有些连接似乎是多余的：

• discussions 可以直接在 company_id 列上连接到 subscriptions；

• posts 可以直接在 analyst_id 列上加入subscriptions；

• 在任一 SELECT 中最后一次加入 users 是不必要的，因为没有从该表中检索到任何数据，并且可以重新应用过滤器 (users.id = 6)到 subscriptions.user_id。

所以，我可能会像这样重写查询：

SELECT p.id
FROM posts p
  INNER JOIN discussions d ON d.post_id = p.id
  INNER JOIN subscription s
    ON s.subscribable_type = 'Company' AND s.subscribable_id = d.company_id
    OR s.subscribable_type = 'User'    AND s.subscribable_id = p.analyst_id
WHERE s.user_id = 6

Answer 2

这是未经测试的，但请尝试一下。让我知道它是否有效。

select posts.id
from posts
inner join discussions
on discussions.post_id = posts.id
inner join companies
on discussions.company_id = companies.id
inner join subscriptions
on subscriptions.subcribable_id = companies.id
inner join users
on subscriptions.user_id = users.id
or users.id = posts.analysis_id
where subscriptions.subscribable_type in ('Company', 'User')
and users.id = 6

Answer 3

我认为我忽略了一些差异，但如果您只想将帖子放在第一个或第二个而不使用 UNION 那么：

SELECT posts.id FROM posts
where posts.id IN 
(
SELECT posts.id FROM posts
INNER JOIN discussions ON discussions.post_id = posts.id 
INNER JOIN companies ON discussions.company_id = companies.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = companies.id AND subscriptions.subscribable_type = 'Company' 
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6
)
or
posts.id IN 
(
SELECT posts.id FROM posts 
INNER JOIN users users_2 ON posts.analyst_id = users_2.id 
INNER JOIN subscriptions ON subscriptions.subscribable_id = users_2.id AND subscriptions.subscribable_type = 'User'
INNER JOIN users ON subscriptions.user_id = users.id WHERE users.id = 6
)