Imagine a database that has tables for cities, services, and companies. There is also cities_companies and companies_services for the has and belongs to manys.

Some typical data stored might indicate:

CompanyX preforms the service auto repair
CompanyX operates in the city of Atlanta
Atlanta is in the state of Georgia

I have represented this relationship in Active record as:
Code:
State -> has many -> City
City -> belongs to -> State
Services <- has and belongs to many -> Companies
Companies <- has and belongs to many -> Cities
This is workable, but has some issues.

For example, if I list the cities in a state, what would be the best way to get the number of companies that serve that city?

For example, this:
Code:
<% for city in @state.cities %>
  <li><%= city.name %>, <%= city.services.count %> </li>
<% end %>
... has the problem of a query being called for every count. How might that be reduced to one query?

What's worse, what if the page is supposed to be a listing of cities where there are auto mechanics in Georgia? I'm just not sure how to properly do that with Active Record.

I could go into all these models and start adding methods with find_by_sqls, but I'd really like to know if there is some way I can get Active Record to do this without so much fuss. I for example, I would prefer adding in :join and :includes into a find than using find_by_sql.

Also, I'd appreciate a heads up if I am going about this all wrong!

Thank you much for reading! If you need a better explanation about anything, post and I'll do my best to explain.

Thanks again,
Sam