Forum: Ruby on Rails Help doing find - look for nonempty habtm

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-03-14 20:54
(Received via mailing list)
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
Bf66e10c8fc4abefebde0425e7f6f15a?d=identicon&s=25 Norman Timmler (Guest)
on 2006-03-14 21:12
(Received via mailing list)
Am Dienstag, den 14.03.2006, 12:52 -0700 schrieb Pat Maddox:
> 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
7c4087d053eb02d099a17d91ba5e33b5?d=identicon&s=25 Brian V. Hughes (Guest)
on 2006-03-14 21:15
(Received via mailing list)
Pat Maddox 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. ;)

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
42172acdf3c6046f84d644cb0b94642c?d=identicon&s=25 Pat Maddox (pergesu)
on 2006-03-14 21:31
(Received via mailing list)
On 3/14/06, Norman Timmler <lists@inlet-media.de> wrote:
> >
> 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
This topic is locked and can not be replied to.