Yii2: HasMany via two relations

[angelcoding]

I have the following tables and fields (simplified a bit here) and associated AR models

tbl_account
id, plan_id, name

tbl_user
id, name

tbl_plan
id, name

tbl_account_user
id, account_id, user_id, type

The FK's are obvious.

Basically ...
An account has one plan.
User and account is many to many.
A plan can belong to many accounts and therefore be related to many users.

I want to get all users that are related to a plan so I have setup the following relations in the Plan model ...

public function getAccounts()
{
   return $this->hasMany(Account::className(), ['plan_id' => 'id']);
}
public function getAccountUsers()
{
    return $this->hasMany(AccountUser::className(), ['account_id' => 'id'])->via('accounts');
}
public function getUsers()
{
    return $this->hasMany(User::className(), ['id' => 'user_id'])->via('accountUsers');
}

If I call $planModel->accountUsers I get the expected models but calling $planModel->users returns no results.

I have done some digging and have found the problem is that the user models are correctly fetched from the db but they are indexed by the account_id rather than the user_id so are not found when populating the relation.

Is this expected behavior?
Should I be doing something differently in this scenario?

I know this is probably a rare case and I'm most likely doing something stupid but I couldn't find any docs or examples anywhere for setting up these types of multi-level relations so it would be very useful to know where I'm going wrong.

[qiangxue]

@cebe Will you be able to handle this issue during the next week? Since this is not a common issue, I suggest we move it to 2.0.1 so that you can spend time on more urgent issues.

[cebe]

I have just created a unit test for this and it passes. Need more information about the issue as I can not reproduce it with the information given here. @angelcoding are you sure your data has the expected relations so that you get some users in your case. Try what you get with plain SQL to double check.

[cebe]

@qiangxue if what I have just written in the test did not pass I'd consider this a major issue with AR relations that prevents release, as the test passes I am fine with postponing it.

[angelcoding]

@cebe Thanks. It's a possibility I got a bit of code blindness so will have another look at it tomorrow. Will be sure to give you more details about reproducing if it's still not working for me.

[angelcoding]

This empty relation problem seems to only occur when using with().
@cebe I don't think this was accounted for in the test.

e.g. Plan::find()->with('users')->all(); doesn't work correctly

The relation works fine when lazy loading $planModel->users
Again, possibly something I'm doing wrong but it seems strange that there's even an inconsistency there.

[cebe]

Okay, now I've got a failing test:

    /**
     * https://github.com/yiisoft/yii2/issues/5341
     *
     * Issue:     Plan     1 -- * Account * -- * User
     * Our Tests: Category 1 -- * Item    * -- * Order
     */
    public function testDeeplyNestedTableRelationWith()
    {
        /* @var $category Category */
        $categories = Category::find()->with('orders')->indexBy('id')->all();

        $category = $categories[1];
        $this->assertNotNull($category);
        $orders = $category->orders;
        $this->assertEquals(2, count($orders));
        $this->assertInstanceOf(Order::className(), $orders[0]);
        $this->assertInstanceOf(Order::className(), $orders[1]);
        $ids = [$orders[0]->id, $orders[1]->id];
        sort($ids);
        $this->assertEquals([1, 3], $ids);

        $category = $categories[2];
        $this->assertNotNull($category);
        $orders = $category->orders;
        $this->assertEquals(1, count($orders));
        $this->assertInstanceOf(Order::className(), $orders[0]);
        $this->assertEquals(2, $orders[0]->id);

    }

[qiangxue]

The issue is not easy to fix. The SQL statements are all correct. The issue lies in populating the related models to the primary models due to incorrect buckets (in ActiveRelationTrait::populateRelation() to be exact). For this test case, the population of orderItems relation is correct. The problem occurs when populating orders because the buckets are indexed by (item_id, order_id), instead of the desired (category_id, order_id).

[cebe]

@qiangxue if what I have just written in the test did not pass I'd consider this a major issue with AR relations that prevents release, as the test passes I am fine with postponing it.

this test passes because all tables share the same name of the primary key id and accidentally the tested pks match the expected result :-/

I have changed the primary keys in the test data and now that test fails. See also #7625 for another failing test.

[cebe]

too much work to fix this in 2.0.9, set for 2.0.10

[whuhacker]

After 2+ years I just run into the same situation in my project. so when will this problem be fixed?

[cebe]

As the label says: "complexity:hard" ;) so it is not likely to be fixed very soon. This needs a rewrite of the bucket matching algorithm in eager loading implementation of AR which is quite complex already and might get even more complex when fixing this.

[SamMousa]

If this is complex and no one is working on it yet, should the milestone be removed?

[cebe]

I am planning to work on this soon.

[Erkebulan69]

I've met with this problem a while ago, and I hope that you'll fix it asap

[darkphase01]

I have encountered this one too. Hope there's someone who can patch this. Thanks :)

[DavidPrada]

I have encountered the same problem. Has anyone found a fix yet?

[TiaraMF]

Do you have a time frame as to when we can expect this to be fixed? Needing ASAP. Thank you!

[samdark]

Nope. Hopefully @cebe will have enough time to prepare it for the next release. You can speed things up significantly by implementing it yourself and making a pull request.

[TiaraMF]

Thank you for getting back so quick! That is awesome. When is the next release?

[samdark]

In a month or so if everything will go well.

[TiaraMF]

Great! Thank you.

[rebill]

@samdark Look forward to your good news.

[juffin-halli]

I encountered this problem. In my case the problem is: eager loaded records has typecasted attributes, but junction rows attributes are just raw strings and this valus gives different serialized keys. So as quick fix is it possible to typecast junction rows?

[TiaraMF]

Hello Alexander,

I am just looking for an update on when I might expect this fix to be
implemented? Can I please get an update?

Thank you kindly,

Tia

On Wed, Jun 28, 2017 at 8:59 AM, Alexander Makarov <[email protected]

wrote:

Nope. Hopefully @carsten https://github.com/carsten will have enough
time to prepare it for the next release. You can speed things up
significantly by implementing it yourself and making a pull request.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/yiisoft/yii2/issues/5341#issuecomment-311686367, or mute
the thread
https://github.com/notifications/unsubscribe-auth/AXyho4U-t0nIOdg2-ZkJyP6ho63WDk6Sks5sImo_gaJpZM4CqmzA
.

[samdark]

It's planned for 2.0.14 unless someone will take care of it earlier.

[samdark]

Sure we're OK merging PR earlier.

[TiaraMF]

Hello,

I am just looking for am update, as GitHub shows this is one month past due.

Any update would be much appreciated.

Thank you,

Tia

From: Alexander Makarov
Sent: Saturday, August 12, 2017 4:26 PM
To: yiisoft/yii2
Cc: TiaraMF; Comment
Subject: Re: [yiisoft/yii2] HasMany via two relations (#5341)

Sure we're OK merging PR earlier.
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.

[samdark]

2.0.13 is in the making and this issue won't be solved there.

[lebedev19xx]

Will you fix this?

[shirase]

@lebedev19xx Yes it fixed and already merged.