Ruby on Rails – find_by_sql

Problem: Need to find data from Table A where a.name contains ‘term’. Also need to search Table B where b.name contains ‘term’ and include the associated row from Table A where A.primary_id = B.other_id in the returned data. Sadly, Ruby on Rails doesn’t have the join feature working.

Solution: find_by_sql

1. Inside the model of the data you are looking for, add a method that accepts the search string.

2. Create a string named query with your SQL, inserting your search string in the appropriate spot.

3. Generate your result set with find_by_sql(query).

4. Enjoy the data!

Sample code:

class Modeldata < ActiveRecord::Base

# Simple searches of this table by name
    def self.search(q)
        where(arel_table[:name].matches(q))
    end

def self.search_with_name(q)
        query =  "select a.* "
        query << "  from table_a a, table_b b "
        query << " where a.id = b.other_id "
        query << " and (a.name like '#{q}' or b.first_name like '#{q}' or b.last_name like '#{q}') " if q
        logger.info(query) # Just to see what we're sending, remove for production.
        results = find_by_sql(query)
        # Do any extra processing, trimming, additions, or debugging data dumps here.
        results
    end
end
``