Simple search on joined table


#1

Looking for some guidance, I seem to be caught in the weeds.

I want to do a search on fields in a master/detail relationship.

E.g. manufacturer owns many parts. I want to search on the
manufacturer name, part name and part number, then return the full set
of results.

I have tried to use the .find method and create my own model .find
method using a SQL statement, but then the results do not get properly
mapped back to the objects.

What is the accepted strategy for this simple query?

thanks
Don


#2

what i might do is have 2 sets of search results

#simple search
def search
@manufacturers = Manufacturer.find(:all, :conditions => [“LOWER(name)
like
?”, “%#{@params[:criteria].downcase}%”])
@parts = Parts.find(:all, :conditions => [“LOWER(name) like ? or
LOWER(part_no) like ?”, “%#{@params[:criteria].downcase}%”,
“%#{@params[:criteria].downcase}%”])
end

then in your view, display the matches in sections

Manufacturers: found <%= @manufacturers.size -%> matches to “<%=
@params[:criteria] %>”
<%# interate though matched manufacturers %>

Parts: found <%= @parts.size -%> matches to “<%= @params[:criteria] %>”
<%# interate though matched parts %>


#3

On 3/14/06, don cameron removed_email_address@domain.invalid wrote:

Looking for some guidance, I seem to be caught in the weeds.

I want to do a search on fields in a master/detail relationship.

E.g. manufacturer owns many parts. I want to search on the
manufacturer name, part name and part number, then return the full set
of results.

Hi Don,

Have you tried using the :include parameter in find()? For instance,

m = Manufacturer.find( :all, :include => [ :parts ],
:conditions => [ “manufacturers.name
like ? or parts.name like ? or parts.number like ?”, txt, txt, txt ]
)

m would be an array of manufactures and m.parts would be an array of
parts for the manufacturer.

I think this would do what you’re looking for.


#4

Rick T. wrote:

On 3/14/06, don cameron removed_email_address@domain.invalid wrote:

Looking for some guidance, I seem to be caught in the weeds.

I think this would do what you’re looking for.

Thanks Rick, works pretty darn fine.

Hmmm, can I take you for vietnamese lunch on Thursday? Fort St.

Don