# Help doing find - look for nonempty habtm

**URL:** <https://www.ruby-forum.com/t/help-doing-find-look-for-nonempty-habtm/81859>\
**Category:** Rails\
**Created:** [March 14, 2006, 8:54pm UTC](https://www.ruby-forum.com/t/help-doing-find-look-for-nonempty-habtm/81859 "2006-03-14T20:54:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\
**Post date:** [March 14, 2006, 8:54pm UTC](https://www.ruby-forum.com/t/help-doing-find-look-for-nonempty-habtm/81859/1 "2006-03-14T20:54:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\
**Post date:** [March 14, 2006, 9:12pm UTC](https://www.ruby-forum.com/t/help-doing-find-look-for-nonempty-habtm/81859/2 "2006-03-14T21:12:44Z")

</div>

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](http://blog.inlet-media.de)

---

<div class="post-metadata">

**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\
**Post date:** [March 14, 2006, 9:15pm UTC](https://www.ruby-forum.com/t/help-doing-find-look-for-nonempty-habtm/81859/3 "2006-03-14T21:15:45Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![pergesu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/22d042/32.png) [@pergesu](https://www.ruby-forum.com/u/pergesu)\
**Post date:** [March 14, 2006, 9:31pm UTC](https://www.ruby-forum.com/t/help-doing-find-look-for-nonempty-habtm/81859/4 "2006-03-14T21:31:05Z")

</div>

On 3/14/06, Norman T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > 
> 
> Norman T.
> 
> [http://blog.inlet-media.de](http://blog.inlet-media.de)
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](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