Like Query Across Multiple Tables


#1

Firstly, I’m still learning Ruby and rails, so there could be a very
simple solution to this.

I want to search across multiple tables with a “like” query eg: (SQL is
probably the easiest way to describe it):

select
products.*
from products
inner join brands on products.brand_id = brands.id
where
products.name like ‘%canon%’
or brands.name like ‘%canon%’

Currently, I have a solution for only the products table. In the
product.rb I have:
def self.search(search_string)
find(:all,
:conditions => [“lower(products.name) like ?”,
“%#{search_string.downcase}%”])
end

Which works fine, is there an easy way to add the search to include
brands?

Thanks,
Dan



#2

Dan H. wrote:

Currently, I have a solution for only the products table. In the
product.rb I have:
def self.search(search_string)
find(:all,
:conditions => [“lower(products.name) like ?”,
“%#{search_string.downcase}%”])
end

Which works fine, is there an easy way to add the search to include
brands?
No ideas on this one guys?

I’ve been scouring through the Agile Rails book, but I can’t seem to
find any reference that fits my need. Any ideas?

Thanks,
Dan



#3

With :include => you can use like on the other table as well:

def self.search(keyword)
find(:all, :include => :brand, :conditions => [‘products.name like ?
or brands.name like ?’, “%#{keyword}%”, “%#{keyword}%”]
end

The result will be an array of Product objects. If you want brand
objects, just implement a similar search on the Brand class.

-Jonny.


#4

You can use any custom SQL you’d like with AR’s find_by_sql:

Product.find_by_sql(“select * from products where 1=1”)

The fact that you use a LIKE clause should have no effect on this.

HTH.

On Thursday, February 16, 2006, at 12:16 PM, Dan H. wrote:

brands?


Rails mailing list
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

Cheers!
–Dave T.
http://devlists.com - Email list management
http://palmsphere.com - Apps for your hand-held