# Help me convert find\_by\_sql to Rails idioms

**URL:** <https://www.ruby-forum.com/t/help-me-convert-find-by-sql-to-rails-idioms/106022>\
**Category:** Rails\
**Created:** [June 5, 2007, 7:21pm UTC](https://www.ruby-forum.com/t/help-me-convert-find-by-sql-to-rails-idioms/106022 "2007-06-05T19:21:22Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![markthomas](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/90db22/32.png) [@markthomas](https://www.ruby-forum.com/u/markthomas)\
**Post date:** [June 5, 2007, 7:21pm UTC](https://www.ruby-forum.com/t/help-me-convert-find-by-sql-to-rails-idioms/106022/1 "2007-06-05T19:21:22Z")

</div>

I managed to cobble together some dependent drop-down selects. First,  
you pick a state, then a city in that state, then a venue in both the  
city and the state. I got it to work using find\_by\_sql, but I’d like  
to use Rails idioms instead.

The drop-downs:

\<%= collection\_select ‘event’, ‘venue\_state’,  
Venue.find\_by\_sql(“SELECT DISTINCT(state) FROM venues ORDER BY  
state”), ‘state’, ‘state’ %\>

\<%= collection\_select ‘event’, ‘venue\_city’,  
Venue.find\_by\_sql([“SELECT DISTINCT(city) FROM venues WHERE state=?  
ORDER BY city”, if (@event.venue) then @event.venue.state end]),  
‘city’, ‘city’ %\>

\<%= collection\_select ‘event’, ‘venue\_id’, Venue.find\_by\_sql([“SELECT  
DISTINCT(name) FROM venues WHERE state=? AND city=? ORDER BY name”,  
@event.venue.state, @event.venue.city]), ‘name’, ‘name’ %\>

And, for completeness, the two observers that provide the cascade:

\<%= observe\_field(‘event\_venue\_state’,  
{:url=\>{:action=\>‘select\_venue\_city’},:update=\>‘event\_venue\_city’,:with=\>‘state’})  
%\>

\<%= observe\_field(‘event\_venue\_city’,  
{:url=\>{:action=\>‘select\_venue\_id’},:update=\>‘event\_venue\_id’,:with=\>‘city’})  
%\>

I was unable to find a rails equivalent to SELECT DISTINCT short of  
using find\_all with map and uniq, which would probably be inefficient.

Another problem I have is: This is a partial on an Event controller  
being shared by “edit” and “new” actions, but a “new” action results  
in errors because the relationship does not yet exist. Is there any  
way for me to remove dependence on the event object? The first two  
don’t really need to be tied to event at all, since the only thing I  
care about is the venue\_id from the third drop-down.

---

<div class="post-metadata">

**Author:** ![markthomas](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/90db22/32.png) [@markthomas](https://www.ruby-forum.com/u/markthomas)\
**Post date:** [June 5, 2007, 9:07pm UTC](https://www.ruby-forum.com/t/help-me-convert-find-by-sql-to-rails-idioms/106022/2 "2007-06-05T21:07:38Z")

</div>

I wrote:

> Venue.find\_by\_sql(“SELECT DISTINCT(state) FROM venues ORDER BY  
> state”)  
> …  
> I was unable to find a rails equivalent to SELECT DISTINCT short of  
> using find\_all with map and uniq, which would probably be inefficient.

D’oh! I figured this one out. It’s simple:

Venue.find(:all, :select=\>“DISTINCT state”, :order=\>“state”)

> Another problem I have is: This is a partial on an Event controller  
> being shared by “edit” and “new” actions, but a “new” action results  
> in errors because the relationship does not yet exist. Is there any  
> way for me to remove dependence on the event object? The first two  
> don’t really need to be tied to event at all, since the only thing I  
> care about is the venue\_id from the final drop-down.

This question I still have. How do you prevent collection\_select from  
checking the method? Will I have to write my own?