Can AR retrieve extra data from the associative table?

Archives Yii 1.0.x Discussions
1

Well as the title says, I'm curious if this is possible… I'll explain more in detail of course…

I have a table (and model) 'Item' which can be a lot of things, including schematics that shows how to build stuff (it's for a game tool, a build-helper to be specific)… So anyway, a schematic will need resources and/or modules (which may in turn themselves require resources and/or other modules).

For this purpose I have a table I've called 'Need' which has the primary and foreign keys, linking both 'itemId' and 'needItem' to 'Item.id', which was fine up until it came time to define relations… Obviously this is a many-to-many scenario, and I do know how to set those up, but here's the catch… It may need more than one of a resource/module, which means I need a 'count' column as well… The only logical place to put that would be in the 'Need' associative table, but how do I retrieve it from there along with the query?

Or do I need to instead use another approach? I'm thinking something like "Item has-many Need" and "Need has-one Item", but then Item would belong-to Need, which would make for a circular reference… I think…

Urgh… I think I’ve been staring at this for too long, so I should just leave it be, and hope that someone here knows how to best solve it. :)

Thanks in advance.

2

I think your other approach (item has-many need, need has-one item, item belongs-to need) should be fine. It's not really circular reference as for the items related with other items via need are different. When you declare the AR relations, you should choose different names for these relations.

3

Thank you for the surprisingly swift response. Hehe. :)

I have a follow-up question, though, relating to many-to-many relationships, if you don't mind?

I've recently moved to Yii from CakePHP, and while I understood quite perfectly how many-to-many relationships could work on there, since there was a strict database schema format one must follow, I'm not quite sure how it works in Yii…

I'll elaborate…

I have two tables 'Location' and 'Shop' with a many-to-many relationship, which I use 'LocationShop(locationId, shopId)' for… Now obviously when there's foreign keys defined it will just use those to determine which ID is linked to which table, right? But… It says in the documentation that since 1.0.2 it's possible to do it without foreign keys, so how would it know which ID belongs to which table?

4

When you specify the foreign keys in the relation, you need to list them in the order of (A, B), where A is the key referring to the class containing the relation declaration, and B is the other key.

5

So… In the ‘Location’ class it would be ‘LocationStore(locationId, storeId)’ and in the ‘Store’ class it would be ‘LocationStore(storeId, locationId)’, or is it the other way around? :)

6

Yes, that's right.

7

Hmm… Another many-to-many related question (sorry!)

I have a table called 'System', and I need to link these to one another (think jump gates or wormholes) in a many-to-many way, I assume. How would I best go about this? In this case I don't need extra data from the associative table, so I wonder if many-to-many is possible there, or if I should again use the has-many/has-one/belongs-to approach from above?

8

Yes, it's possible. You just need to declare the many-many relation.

9

I was assuming it would be possible, but I'm having trouble figuring out how to do it, since for a many-to-many you need two relationship indicators…

So do I need to do something like this?:

'systems' => array(self::MANY_MANY, 'System', 'SystemSystem(fromSystem, toSystem)'),

'systems2' => array(self::MANY_MANY, 'System', 'SystemSystem(toSystem, fromSystem)')

10

Yes, something like this. Theoretically, it should work, but I didn't test it though.