Can this be refactored into a single database call?

I have a listing of events in an Events model. What I need to do is visually group events by city/state.

Visually, that will look like this:

Here's, a stripped down look of what I'm doing in my view to make this happen:

<% @number.events.locations.each do |location| %>
  <li>
    <h4><%= "#{location.city}, #{location.state}" %></开发者_如何学编程h4>
    <ol>
    <% @number.events.city_state(location).each do |event| %>
      <li><%= event.status %></li>
    <% end %>
    </ol>
  </li>
<% end %>

And the corresponding scopes:

scope :locations, :group => 'city, state'
scope :city_state, lambda {|location| {:conditions => ["city = ? AND state = ?", location.city, location.state] }}

The problem, though, is that I'm doing an additional database call for every location.

So can I get that down to a single query?

Use eager loading to pre-fetch associations from your database:

If you're pulling all of your events at once, you could change it to pre-load the location associations for the events:

Event.all      # only loads Event objects from the database

#maybe try...

Event.includes(:locations)    # loads Event objects and their Locations

If you need events for some other object (like your @number object), you might want to do something like this in your controller, for example:

@number = Number.where(:id => whatever).includes(:events => :locations)

Now, in this example, your @number object will be pulled from the database along with all of it's events and all of those events' locations.

See the Rails guides for a little more info on this: http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations

Updated with my answer from the comments:

Instead of using scopes, at all, you can simply use Ruby to do the work for you and cut ActiveRecord and extra database calls out of the loop.

Enumerable#group_by will take a collection and group by some attribute you provide. In this case, you could take all of your Events, group by the city and state, and then iterate over the hash it returns:

events = Event.all
events.group_by { |e| "#{e.city}, #{e.state}" }
# => {"Birmingham, AL" => [<event>, <event>, <event>], "Nashville, TN" => [<event>, <event>]}

Doing this may be a little less pretty than using scopes, but it allows you to make one database call and leave it at that.

Here's an O(n^2) solution, assuming your locations.each do |location| loop works as advertised. Should cut it down to 2 db hits.

<% @number.events.locations.each do |location| %>
  <li>
    <h4><%= "#{location.city}, #{location.state}" -%></h4>
    <ol>
      <% @number.events.each do |event| %>
        <% if event.city == location.city and event.state == location.state %>
        <li><%= event.status -%></li>
        <% end %>
      <% end %>
    </ol>
  </li>
<% end %>

Not tested yet, will check back later.