I have a requirement to allow filtering articles by absence of tags.
E.g. I have:
articles.id
A
B
tags.id
1
2
3
4
articles_tags.article_id articles_tags.tag_id
A 1
A 2
B 2
B 4
Now, I have a list of tag ids, e.g. (3, 4). I would like a query that returns list of articles that are missing any tags from the list.
In this example, it would return both A and B because both don't have tag 3.
If I send (1) it should return only B because A has tag 1.
I would use the PostgreSQL array type to handle this.
Ignoring the articles and tags tables to simplify:
with arraystyle as (
select article_id, array_agg(tag_id) as tagarray
from articles_tags
group by article_id
)
select * from arraystyle;
article_id | tagarray
------------+-----------
B | {2,4}
A | {1,2}
(2 rows)
Having the tagarray in this format allows you to use the Array Functions and Operators. One of the containment operators, @> and <@, is what you need in its negated form.
with arraystyle as (
select article_id, array_agg(tag_id) as tagarray
from articles_tags
group by article_id
)
select * from arraystyle
where not tagarray @> '{1,3}';
article_id | tagarray
------------+----------
B | {2,4}
A | {1,2}
(2 rows)
with arraystyle as (
select article_id, array_agg(tag_id) as tagarray
from articles_tags
group by article_id
)
select * from arraystyle
where not tagarray @> '{1}';
article_id | tagarray
------------+----------
B | {2,4}
(1 row)
You can use not exists and an aggregate query:
select a.*
from articles a
where (
select count(*)
from article_tags at
where at.article_id = a.id and at.tag_id in (3, 4)
) < 2
This assumes no duplicates on article_tags(article_id, tag_id) (as shown in your sample data).
You can also express this with outer aggregation and filtering in a having clause:
select a.*
from articles a
inner join article_tags at on at.article_id = a.id
group by a.id
having count(*) filter(where at.tag_id in (3, 4)) < 2
Try this:
select
distinct article_id
from (
select
t1.id as "article_id",
t2.id as "articles_tags"
from articles t1 cross join tags t2 where t2.id in (3,4)
except
( select
article_id,
tag_id from articles_tags where tag_id in (3,4) ) ) tab
This will handle in case of duplicate entries of combinations also