Search code examples
sqlpostgresqlrelational-division

How to perform the equivalent of AND amongst search terms

I've built an app that displays images. Each image can be tagged with multiple tags. I've now added a Search box that works fine in an OR kind of way. What I'm trying to figure out with PostrgreSQL is how to also let AND work with the entered search terms.

The key DB tables are these:

  • images
  • images_tags (bridge table)
  • tags

Here's a simplified version of an OR query working correctly:

SELECT * from media m JOIN media_tags mt JOIN tags t USING (tag_id)
WHERE t.tag = ANY(ARRAY[${searchTerms.map(term => `'${term}'`).join(',')}]);

Here's an example of how this SQL code might be rendered:

SELECT * from media m JOIN media_tags mt JOIN tags t USING (tag_id)
WHERE t.tag = ANY(ARRAY['dog', 'cat']);

So, for example, if the user enters dog cat then every media record tagged with dog or cat will be returned.

But what if I wanted to perform the equivalent of dog AND cat? How can I accomplish this with the Postgres flavor of SQL?

Solution

  • The most syntactically satisfying, but also impossible to get good performance, would be to aggregate the tags, then test the the aggregate:

    SELECT m.* from media m JOIN media_tags mt USING (something) JOIN tags t USING (tag_id)
GROUP by m.something
HAVING array_agg(t.tag) @> ARRAY['dog', 'cat'];

    Less pretty but easier to optimize with indexes would be to use EXISTS subqueries.

    SELECT m.* from media m where
 exists (select 1 from media_tags join tags using (tag_id) where m.id=id and tag='cat') 
 and
 exists (select 1 from media_tags join tags using (tag_id) where m.id=id and tag='dog');