SitePoint Sponsor |
|
User Tag List
Results 1 to 17 of 17
-
Oct 15, 2005, 10:50 #1
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code: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:Code:<% 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.
Thanks again,
SamUsing your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
-
Oct 15, 2005, 10:58 #2
- Join Date
- Aug 2005
- Posts
- 986
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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??Last edited by Fenrir2; Oct 15, 2005 at 11:33.
-
Oct 15, 2005, 14:21 #3
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by Fenrir2
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.Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
-
Oct 15, 2005, 19:56 #4
- Join Date
- Nov 2004
- Location
- Yakima WA.
- Posts
- 100
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Oct 15, 2005, 20:09 #5
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
-
Oct 17, 2005, 14:15 #6
- Join Date
- Nov 2004
- Location
- Yakima WA.
- Posts
- 100
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Sam-
Also there is the finder_sql option for has_and_belongs_to_many relationships. I am using it in an app like so:
Code: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
-
Oct 18, 2005, 04:09 #7
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.
-
Oct 18, 2005, 07:51 #8
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Originally Posted by ezmobius1
Originally Posted by vgarcia
Man, all these things have the same names, at first glance I thought you had gone crazy and meant mvc views.Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
-
Oct 18, 2005, 08:15 #9
- Join Date
- Nov 2004
- Location
- Yakima WA.
- Posts
- 100
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Oct 18, 2005, 08:40 #10
Originally Posted by samsm
Code:SELECT * FROM simpleEventView WHERE firstname='John';
Edit:
called them DB views so as not to confuse with the V in MVC
-
Oct 18, 2005, 11:51 #11
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Good news about Rails .14!
Originally Posted by ezmobius1
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.
Originally Posted by 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).Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
-
Oct 18, 2005, 12:43 #12
Originally Posted by samsm
-
Oct 18, 2005, 15:59 #13
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by vgarcia
Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
-
Oct 18, 2005, 17:03 #14
- Join Date
- Sep 2003
- Location
- Glasgow
- Posts
- 1,690
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Oct 19, 2005, 03:34 #15
- Join Date
- Aug 2005
- Posts
- 986
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Oct 19, 2005, 18:45 #16
- Join Date
- Jun 2004
- Location
- Stillwater, MN
- Posts
- 96
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
For the 1+N query problem, scroll down to "Eager loading of associations" on this page:
http://ar.rubyonrails.com/classes/Ac...ssMethods.htmlRad Smith
My blog
-
Oct 19, 2005, 20:09 #17
- Join Date
- Nov 2001
- Location
- Atlanta, GA, USA
- Posts
- 5,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by McGruff
Originally Posted by Radley
Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?
Bookmarks