The following query is almost correct, except that it results in duplicate rows from the list_taxonomies table. I only need unique rows from the list_taxonomies table. I've struggled with a few solutions but can't seem to get it. I've tried LEFT OUTER and INNER joins on the items table.
以下查询几乎是正确的，除了它导致list_taxonomies表中的重复行。我只需要list_taxonomies表中的唯一行。我一直在努力解决一些问题，但似乎无法得到它。我已尝试将LEFT OUTER和INNER连接到items表上。
Any help appreciated.
任何帮助赞赏。
Query:
查询：
SELECT
lists.*,
json_agg(items ORDER BY items.id) AS _items,
json_agg(list_taxonomies ORDER BY list_taxonomies.type) AS taxonomy
FROM
lists
JOIN
list_taxonomies ON list_taxonomies.list_id = lists.id
JOIN
items ON items.list_id = lists.id
WHERE
lists.id = 3
GROUP BY
lists.id
Current result:
目前的结果：
{
"status": "success",
"data": [{
"id": 3,
"name": "tincidunt pede ac urna. Ut",
"description": "Lorem ipsum dolor sit amet, consectetuer adipiscing",
"created": "2016-08-24T12:00:00.000Z",
"updated": "2016-08-24T12:00:00.000Z",
"owner": 9,
"likes": 3,
"private": 0,
"location": "United States",
"nsfw": 0,
"_items": [{
"id": 2,
"name": "sem semper",
"description": "sollicitudin commodo",
"list_id": 3,
"type": 2,
"image": "http://fillmurray.com/",
"list_order": 6,
"created": "2016-08-24T05:00:00-07:00",
"link": "http://amazon.com"
}, {
"id": 14,
"name": "magna sed",
"description": "bibendum. Donec felis",
"list_id": 3,
"type": 2,
"image": "http://fillmurray.com/",
"list_order": 1,
"created": "2016-08-24T05:00:00-07:00",
"link": "http://google.com"
}],
"taxonomy": [{
"list_id": 3,
"taxonomy": "Art",
"type": 1
}, {
"list_id": 3,
"taxonomy": "Art",
"type": 1
}, {
"list_id": 3,
"taxonomy": "Art",
"type": 1
}, {
"list_id": 3,
"taxonomy": "Art",
"type": 1
}, {
"list_id": 3,
"taxonomy": "Art",
"type": 1
}, {
"list_id": 3,
"taxonomy": "Art",
"type": 1
}, {
"list_id": 3,
"taxonomy": "Art",
"type": 1
}]
}],
"message": "Retrieved list 1"
}
1 个解决方案
#1
1
When aggregating from more than one table, then aggregate before joining:
从多个表聚合时，然后在加入之前聚合：
SELECT
l.*,
i._items,
lt.taxonomy
FROM lists l
JOIN
(
select list_id, json_agg(list_taxonomies.* order by type) AS taxonomy
from list_taxonomies
group by list_id
) lt ON lt.list_id = l.id
JOIN
(
select list_id, json_agg(items.* order by id) AS _items
from items
group by list_id
) i ON i.list_id = l.id
WHERE l.id = 3;
#1
1
When aggregating from more than one table, then aggregate before joining:
从多个表聚合时，然后在加入之前聚合：
SELECT
l.*,
i._items,
lt.taxonomy
FROM lists l
JOIN
(
select list_id, json_agg(list_taxonomies.* order by type) AS taxonomy
from list_taxonomies
group by list_id
) lt ON lt.list_id = l.id
JOIN
(
select list_id, json_agg(items.* order by id) AS _items
from items
group by list_id
) i ON i.list_id = l.id
WHERE l.id = 3;