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?
Robert
CodePudding user response:
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');