Rails - accessing more data in join tables

[DevilBear]

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.

[Fenrir2]

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

[DevilBear]

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

[DevilBear]

OK I take my rant back a little bit... it was fairly easy to override the update method in the model.

But still :-P

[Fenrir2]

But still :-P

? ;-)

[DevilBear]

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?

[Luke Redpath]

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.

[DevilBear]

Impatient, yes.

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

[Luke Redpath]

Where did you read that? Its in the latest version, been there since 1.1 I think. Read the docs.

[Mandibal]

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

[Fenrir2]

Why do you want to create composite primary keys? Why not artificial id's?

[Mandibal]

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?

[Luke Redpath]

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.

[Mandibal]

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.

[Fenrir2]

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.

[Mandibal]

It does if you do validates_uniqueness_of.

That's what I said in my post.

[Fenrir2]

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.