RoR Search based on many to many association such as tags

I have built a RoR database with several different models. Each "record" has many taggings and also is put into a category by a "cat_id." I have built all the models, but I need help with filtering. Right now I have the ability to select a category from a tree view with nodes and it shows all the records in that category and children categories. I can also do a simple text search with a search function (LIKE operator in SQL). I want to add an additional filter of tags to be able to select a category and a tag and show the results.

Here's what I've got so far:

RECORD MODEL

class AuctionRecord < ActiveRecord::Base

  #comments
  has_many :comments, :dependent => :destroy

  #tags
  has_many :auction_records_tags
  has_many :tags, :through => :auction_records_tags 

TAG MODEL

class Tag < ActiveRecord::Base
  attr_accessible :name
  has_many :auction_records_tags
  has_many :auction_records, :through => :auction_records_tags
end

AUCTIONS_RECORDS_TAGS MODEL

class AuctionRecordsTag < ActiveRecord::Base
  attr_accessible :auction_record_id, :tag_id

  belongs_to :tag
  belongs_to :auction_record

end

As you can see the records and tags have a many to many relationship so it is possible to select many tags and be returned with many auction records.

RECORD CONTROLLER

@auction_records = AuctionRecord.filter(session[:search], @cat_sql).order(sort_column + " " + sort_direction).paginate(:per_page => 20, :page => session[:page] )

FILTER FUNCTION

 #this is the main search engine
        def self.filter(search, cat_sql)   

        search_sql = "" 

        if search != ""  
          search_sql = "title LIKE '%#{search}%' OR itemnumber LIKE '%#{search}%' OR buyer LIKE '%#{search}%' OR seller LIKE '%#{search}%'"
          end

        if cat_sql != "" && search == ""
          search_sql = cat_sql
        end    

        if cat_sql != "" && search != ""
          search_sql = search_sql + " AND " + cat_sql
        end

        if search_sql !=""      
          where(search_sql)
        else 
          scoped
        end

      end

I generate the category sql statement manually by for looping through all the nodes and children nodes. As you can see I do almost everything "manually" (not the best way). The fact that certain filters may also not be defined was troublesome (for example a category selection, but no search term). I think all three filters can be accomplished in one line of code, but I'm not sure how it's done. Perhaps something like tags.auction_records? If I need to post the schema of the tables, I can do that too, but for now all the categorization and taggings are done with integer/id relationships.

Thanks in advance for any help / comments

Best regards

Added Info #1

NODE MODEL (used for categorization/categories)

class Node < ActiveRecord::Base
  has_many :nodes
  belongs_to :node
end

SCHEMA

 create_table "auction_records", :force => true do |t|
    t.string   "title"    
    t.string   "categorization"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

 create_table "auction_records_tags", :force => true do |t|
    t.integer  "auction_record_id"
    t.integer  "tag_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "nodes", :force => true do |t|
    t.integer  "node_id"
    t.string   "text"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "tags", :force => true do |t|
    t.string   "name"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

开发者_Python百科

For "auction_records", the field categorization contains the node_id integer to point to which node it belongs to on the tree view. If you click on a single node (category), many auction_records appear. The auction_records_tags also creates the association between auction_records and tags. I have found something that works for only one tag_id, but I will have to work on something for multiple tags.

Right now I have this code and it is returning the error "Association named "nodes" was not found"

       search_term = session[:search]
        term = "%#{search_term}%"
        @auction_records = AuctionRecord.scoped
        @auction_records = @auction_records.where('title LIKE ?', term).where('description LIKE ?',term)  if search_term
        @auction_records = @auction_records.joins(:nodes).where('node.node_id IN ?', session[:cat_id])  if session[:cat_id]
            @auction_records = @auction_records.joins(:tags).where('tags.id = ?', session[:tag_ids])  if session[:tag_ids]
        @auction_records = @auction_records.order(sort_column + " " + sort_direction).paginate(:per_page => 20, :page => session[:page] )

---

Remember that in Rails 3 you can do chained queries that are less error prone than concatenation, also is better to use wildcards to prevent SQL injection. You can refactor the code to look similar to this:

def self.filter(search_term, categories, tags)
  term = "%#{search_term}%"
  auction_records = AuctionRecord.scoped
  auction_records = auction_records.where('title LIKE ?', term).where('other_field LIKE ?',term)  if search_term
  auction_records = auction_records.joins(:categories).where('categories.name IN ?', categories)  if categories
  auction_records = auction_records.joins(:tags).where('tags.name IN ?' tags)  if tags
end