SitePoint Sponsor

User Tag List

Results 1 to 17 of 17

Hybrid View

  1. #1
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rails - accessing more data in join tables

    I'm working with a database that holds race schedule info, including results.

    Each race has many drivers, and each driver can be in many races. Pretty straight forward.

    So we have a races table, a drivers table, and a drivers_races join table.

    For each race & driver pair, there exists unique data, such as starting position, end position, etc.

    It makes sense to me to put those into the join table like so:

    drivers_races
    driver_id int
    race_id int
    start int
    result int
    etc.

    Any advice on working with this table? I could create a model for it maybe, but I am not familiar enough with RoR yet to really know how to access it efficiently. I'd like to avoid using raw SQL if possible.

  2. #2
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's better to create a separate model for the join table. You might want to look at this:

    http://www.matthewman.net/articles/2...d-goes-through

  3. #3
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <rant on>
    I think I've said before, Rails spends only half it's time making my life easier. And it seems like every other day on this project, I'm finding Rails getting in my way on something it *should* take care of automatically.

    It's complex enough that you have to know something in order to use it, and dumb enough that you can only use it for the simplest of projects.

    And when I find this while googling:

    Composite primary keys are not supported... yet. But keep an eye on Edge Rails.
    I want to find the person that wrote it and torture him slowly, perhaps Clockwork Orange style. YOU keep YOUR eyes on Edge Rails. Some of us have work to do NOW.

    Meanwhile, the client is getting very impatient, and I could have busted out their site in PHP by now.

    </rant off>

    BTW, created a model for the join table... got this:

    Mysql::Error: Unknown column 'id' in 'where clause'

    Guess we're back to SQL everywhere, since the join table has a composite primary key.

  4. #4
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I take my rant back a little bit... it was fairly easy to override the update method in the model.

    But still :-P

  5. #5
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But still :-P
    ? ;-)

  6. #6
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But still, other things have NOT been that easy to work around. I hate to think of what anyone working with a schema that's actually complicated must go through.

    Has anyone here used Rails with a database containing complex relationships and made it through with your sanity?

  7. #7
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why do you need to use a composite key on your join table? Just give it an id column and off you go.

    It seems like you are getting very impatient with something that you still haven't taken the time to learn fully.

    As somebdoy says, you want a has_many :through relationship to link your Race and Driver models. In between these is your join model.

  8. #8
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Impatient, yes.

    Apparently :through is not available in the current stable version of Rails.

  9. #9
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where did you read that? Its in the latest version, been there since 1.1 I think. Read the docs.

  10. #10
    Mal Reynolds Mandibal's Avatar
    Join Date
    Aug 2003
    Location
    Columbus
    Posts
    718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can simulate composite primary keys creating uniqueness by using validates_uniqueness_of and :scope in your models.

    I used to have a composite primary key for a schedule table composed of: week_number, season_id

    Now I just use this in my model:
    validates_uniquness_of :week_number, :scope=>:season_id
    Erh

  11. #11
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why do you want to create composite primary keys? Why not artificial id's?

  12. #12
    Mal Reynolds Mandibal's Avatar
    Join Date
    Aug 2003
    Location
    Columbus
    Posts
    718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2
    Why do you want to create composite primary keys? Why not artificial id's?
    Why not composite or compound primary keys instead of having to generate artificial id's?
    Erh

  13. #13
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mandibal
    Why not composite or compound primary keys instead of having to generate artificial id's?
    Because Rails does all of the work of generating those artificial IDs (it will even create the ID column for you if you create your table using a migration) therefore its absolutely no extra effort to do so. The artificial IDs are completely transparent.

    Thats not to say Rails shouldn't have support for composite primary keys in the future - I think it should, but only for those who working with legacy databases.

  14. #14
    Mal Reynolds Mandibal's Avatar
    Join Date
    Aug 2003
    Location
    Columbus
    Posts
    718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Luke Redpath
    Because Rails does all of the work of generating those artificial IDs (it will even create the ID column for you if you create your table using a migration) therefore its absolutely no extra effort to do so. The artificial IDs are completely transparent.

    Thats not to say Rails shouldn't have support for composite primary keys in the future - I think it should, but only for those who working with legacy databases.
    All Rails does is use an autoincrement primary key called ID. It makes no assumptions on whether the model should have data that is repeated for what should be unique situations. You still have to go the extra mile of adding validations to preserve unique situations that are in the model data. Just because what will be object A with ID 1 from the Rails AR has two attributes that create uniqueness doesnt stop rails from adding a new row that will be object B with ID 2 that has the exact same values for the two attributes. Now you have two objects that could be colliding. The database already has mechanisms built in for this kind of thing. That's a strength of the database engine. I personaly think rails tries to ignore too much of the database functionality so that it can handle it itself. In some instances this may be enough and it may be better but I'm not convinced its the case for composite primary keys. Speed is probably worse by having to use validations instead of the databases native implementation that solves this problem already.

    Look, I'm not claiming to have tested this all out and have data that says A is better than B or to be an expert that intimately knows the inner workings of Rails and relational databases but it seems like you guys are accepting that Rails does it the right way all the time. This may not be the case. That's why I ask why not use compound primary keys instead of artificially generated ID's. To question wether this is really the right way.
    Erh

  15. #15
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just because what will be object A with ID 1 from the Rails AR has two attributes that create uniqueness doesnt stop rails from adding a new row that will be object B with ID 2 that has the exact same values for the two attributes.
    It does if you do validates_uniqueness_of.

  16. #16
    Mal Reynolds Mandibal's Avatar
    Join Date
    Aug 2003
    Location
    Columbus
    Posts
    718
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Fenrir2
    It does if you do validates_uniqueness_of.
    That's what I said in my post.
    Erh

  17. #17
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whoops

    You still have to go the extra mile of adding validations to preserve unique situations that are in the model data.
    That is, an extra mile if you have uniqueness already set up in your database. It's a mile in a different direction if you haven't.


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
  •