I have a table posts which has a many-to-many relationship with tags, the pivot table is called posts-tags.
I want to be able to retrieve all posts by a list of tag id's.
Imagine
posts
id | text
--------
1 | "foo"
2 | "bar"
3 | "baz"
posts_tags
post_id | tag_id
-----------------
1 | 1
1 | 2
1 | 3
2 | 1
3 | 1
tags
id | name
--------
1 | "foo"
2 | "bar"
3 | "baz"
With tag id's [1, 2, 3], I should get back [{id: 1, text: "foo"}]
With tag id's [1], I should get back [{id: 1, text: "foo"}, {id: 2, text: "bar"}, {id: 3, text: "baz"}]
Basically, I want to retrieve all the posts related to the list of tags.
CodePudding user response：
You can use a subquery to filter posts that have all the specified tags:
select json_agg(json_build_object('id', p.id, 'text', p.txt))
from posts p where (select count(*) from json_array_elements('[1, 2, 3]') v
join post_tags t on t.post_id = p.id and v.value::text::int = t.tag_id) = json_array_length('[1, 2, 3]')