Join Queries? - find() or find_by_sql()


#1

Hi All,

Is there a way to do join queries with find() or is it best to use
find_by_sql() instead?

I’m looking to do something like:

SELECT winery_name, wine_name FROM winery, wine
WHERE wine.winery_id = winery.winery_id
AND winery.winery_name LIKE ‘Borg%’;

Thanks!

: )

Jason


#2

Jason,

find() takes a :joins parameter to let you do… well, joins. Its
documented in the API docs. It might be a little easier to work with
than find_by_sql().

Jeff


#3

Actually, I’d say the answer to Jason’s question is, neither. The
“Rails Way” of doing this kind of search is through the :include
option to the standard find method. Based on Jasons brief explanation
it looks like he has two models: Winery and Wine, where Winery
has_many: wines and Wine belongs_to: winery.

Given that, his find is to get all Wineries, and their associated
Wines, based on a name sub-string:

assuming params[:query] == ‘Borg’

@wineries = Winery.find( :all, :include => :wines,
:conditions =>
[‘wineries.winery_name LIKE ?’, “#{params[:query]}%”] )

That will give you an array of Winery objects, each of which will
have a wines method, which will be an array containing all of the
wines for that Winery…

-Brian