Help doing find - look for nonempty habtm


#1

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


#2

Am Dienstag, den 14.03.2006, 12:52 -0700 schrieb Pat M.:

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 T.

http://blog.inlet-media.de


#3

Pat M. wrote:

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. :wink:

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


#4

On 3/14/06, Norman T. removed_email_address@domain.invalid wrote:

Norman T.

http://blog.inlet-media.de


Rails mailing list
removed_email_address@domain.invalid
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