I''ve got a Cuisine and a Restaurant model, with a habtm. One thing I want to do is find all the cuisines that have at least one restaurant associated with them. Right now I''m using the following SQL query: SELECT DISTINCT(cuisines.*) FROM cuisines, restaurants, cuisines_restaurants WHERE cuisines.id cuisines_restaurants.cuisine_id AND restaurants.id cuisines_restaurants.restaurant_id AND restaurants.approved_at IS NOT NULL That''s working fine. I''d really like to make this a pure AR thing though so I can use the rest of the AR options. Any tips on how to make it happen? I''m using Rails 1.0, but have no problem going to Edge if that''ll make it easier. Pat
Am Dienstag, den 14.03.2006, 12:52 -0700 schrieb Pat Maddox:> I''ve got a Cuisine and a Restaurant model, with a habtm. One thing I > want to do is find all the cuisines that have at least one restaurant > associated with them. Right now I''m using the following SQL query: > > SELECT DISTINCT(cuisines.*) FROM cuisines, restaurants, > cuisines_restaurants WHERE cuisines.id > cuisines_restaurants.cuisine_id AND restaurants.id > cuisines_restaurants.restaurant_id AND restaurants.approved_at IS NOT > NULL > > That''s working fine. I''d really like to make this a pure AR thing > though so I can use the rest of the AR options. Any tips on how to > make it happen? I''m using Rails 1.0, but have no problem going to > Edge if that''ll make it easier.Cuisine.find(:all, :include => :restaurants, :conditions => ''restaurants.approved_at IS NOT NULL'') Works on from Rails 1.0. -- Norman Timmler http://blog.inlet-media.de
Pat Maddox wrote:> I''ve got a Cuisine and a Restaurant model, with a habtm. One thing I > want to do is find all the cuisines that have at least one restaurant > associated with them. Right now I''m using the following SQL query: > > SELECT DISTINCT(cuisines.*) FROM cuisines, restaurants, > cuisines_restaurants WHERE cuisines.id > cuisines_restaurants.cuisine_id AND restaurants.id > cuisines_restaurants.restaurant_id AND restaurants.approved_at IS NOT > NULL > > That''s working fine. I''d really like to make this a pure AR thing > though so I can use the rest of the AR options. Any tips on how to > make it happen?There are actually 2 ways that I can think of doing this, off the top of my head. I''m sure there are more. ;) The main difference between them is whether or not you plan to loop over the @cuisines array and display/work with any restaurant data. If the answer is yes, then it''s easy: @cuisines = Cuisine.find( :all, :include => :restaurants, :conditions => "restaurants.approved_at IS NOT NULL" ) The trick here is you''ll also get all of the Restaurant columns returned, which might be way more data that you actually want/need. So, the second method would be to simply take your find_by_sql and break it up into a more standard find. I think this will do what you want: @cuisines = Cuisine.find( :all, :select => "DISTINCT(cuisines.*) FROM cuisines, restaurants, " + "cuisines_restaurants", :conditions => "cuisines.id = cuisines_restaurants.cuisine_id" + " AND restaurants.id = cuisines_restaurants.cuisine_id" + " AND restaurants.approved_at IS NOT NULL" ) I''m not sure that''s any less cumbersome than what you''re already doing. If you had any explicit joins or a order by, that would make it look/read a lot cleaner. I''m also not positive that you need the DISTINCT() around the cuisines.*, but I haven''t run any tests... -Brian
On 3/14/06, Norman Timmler <lists@inlet-media.de> wrote:> Am Dienstag, den 14.03.2006, 12:52 -0700 schrieb Pat Maddox: > > I''ve got a Cuisine and a Restaurant model, with a habtm. One thing I > > want to do is find all the cuisines that have at least one restaurant > > associated with them. Right now I''m using the following SQL query: > > > > SELECT DISTINCT(cuisines.*) FROM cuisines, restaurants, > > cuisines_restaurants WHERE cuisines.id > > cuisines_restaurants.cuisine_id AND restaurants.id > > cuisines_restaurants.restaurant_id AND restaurants.approved_at IS NOT > > NULL > > > > That''s working fine. I''d really like to make this a pure AR thing > > though so I can use the rest of the AR options. Any tips on how to > > make it happen? I''m using Rails 1.0, but have no problem going to > > Edge if that''ll make it easier. > > Cuisine.find(:all, :include => :restaurants, :conditions => > ''restaurants.approved_at IS NOT NULL'') > > Works on from Rails 1.0. > -- > Norman Timmler > > http://blog.inlet-media.de > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >This seems to work, but is there any way I can do it without getting all the data for the included restaurants? I won''t be using them when I call this, so I''d rather not pull up all that data needlessly, particularly since this method will be getting called a lot. Pat