Forum: Ruby on Rails Like Query Across Multiple Tables

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Dan H. (Guest)
on 2006-02-16 00:30
(Received via mailing list)
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

--
http://www.kingdomsolutions.com.au
Dan H. (Guest)
on 2006-02-16 03:19
(Received via mailing list)
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

--
http://www.kingdomsolutions.com.au
Dave T. (Guest)
on 2006-02-16 04:01
(Received via mailing list)
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
Jonathan V. (Guest)
on 2006-02-17 10:00
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.
This topic is locked and can not be replied to.