Why is the ActiveRecord relationships handled like this?

Yii 2.0 General Discussions
1

I have a ActiveRecord class "Users" with a "hasMany" relationship defined like this:


public function getRelationships()

    {

        return $this->hasMany("relationships", ['id' => 'relationId'])->viaTable('relationshipTable', ['userId' => 'id']);

    }

This generates two SQL-queries like this:


SELECT relationId 

FROM relationshipTable 

WHERE userId = X



SELECT * 

FROM relationships 

WHERE id IN (A,B,C,D,E,F,G)

Why doesn’t the relationship generate one SQL-query like this?


SELECT relationships.* 

FROM relationships 

JOIN relationshipTable ON relationshipTable.relationId = relationships.id

WHERE relationshipTable.userId = X
2

Because for example if your two table had 2 same column (for ex. ‘id’),

value of attribute ‘id’ of model and his relationed model

would not be identified exacty.

So if you need to have attributes of relationed model then you should use relations with hasOne or hasMany. But if you need only to filter model’s record against an other table, you can use innerJoin() or leftJoin() of ActiveRecord class.

3

Hi Fabrizio. I’m not sure I follow you.

My relationship table would only consist of two ID columns which would "key together" my relations. So my "relationshipTable" table would look like this:




-------------------

relationId | userId

-------------------

1 | 1

2 | 1

3 | 1

4 | 1

Let’s say my relationshipTable holds “orders” and my user have hundreds of orders. I don’t want those orders to be fetched with an “IN()” statement and hundreds of ID’s specified in the SQL. This “hasMany” relationship would be much better with one SQL-statement using a JOIN.

Am I using the relationship part of the ActiveRecord in a wrong way maybe?

4

It depends if you run the query in eager or lazy loading mode.

http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#lazy-and-eager-loading

Try to run you query using yii\db\ActiveQuery::with()

Example form the docs:

$orders = Order::find()->select([‘id’, ‘amount’])->with(‘customer’)->all();

// $orders[0]->customer is always null. To fix the problem, you should do the following:

$orders = Order::find()->select([‘id’, ‘amount’, ‘customer_id’])->with(‘customer’)->all();

Eager loading use more memory but is much faster than lazy when you must handle large amount of data with relationship tables.

I used it to resolve a performance issue with oracle (which didn’t affect mysql sensibly).

5

Ah okay. I think I’ll switch my code to see what that does to my relations. Right now I’m just accessing my relation in question on my record like to $record->relationships();. I’ll try it your way. Cheers!