Topic: Search by value taken from another table

My Problem is I want to search Subreqs by scope id field which is stored in the subreqs table as an ID like 1,2,3 etc. but the user should enter real values like 5037 etc. which are stored in scopes table. Right now it's searching by id whereas I want to search by scope which comes from scopes table.

Scopes table has --> id,scope,scope_desc

def search_by_scope_id
      @results = Subreq.find(:all, :conditions=> ["Scope_id LIKE ?", "%#{params[:search]}%" ] )
      @total = Subreq.count(:conditions=> ["Scope_id like ?", "%#{params[:search]}%" ] )
      render :layout=>false
  end

Last edited by deepti.jerath (2007-03-15 13:01:59)

Re: Search by value taken from another table

So there's some column in the scopes table you want to search by instead of the id? You can do this:

Subreq.find(:all, :include => :scope, :conditions=> ["scopes.foo LIKE ?", "%#{params[:search]}%" ] )

Replace "foo" with the column in the scopes table you want to search by.

Railscasts - Free Ruby on Rails Screencasts

Re: Search by value taken from another table

I guess your code will work Ryan but doesn't work in my case as there's a search method called before that which handles everything. So you need to tell me now how to modify this code:

def search
    if !params[:req_id].nil?
       conditions = Subreq.conditions(*params.values_at(:req_id, :title, :br_tr_no, :scope_id))
    order = params[:order] || "id DESC"
       @title = "Search Results"
     else
       conditions = nil
    order = params[:order] || "id DESC"
       @title = "All Subreqs returned"
     end
     @subreq_pages, @subreqs = paginate(:subreqs, :conditions => conditions, :order => order, :per_page => 10)
     @total = @subreq_pages.item_count
     render :layout => false if request.xml_http_request?
  end

Re: Search by value taken from another table

What is the "Subreq.conditions" method returning? A string? An array?

Railscasts - Free Ruby on Rails Screencasts

Re: Search by value taken from another table

I think it returns an array as all the search criteria or the search fields entered by the user are taken into consideration.

Re: Search by value taken from another table

Can you post the conditions method?

Railscasts - Free Ruby on Rails Screencasts