SitePoint Sponsor

User Tag List

Results 1 to 17 of 17

Hybrid View

  1. #1
    SitePoint Wizard samsm's Avatar
    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
    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
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  2. #2
    SitePoint Guru
    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.

  3. #3
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2
    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.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  4. #4
    SitePoint Zealot
    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.

  5. #5
    SitePoint Wizard samsm's Avatar
    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?

  6. #6
    SitePoint Zealot
    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

  7. #7
    SitePoint Wizard samsm's Avatar
    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.
    Quote Originally Posted by ezmobius1
    Code:
    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.

    Quote Originally Posted by 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.
    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.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  8. #8
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by samsm
    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:
    Code:
    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

  9. #9
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    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.

  10. #10
    SitePoint Zealot
    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!

  11. #11
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good news about Rails .14!
    Quote Originally Posted by ezmobius1
    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.
    Quote Originally Posted by vgarcia
    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).
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  12. #12
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by samsm
    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.

  13. #13
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vgarcia
    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.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  14. #14
    simple tester McGruff's Avatar
    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.

  15. #15
    SitePoint Guru
    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.

  16. #16
    SitePoint Enthusiast
    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.html
    Rad Smith
    My blog

  17. #17
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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.
    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.

    Quote Originally Posted by 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
    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.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •