SQL: Select * where attribute matches all items in array

Question

Working in a Rails App, I have the following table structure (pertinent columns only)

Photos (id: integer) Taggings (photo_id: integer, tag_id: integer) Tags (id: integer, name:string)

I have the following SQL query:

SELECT distinct photos.* 
FROM \"photos\" INNER JOIN \"taggings\" ON \"photos\".\"id\" = \"taggings\".\"photo_id\" 
                INNER JOIN \"tags\"     ON \"tags\".\"id\" = \"taggings\".\"tag_id\" 
WHERE \"tags\".\"name\" IN ('foo', 'bar')

When I generate this query I'm passing in an array of tags (in this case ["foo","bar"]). The query correctly searches for photos that match ANY of the tags passed in the array.

How can I change this query to select records with ALL of the given tags (ie a photo only matches if tagged with "foo" AND "bar", instead of selecting records with ANY of the given tags?

Paul Creasey · Accepted Answer · 2011-04-29 22:24:32Z

2

There may be a better way, but this should do it

SELECT photos.id,max(otherColumn)
FROM \"photos\" 
INNER JOIN \"taggings\"
ON \"photos\".\"id\" = \"taggings\".\"photo_id\" 
INNER JOIN \"tags\" 
ON \"tags\".\"id\" = \"taggings\".\"tag_id\" 
WHERE \"tags\".\"name\" IN ('foo', 'bar')
group by photos.id
having count(*) = 2 --2 is the number of items in your array of tags.

answered Apr 29, 2011 at 22:24

Paul Creasey

28.9k10 gold badges60 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Over a year ago

This works, but in PostgreSQL I continually run into the classic "PG Error: column ______ must be included in Group By clause or used in aggregate function." I can avoid that by grouping by all the columns, but do you know of a better solution?

Shaunak · Accepted Answer · 2011-04-29 22:27:36Z

0

If you are in rails you don't need query to do this.

Tags.find(1).taggings should give you an array of all photos with that tag

you can also use Tags.find_by_name("foo").taggings

you can similarly iterate over all tags, and collect the arrays and then just do something like on the arrays you have got.

[ 1, 1, 3, 5 ] & [ 1, 2, 3 ]   #=> [ 1, 3 ]

Basically 'and' the arrays and get the unique photos.This way you can get the photos that match all the tags.

answered Apr 29, 2011 at 22:27

Shaunak

18.2k5 gold badges59 silver badges86 bronze badges

2 Comments

Paul Creasey Over a year ago

Not a good idea since it requires multiple database calls and potentially returning a lot of data into memory. Of course it may be fine for a small data set and low number of users.

Andrew Over a year ago

No, this won't work. I'm using this to create a search method on Photos that I can chain with other methods. It has to originate with photos so it can be scoped etc.

Collectives™ on Stack Overflow

SQL: Select * where attribute matches all items in array

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related