4

I have the models Post, Tag, and PostTag. A post has many tags through post tags. I want to find posts that are exclusively tagged with more than one tag.

has_many :post_tags
has_many :tags, through: :post_tags

For example, given this data set:

posts table
--------------------
id | title         |
--------------------
1  | Carb overload |
2  | Heart burn    |
3  | Nice n Light  |

tags table
-------------
id | name   |
-------------
1  | tomato |
2  | potato |
3  | basil  |
4  | rice   |

post_tags table
-----------------------
id | post_id | tag_id |
-----------------------
1  | 1       | 1      |
2  | 1       | 2      |
3  | 2       | 1      |
4  | 2       | 3      |
5  | 3       | 1      |

I want to find posts tagged with tomato AND basil. This should return only the "Heart burn" post (id 2). Likewise, if I query for posts tagged with tomato AND potato, it should return the "Carb overload" post (id 1).

I tried the following:

Post.joins(:tags).where(tags: { name: ['basil', 'tomato'] })

SQL

SELECT "posts".* FROM "posts"
INNER JOIN "post_tags" ON "post_tags"."post_id" = "posts"."id"
INNER JOIN "tags" ON "tags"."id" = "post_tags"."tag_id"
WHERE "tags"."name" IN ('basil', 'tomato')

This returns all three posts because all share the tag tomato. I also tried this:

Post.joins(:tags).where(tags: { name 'basil' }).where(tags: { name 'tomato' })

SQL

SELECT "posts".* FROM "posts"
INNER JOIN "post_tags" ON "post_tags"."post_id" = "posts"."id"
INNER JOIN "tags" ON "tags"."id" = "post_tags"."tag_id"
WHERE "tags"."name" = 'basil' AND "tags"."name" = 'tomato'

This returns no records.

How can I query for posts tagged with multiple tags?

Jumbalaya Wanton
  • 1,601
  • 1
  • 25
  • 47

2 Answers2

3

You may want to review the possible ways to write this kind of query in this answer for applying conditions to multiple rows in a join. Here is one possible option for implementing your query in Rails using 1B, the sub-query approach...

Define a query in the PostTag model that will grab up the Post ID values for a given Tag name:

# PostTag.rb
def self.post_ids_for_tag(tag_name)
  joins(:tag).where(tags: { name: tag_name }).select(:post_id)
end

Define a query in the Post model that will grab up the Post records for a given Tag name, using a sub-query structure:

# Post.rb
def self.for_tag(tag_name)
  where("id IN (#{PostTag.post_ids_for_tag(tag_name).to_sql})")
end

Then you can use a query like this:

Post.for_tag("basil").for_tag("tomato")
Community
  • 1
  • 1
cschroed
  • 6,304
  • 6
  • 42
  • 56
  • Thanks. That works. But I am a bit histant with the sub-query approach. I found that [joining the table twice under an alias works](https://gist.github.com/anonymous/7811fa8e68432495e77a). Although how to get that into active record syntax is another story. – Jumbalaya Wanton Sep 02 '14 at 13:08
  • The only way I've seen a join alias implemented in Rails is with straight SQL inside the joins command. Something like `PostTag.joins("INNER JOIN tags AS t1 ON t1.tag_id = post_tags.tag_id").where("t1.name = ?", "basil")` – cschroed Sep 02 '14 at 13:16
-2

Use method .includes, like this:

Item.where(xpto: "test")
.includes({:orders =>[:suppliers, :agents]}, :manufacturers)

Documentation to .includes here.

JeanMatheus
  • 17
  • 3