Active Record - Getting indirectly connected data
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:
This is workable, but has some issues.
State -> has many -> City
City -> belongs to -> State
Services <- has and belongs to many -> Companies
Companies <- has and belongs to many -> Cities
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:
... has the problem of a query being called for every count. How might that be reduced to one query?
<% for city in @state.cities %>
<li><%= city.name %>, <%= city.services.count %> </li>
<% end %>
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.