CODEWITHSUNDEEP
mysqlsql
gosukiwi
gosukiwi

Reputation: 1575

Searching by tag, inclusive

I have a many to many relationship between stories and tags, the table created to hold the relationship is taxonomies. I want to search all the stories that have all the given tags.

The query I'm using so far is this one, of course it doesn't work for me, it returns all stories that contain any of the given tags, I want to show stories that contain all the defined tags.

 SELECT stories.* FROM `stories` 
 JOIN `taxonomies` ON `stories`.id = `taxonomies`.`story_id`
 JOIN `tags` ON `tags`.id = `taxonomies`.tag_id
 WHERE `tags`.name = 'tag 1' OR `tags`.name = 'tag 2' /* ... */
 GROUP BY stories.id

Upvotes: 2

Views: 71

Answers (3)

Mihai
Mihai

Reputation: 26784

I think you want this query

SELECT stories.id FROM `stories` 
 JOIN `taxonomies` ON `stories`.id = `taxonomies`.`story_id`
 JOIN `tags` ON `tags`.id = `taxonomies`.tag_id
 GROUP BY stories.id
 HAVING SUM(`tags`.name = 'tag 1')>0
 AND SUM(`tags`.name = 'tag 2')>0

Upvotes: 4

Abe Miessler
Abe Miessler

Reputation: 85056

Do you know the number of tags you are checking? If so, could you use a HAVING to confirm that the number of matches for stories.id matches the total number of tags? Something like this:

SELECT stories.id, COUNT(stories.id)
FROM `stories` 
 JOIN `taxonomies` ON `stories`.id = `taxonomies`.`story_id`
 JOIN `tags` ON `tags`.id = `taxonomies`.tag_id
 WHERE `tags`.name = 'tag 1' OR `tags`.name = 'tag 2' /* ... */
 GROUP BY stories.id
HAVING COUNT(stories.id) = @numberOfTags

Upvotes: 1

Marcin Zablocki
Marcin Zablocki

Reputation: 10683

Simplest way to do this will be:

 SELECT stories.* FROM `stories` 
 JOIN `taxonomies` ON `stories`.id = `taxonomies`.`story_id`
 JOIN `tags` ON `tags`.id = `taxonomies`.tag_id
 WHERE `tags`.name = 'tag 1'
 GROUP BY stories.id
intersect
 SELECT stories.* FROM `stories` 
 JOIN `taxonomies` ON `stories`.id = `taxonomies`.`story_id`
 JOIN `tags` ON `tags`.id = `taxonomies`.tag_id
 WHERE `tags`.name = 'tag 2'
 GROUP BY stories.id

Upvotes: -1

Related Questions