Active Record - Getting indirectly connected data

[samsm]

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:

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:

<% 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

[Fenrir2]

I have no experience with join in activerecord, but couldn't you just join the cities_companies table, and count the number of rows where the ID is the id of the current city?

SELECT cities.name, COUNT(cities.ID)
FROM cities, cities_companies
WHERE cities_companies.cityID = cities.ID
GROUP BY cities.ID

Would this work? If it does, you would need to be able to add a COUNT(cities.ID) in the SELECT, and GROUP BY cities.ID. Maybe that's possible with :include and :join??

[samsm]

Would this work? If it does, you would need to be able to add a COUNT(cities.ID) in the SELECT, and GROUP BY cities.ID. Maybe that's possible with :include and :join??

The fundamental limitation to the find method is that you can't directly alter the "SELECT * FROM table" portion of query. Basically, find works like this:
SELECT * from table [:join] [where :condition] [:order] [:limit]

With :join, you are just slipping text into the middle of a query. When you use :include, the query becomes a mess of joins that accomplish preemptive loading of whatever child objects you specify.

There are a lot of directions to go, I'm basically trying to figure out how I can best use the built-in features of Active Record while accomplishing everything efficiently.

[ezmobius1]

Sam-
Rails is rapidly approaching 1.0. I've been following and there are only 3 more tickets to finish before the big release.

Having said that, there is a feature that you can get if you run edge rails or if you wait a week or two for 1.0 that lets you set the SELECT portion of your quesry. So this will do exactly what I think you are asking for.

[samsm]

Nice. I should totally check out edge Rails on my home computer... should reduce surprises if nothing else.

Changing the SELECT portion of the query would add some more hacking flexibility, that's probably a good change.

[ezmobius1]

Sam-
Also there is the finder_sql option for has_and_belongs_to_many relationships. I am using it in an app like so:

has_many :locations, :finder_sql => "SELECT locations.* " +
                   "FROM locations, item_locations " +
                   "where item_locations.barcode ='#{barcode}' and " + 
                     "item_locations.location_id = locations.id"

Hope maybe that helps with what you want to do. And this works with stable gems

[vgarcia]

Wouldn't a better solution be to use views (assuming your database supports them that is)? Then your model objects stay simple while your database queries stay efficient.

[samsm]

I have to think about each of these for awhile so it take me some time to answer. And then my answers aren't that mind blowing. Oh well.

has_many :locations, :finder_sql => "SELECT locations.* " +
                   "FROM locations, item_locations " +
                   "where item_locations.barcode ='#{barcode}' and " +
                     "item_locations.location_id = locations.id"

This has possibility. I'm thinking that you could pipe all kinds of limitations into the query based upon whatever. The problem I'm generally facing is figuring out which way to go. For now I think I might do best writing some new methods with some new queries that just replace the Active Record stuff I'm wrestling with.

Wouldn't a better solution be to use views (assuming your database supports them that is)? Then your model objects stay simple while your database queries stay efficient.

Total area of ignorance for me. I'm going to have to read up on database views.
Man, all these things have the same names, at first glance I thought you had gone crazy and meant mvc views.

[ezmobius1]

Sam you could also just use:

MyModel.connection.select("Whatever SQL query you want right here")

If you go this route the best thing to do is usually to make a method in you model that abstract this away like so:

In MyModel Class:

def custom_select
MyModel.connection.select("Whatever SQL query you want right here")
end

Then in your controller you can do this:

def index
@items = MyModel.custom_select
end

Many ways to do things with rails depending on what you want to do. By the way the Rails 0.14 isout which is actually 1.0rc1! So 1.0 is within a few weekes of being released with many great new features!

[vgarcia]

Total area of ignorance for me. I'm going to have to read up on database views.
Man, all these things have the same names, at first glance I thought you had gone crazy and meant mvc views.

It's basically a simplified look into a complex data structure. For example, here at work I have 4 tables as part of an event registration: timeslot, event, user, and registration (a many-to-many that stores user id and event id). Let's just say I wanted the user's name, the event's name, and the time it starts. Normally this would be a massive query using joins for all 4 tables. With a DB view you write that query once, name it and save it, then use that DB view name just like a database table. So my 4-table join would go down to something like this:

SELECT * 
FROM simpleEventView
WHERE firstname='John';

You can then base model objects off of this DB view if you need to.

Edit:

called them DB views so as not to confuse with the V in MVC

[samsm]

Good news about Rails .14!

In MyModel Class:
def custom_select
MyModel.connection.select("Whatever SQL query you want right here")
end
Then in your controller you can do this:
def index
@items = MyModel.custom_select
end

I'm glad you showed me this because it answers some other questions I had in mind.

I have a lot of questions but frankly, most of them are of the "in the time it would take to write a coherent question I could have tested it out and found the answer" variety.

Introduction to DB Views

Good description, that refreshes me on the premise. I'm pretty sure I have read about those way back.

I can see those making queries a lot more pretty and probably a heck of a lot more readable, but it wouldn't really alter the amount of model adjustment, right? Just shorter queries (nothing to scoff at).

[vgarcia]

I can see those making queries a lot more pretty and probably a heck of a lot more readable, but it wouldn't really alter the amount of model adjustment, right? Just shorter queries (nothing to scoff at).

Well at that point you could make an EventView model that only pulls from the DB view if that's all you need in your app. If you still need to manage events, users, etc. separately then you'll need the original model objects yes, but in some instances you might only need to manage a sliver of that full data set. That's where DB views and the models you create based on that come in handy.

[samsm]

Well at that point you could make an EventView model that only pulls from the DB view if that's all you need in your app.

That's a great idea.

[McGruff]

I think you have to be prepared to dump ActiveRecord if you want a more complex domain model. Don't ask me how easy it is to do that in Rails, I don't use it.

[Fenrir2]

If you have some complex things you can still use activerecord for the simple part. So you don't have to drop activerecord, because you can make a SQL-query for the complex things.

[Radley]

For the 1+N query problem, scroll down to "Eager loading of associations" on this page:
http://ar.rubyonrails.com/classes/Ac...ssMethods.html

[samsm]

I think you have to be prepared to dump ActiveRecord if you want a more complex domain model. Don't ask me how easy it is to do that in Rails, I don't use it.

That would be very easy to do. Fenrir2's suggestion of just adding everything I want onto the model is what I'll probably do, but the models in Rails simply inherit from ActiveRecord by default, you can change that inheritance to whatever, I would expect.

For the 1+N query problem, scroll down to "Eager loading of associations" on this page:
http://ar.rubyonrails.com/classes/Ac...ssMethods.html

That is an excellent tip. However, it doesn't work over one layer deep, and as of .13 it doesn't work when you use certain options. I mention .13 because I've noticed bug notes that indicate that some of those restrictions are on the the chopping block for future versions. Still, that is a great tool, and one reason I'd like to keep my queries inside the find method.