CODEWITHSUNDEEP
sqlruby-on-rails
Cesar
Cesar

Reputation: 2057

Finding an entry which contains all elements of an array

my current database structure is that I have places that consist of many tags. I want to be able to search for a place that contains all the tags that a user enters into a searchbox. My current code looks like this, and it will only find places which have atleast one of the tags specified:

tags = params[:tags]

Place.find(:all, :include => [:tags], :conditions => ['tags.title IN (?)', tags])

Upvotes: 0

Views: 223

Answers (2)

ardavis
ardavis

Reputation: 9895

IF you're using tags and Ruby on Rails, I strongly recommend acts_as_taggable_on.

This will let you do things like this:

class User < ActiveRecord::Base
  # Alias for <tt>acts_as_taggable_on :tags</tt>:
  acts_as_taggable
  acts_as_taggable_on :skills, :interests
end

@user = User.new(:name => "Bobby")
@user.tag_list = "awesome, slick, hefty"      # this should be familiar
@user.skill_list = "joking, clowning, boxing" # but you can do it for any context!
@user.skill_list                              # => ["joking","clowning","boxing"] as TagList
@user.save

@user.tags # => [<Tag name:"awesome">,<Tag name:"slick">,<Tag name:"hefty">]
@user.skills # => [<Tag name:"joking">,<Tag name:"clowning">,<Tag name:"boxing">]

@frankie = User.create(:name => "Frankie", :skill_list => "joking, flying, eating")
User.skill_counts # => [<Tag name="joking" count=2>,<Tag name="clowning" count=1>...]
@frankie.skill_counts

Very easy to implement. And you won't have to worry as much about how to find specific elements in an array.

Upvotes: 0

MrAlek
MrAlek

Reputation: 1710

Try this:

tags = params[:tags]

Place.find(:all, :include => [:tags], :conditions => ['places.id IN
  (SELECT place_id FROM 
    (SELECT place_id, count(place_id) AS tags_count FROM places_tags WHERE tag_id IN
      (SELECT id FROM tags WHERE title IN (?))
    GROUP BY place_id) AS count_table
   WHERE tags_count = ?)', tags, tags.size])

Explanation: First find all tags in array, then count how many times each place is found (which equals how many of the tags are matched), then pick out only the places which have all the tags matched.

Upvotes: 1

Related Questions