How to get articles that are missing one of the tags in Posgtres?

user_912225 0
Viewed 54

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.

select join postgresql sql
4 Answers

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)
user_138083190

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
user_106767160

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

DEMO

This will handle in case of duplicate entries of combinations also

user_77724830

One method is simply not exists:

select a.*
from articles a
where not exists (select 1 from article_tags art where art.article_id = a.id and art.tag = 3) or
      not exists (select 1 from article_tags art where art.article_id = a.id and art.tag = 4) ;

You could also use set operations:

select a.id
from articles a
except
(select art.article_id
 from article_tags art
 where art.tag = 3
 intersect
 select art.article_id
 from article_tags art
 where art.tag = 4
);
user_11440350
Related