2

I have this model:

class Offer < ActiveRecord::Base
   ...
   has_and_belongs_to_many :tags
   ...
end

and this:

class Tag < ActiveRecord::Base
  ...
  has_and_belongs_to_many  :offers
  validates :name, presence: true
end

I want to find all offers that have relations with tag with a name on array

For example:

Offers1 -> Tag1, Tag2
Offers2 -> Tag2, Tag3
Offers3 -> Tag2, Tag3, Tag4

Tag1.name="test1"
Tag2.name="test2"
Tag3.name="test3"
Tag4.name="test4"

If i have this array ["test2","test3"] i want to find Offers2 and Offers3.

If i have this array ["test2","test3","test4"] i want to find Offers3.

I hope I have explained well, thanks for the help.

Aleksei Matiushkin
  • 119,336
  • 10
  • 100
  • 160
Giacomo Carrozzo
  • 27
  • 1
  • 6
  • Please show us what have you tried. – Aleksei Matiushkin Mar 21 '16 at 09:41
  • I tried this solution but returns all the offers that have at least one of those relations: `@offers = Offer.joins(:tags).where(tags: { name: ["test2","test3","test4"]})`. For example: If i have this array ["test2","test3","test4"] find all offers. – Giacomo Carrozzo Mar 21 '16 at 10:14
  • Basically you would use `joins` with a `where` condition, the crux is that you to use an `tags.name = "test2" AND tags.name = "test3"...` clause not IN `( "test2", "test3", "test4" )` – max Mar 21 '16 at 10:14
  • I missed the point that you wanted records that had all the tags. Thats why i removed my answer. – max Mar 21 '16 at 10:15

2 Answers2

1

This is the best possible I could think of but its still not very efficient.

class Offer < ActiveRecord::Base
  # ...
  def has_tags?(other_tags)
    tag_names = tags.loaded? ? tags.map(&:name) : tags.pluck(:name)
    (other_tags - tag_names).empty?
  end
end

This diffs the array to the left with the the array to the right. The result is not empty if the offer does not have all the tags.

names = ["test2","test3","test4"]

# take the records with any of the tag names
offers = Offer.joins(:tags).where(tag: { name: names })

# filter the offers in Ruby
offers.select { |o| o.has_tags?(names) }
max
  • 96,212
  • 14
  • 104
  • 165
  • Note that this will not work with `eager_load` since it uses a `LEFT OUTER` join instead. – max Mar 21 '16 at 10:43
  • In this way I have this query: `SELECT "offers".* FROM "offers" INNER JOIN "offers_tags" ON "offers_tags"."offer_id" = "offers"."id" INNER JOIN "tags" ON "tags"."id" = "offers_tags"."tag_id" WHERE (tags.name = 'test1' AND tags.name = 'test2') ` . What if I have more than one tag returns a null array – Giacomo Carrozzo Mar 21 '16 at 10:53
  • Hmm, you're right. I'm a bit unsure as to how to construct the the condition so that it matches if it is a bigger set but still contains the desired names. – max Mar 21 '16 at 11:02
  • Edited, its the best I can think of. Still not really happy with it since you need pull a bunch of data to sort it in Ruby. Unfortunatly I'm not good enough at SQL to figure out a query to pull the right set. If this remains unanswered I'll put a bounty on it since I am quite interested in how that could be done. – max Mar 21 '16 at 11:42
  • Thanks! When you use params "names" on has_tags function? – Giacomo Carrozzo Mar 21 '16 at 13:09
  • I don't understand what you are asking. – max Mar 21 '16 at 13:10
  • I tried to implement your solution but it does not work, I think that the error is in the "has_tags" function. "other tags" is not initialized, am I wrong? I'm sorry for too many requests – Giacomo Carrozzo Mar 21 '16 at 13:15
  • My bad, I left the argument out from the `has_tags?` method. – max Mar 21 '16 at 13:17
  • I managed to get a far better answer - http://stackoverflow.com/questions/36131803/sql-where-joined-set-must-contain-all-values-but-may-contain-more. The example in there is pretty much identical so it should be easy to adapt. – max Mar 21 '16 at 18:53
  • Thanks! This is a function: `def self.includes_tags(*tag_names) joins(:tags) .where(tags: { name: tag_names }) .group('offers.id') .having('count(*) = ?', tag_names.size) end` Query: `SELECT "offers".* FROM "offers" INNER JOIN "offers_tags" ON "offers_tags"."offer_id" = "offers"."id" INNER JOIN "tags" ON "tags"."id" = "offers_tags"."tag_id" WHERE "tags"."name" IN ('test1', 'test2') GROUP BY offers.id HAVING count(*) = 1` It does not work properly – Giacomo Carrozzo Mar 22 '16 at 14:15
0

Try the following:

class Offer < ActiveRecord::Base

  has_and_belongs_to_many :tags
  scope :with_tags, lambda do |tags = []|
    tags_sql = tags.collect { |tag| "tags.name = '#{tag}'" }.join(" AND ")
    joins(:tags).where(tags_sql)
  end
end

Then you could call:

@offers = Offer.with_tags(["tag1", "tag2"])
Dharam Gollapudi
  • 6,328
  • 2
  • 34
  • 42