仅当存在关系数据时才联接

Question

这些是我的表。

CREATE TABLE IF NOT EXISTS categories (
category_id serial primary key,
category text not null,
user_id int not null
);
CREATE TABLE IF NOT EXISTS activities (
activity_id serial primary key,
activity text not null,
user_id int not null
);
CREATE TABLE IF NOT EXISTS categories_to_activities (
category_id int not null REFERENCES categories (category_id) ON UPDATE CASCADE ON DELETE CASCADE,
activity_id int not null REFERENCES activities (activity_id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT category_activity_pkey PRIMARY KEY (category_id, activity_id)
);

这是我用来获取所有活动及其类别的查询。

SELECT a.*, ARRAY_AGG ( ROW_TO_JSON (c) ) categories
FROM activities a
JOIN categories_to_activities ca ON a.activity_id = ca.activity_id
JOIN categories c ON ca.category_id = c.category_id
WHERE a.user_id = ${userId}
GROUP BY a.activity_id

问题是，如果一个活动没有分配类别，它就不会被返回。

我在组合JOIN和CASE时遇到问题，我想这正是我所需要的。本质上，只有当categories_to_activities中有一些记录时，我才想JOIN？

我该怎么做？

Answer 1

您可以使用left join，并为没有类别的活动返回nulls：

SELECT    a.*, 
CASE WHEN ca.activity_id IS NOT NULL THEN ARRAY_AGG(ROW_TO_JSON(c)) 
ELSE ARRAY[]::JSON[] 
END as categories
FROM      activities a
LEFT JOIN categories_to_activities ca ON a.activity_id = ca.activity_id
LEFT JOIN categories c ON ca.category_id = c.category_id
WHERE     a.user_id = ${userId}
GROUP BY  a.activity_id

Answer 2

多亏了@Murenik ，我最终得到了什么

SELECT    a.*,
CASE WHEN ca.activity_id IS NOT NULL
THEN ARRAY_AGG ( ROW_TO_JSON (c) )
ELSE ARRAY[]::JSON[]
END as categories
FROM      activities a
LEFT JOIN categories_to_activities ca ON a.activity_id = ca.activity_id
LEFT JOIN categories c ON ca.category_id = c.category_id
WHERE     a.user_id = ${userId}
GROUP BY  a.activity_id, ca.activity_id