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
