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.