通过关联查找匹配和不匹配记录的单个匹配项

Question

我有 3 个 Postgres 表

1. 组
2. groups_favourites
3. 收藏夹

这 3 个表的结构是：

groups
--------------------------
| id |  name   | user_id |
--------------------------
| 1  | Group A |   100   |
| 2  | Group B |   100   |
| 3  | Group C |   100   |
| 4  | Group D |   100   |
--------------------------
favourites_groups
-------------------------------
| id | group_id | favorite_id |
-------------------------------
| 1  |  2       | 10          |
| 2  |  2       | 12          |
| 3  |  2       | 14          |
| 4  |  2       | 15          |
| 5  |  2       | 16          |
| 6  |  3       | 12          |
| 7  |  4       | 10          |
| 8  |  4       | 11          |
| 9  |  4       | 12          |
| 10 |  4       | 13          |
-------------------------------
UNIQUE INDEX ON [group_id, favorite_id]
favourites
-------------------------------
| id | product_id  | user_id  |
-------------------------------
| 10 |    1000     |   100    |
| 11 |    1001     |   100    |
| 12 |    1002     |   100    |
| 13 |    1003     |   100    |
| 14 |    1004     |   100    |
| 15 |    1005     |   100    |
| 16 |    1006     |   100    |
-------------------------------

我在后端使用Ruby on Rails，这 3 个表之间的关系是通过has_many :through关联设置

的我想完成的结果是：对于给定的产品和用户，我想找到表中有条目favorite_groups组 + 表中没有条目favorites_groups所有组。

假设对于user_id = 100和product_id = 1000，结果将是：

| group_id | group_name | favorite_id | product_id |
----------------------------------------------------
|    1     |  Group A   |    NULL     |    NULL    |
|    2     |  Group B   |     10      |    1000    |
|    3     |  Group C   |    NULL     |    NULL    |
|    4     |  Group D   |     10      |    1000    |
----------------------------------------------------

另一个用例是user_id = 100和product_id = 1002，结果将是：

| group_id | group_name | favorite_id | product_id |
----------------------------------------------------
|    1     |  Group A   |    NULL     |    NULL    |
|    2     |  Group B   |     12      |    1002    |
|    3     |  Group C   |     12      |    1002    |
|    4     |  Group D   |     12      |    1002    |
----------------------------------------------------

我正在使用的当前 SQL 为我提供了 1 行NULLfavorite_id和product_id以及 1 行匹配的favorite_id和product_id

这是我的 SQL：

SELECT DISTINCT ON (group_id, favorite_id, product_id) groups.id AS group_id, groups.name AS group_name, favorites.id AS favorite_id, favorites.product_id AS product_id
FROM groups
LEFT JOIN favorite_groups ON favorite_groups.group_id = groups.id
LEFT JOIN favorites ON favorites.id = favorite_groups.favorite_id AND favorites.product_id = 1002
WHERE groups.user_id = 100

由于此查询返回的结果，我必须额外工作来迭代每一行并准备所需的结果集。

有没有更好的方法

？提前感谢您的帮助！

Answer 1

你的模型中有一些奇怪的东西。groups.name与groups和favourites中都显示的user_id之间的关系尚不清楚。对favourites_groups的unique约束应该使user_idfavourites变得不必要，因此我添加了一个注释掉的join条件。

请尝试此查询以查看它是否返回您需要的内容：

select g.id as group_id, g.name as group_name
from groups g
left join favourites_groups fg
on fg.group_id = g.id
left join favourites f
on f.id = fg.favorite_id
--  and f.user_id = g.user_id 
where g.user_id = 100
and f.product_id = 1002
;

更新对此感到抱歉。 这应该返回您想要的内容：

select g.id as group_id, g.name as group_name,
max(f.id) as favorite_id, 
max(f.product_id) as product_id
from groups g
left join favourites_groups fg 
on fg.group_id = g.id
left join favourites f 
on f.id = fg.favorite_id
and f.product_id = 1000
where g.user_id = 100
group by g.id, g.name
order by g.id;