CODEWITHSUNDEEP
ruby-on-rails-3activerecordtagsconditional-statementsoption-type
Railsana
Railsana

Reputation: 1842

Rails: Count optional tags while having conditional tags in one query

I want to find products matching as many optional tags as possible which are tagged by all conditional tags, ordered by the amount of matching optional tags.

I came up with this for counting the optional tags:

optional_tags = [1,2,3]
conditional_tags = [4]

products = Product.select('COUNT(*) AS count_all, products.*')
                  .joins(:tags).where('tags.id IN (?)', optional_tags)
                  .group('products.id')
                  .order('count_all DESC')

I successfully get the product ids and the number of matching optional tags:

products: {30=>4, 26=>3, 29=>3, 27=>2, 28=>1}

When I try to add the conditional tags I only get an empty hash. I tried it this way:

products = Product.where('tags.id IN (?)', conditional_tags)
                  .select('COUNT(*) AS count_all, products.*')
                  .joins(:tags).where('tags.id IN (?)', optional_tags)
                  .group('products.id')
                  .order('count_all DESC')

Rails optimizes the query and combines all where parts...

How could I add the conditional tags to my construct? Thanks for any help!

Example:

The type of a product is important, but people don't care about the details.

conditional_tags = [1 car]

optional_tags = [2 red, 3 mercedes, 4 fast]

A red car would be ok, but a red fast motorcycle by mercedes should not be in the list of results even though there are more matching tags.

Upvotes: 0

Views: 278

Answers (3)

Railsana
Railsana

Reputation: 1842

Thank you for your help, Jason. I think I figured it out... I perform a sub-query for the conditional tags within the main query for the optional tags.

My Product scopes:

scope :tagged, lambda { |tag_ids|
  joins(:tags)
  .where('tags.id IN (?)', tag_ids)
  .group('products.id')
}
scope :matched, lambda { |product_ids, tag_ids|
  select('COUNT(*) AS count_all, products.*')
  .joins(:tags)
  .where('products.id IN (?) AND tags.id IN (?)', product_ids, tag_ids)
  .group('products.id')
  .order('count_all DESC')
}

My query construct:

result = Product.matched(Product.tagged(branch_ids).map(&:id), tag_ids)

I am a little concerned about the performance, because Rails has to perform two separate queries. The nice thing is, that I can add a .limit() to the "main" query.

If someone has an idea how to solve this within one query, please let me know! Thanks!

Upvotes: 0

Jason Swett
Jason Swett

Reputation: 45094

Okay, I think I finally figured it out:

class Product < ActiveRecord::Base
  scope :having_tags, -> { |tags|
    joins(:tags).where('tags.id IN (?)', tags)
  }

  scope :ordered_by_tag_count, -> { |tags|
    joins(:tags)
    .where('tags.id IN (?)', tags)
    .group('products.id')
    .order('count_all DESC')
  }
end

So if you have these tags:

conditional_tags = [1 car]

optional_tags = [2 red, 3 mercedes, 4 fast]

You might do this:

Product.having_tags([1]).ordered_by_tag_count([2, 3, 4])

Upvotes: 1

Jason Swett
Jason Swett

Reputation: 45094

I don't know if you're aware, but I think your second query is equivalent to this:

products = Product.select('COUNT(*) AS count_all, products.*')
                  .joins(:tags)
                  .where('tags.id IN (?) AND tags.id IN (?)', optional_tags, conditional_tags)
                  .group('products.id')
                  .order('count_all DESC')

Maybe you're after something closer to this?

products = Product.select('COUNT(*) AS count_all, products.*')
                  .joins(:tags)
                  .where('tags.id IN (?)', optional_tags | conditional_tags)
                  .group('products.id')
                  .order('count_all DESC')

My guess is probably not right, since I don't completely understand what you're trying to do. Maybe that gets you a little further down the road, though.

Upvotes: 1

Related Questions