SQL: Select * where attribute matches all items in array

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?

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.

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.