CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Jumbalaya Wanton
Jumbalaya Wanton

Reputation: 1621

How to find posts tagged with more than one tag in Rails and Postgresql

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?

Upvotes: 4

Views: 1439

Answers (2)

cschroed
cschroed

Reputation: 6834

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")

Upvotes: 3

JeanMatheus
JeanMatheus

Reputation: 17

Use method .includes, like this:

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

Documentation to .includes here.

Upvotes: -2

Related Questions