Forum: Ruby on Rails Help me convert find_by_sql to Rails idioms

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.
Bd2d12f85f54f93d3c9e2f558a593024?d=identicon&s=25 Mark Thomas (markthomas)
on 2007-06-05 19:21
(Received via mailing list)
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.
Bd2d12f85f54f93d3c9e2f558a593024?d=identicon&s=25 Mark Thomas (markthomas)
on 2007-06-05 21:07
(Received via mailing list)
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?
This topic is locked and can not be replied to.