Nested constraint eager loading overrides existing closure

[vigneshgurusamy]

• Laravel Version: 7.13.0
• PHP Version: 7.4.5
• Database Driver & Version: Postgres 12.2

Description:

When we use multiple with() statements to use nested constrained eager loading, the closure from nested eager loading overrides the existing closure.

Steps To Reproduce:

Below example should eager load organisation model only with org_id and asset_id. But instead, it loads completely with all columns.

User::query()
    ->with([
        'organisation' => function($q) {
            $q->select('org_id', 'asset_id');
        }
    ])
    ->when(true, function ($q) {
        $q->with('orgsanisation.asset');
    })
    ->get();

If we write all nested eager loading queries within a single with() statement, then we are getting excepted results.

User::query()
    ->with([
        'organisation' => function($q) {
            $q->select('org_id', 'asset_id');
        },
        'orgsanisation.asset'
    ])
    ->get();

or if we change the statement positions, it works as expected.

User::query()
    ->when(true, function ($q) {
        $q->with('orgsanisation.asset');
    })
    ->with([
        'organisation' => function($q) {
            $q->select('org_id', 'asset_id');
        }
    ])
    ->get();

[driesvints]

This is expected. A "with" constraint will always prepare a new query. Your second example is the correct one. I doubt the third one truly works? Is orgsanisation.asset even loaded?

[vigneshgurusamy]

@driesvints Third example works for us. Because eagerLoad property in Eloquent builder is always merged with existing eager loading relations. It does not override.

Sometimes, we dynamically create a query builder using multiple if() or $q->when() statements.

Right now we have fixed this problem, by creating an array of relations outside of query builder and pass it to with() method. But it defeats the propose of conditional when() method in the Eloquent builder.

So should I consider this as a known bug and we should not use multiple with() methods in a single query builder along with constrained eager loading?

[driesvints]

Ah I see what you mean. I think I see what the problem is. You're not returning your query builder as stated here: https://laravel.com/docs/7.x/queries#conditional-clauses

Try

User::query()
    ->with([
        'organisation' => function($q) {
            $q->select('org_id', 'asset_id');
        }
    ])
    ->when(true, function ($q) {
        return $q->with('orgsanisation.asset');
    })
    ->get();

[vigneshgurusamy]

@driesvints Thanks for the input. But I don't think when() method is a problem. To prove my point I have created an example without when() method.

Example 1:

User::query()
    ->with('org.asset')
    ->with(['org' => fn($q) => $q->select('org_id', 'asset_id')->selectRaw('\'vignesh\'::TEXT as v')])
    ->find(3);

Result:

=> App\Repositories\User\Model\User {#4273
     user_id: 3,
     org_id: 1,
     email: "admin@xxxxx.in",
     first_name: "XXXX",
     last_name: "XXXX",
     created_at: "2017-04-27 13:22:02",
     updated_at: "2020-06-02 06:34:24",
     deleted_at: null,
     activated_at: "2017-04-27 13:22:02",
     org: App\Repositories\Org\Model\Organisation {#4245
       org_id: 1,
       asset_id: 6097,
       v: "vignesh",
       asset: App\Repositories\Asset\Model\Asset {#4328
         asset_id: 6097,
         org_id: 1,
         name: "XXXX.png",
         unique_id: "a09b32c4-5f10-40da-9485-81ac29cbfd8d",
         created_at: "2018-10-01 07:57:50",
         updated_at: "2018-10-01 07:57:50",
         deleted_at: null,
         category: 4,
       },
     },
   }

Example 2:

User::query()
    ->with(['org' => fn($q) => $q->select('org_id', 'asset_id')->selectRaw('\'vignesh\'::TEXT as v')])
    ->with('org.asset')
    ->find(3);

Result:

=> App\Repositories\User\Model\User {#4268
     user_id: 3,
     org_id: 1,
     email: "admin@xxxxx.in",
     first_name: "XXXX",
     last_name: "XXXX",
     created_at: "2017-04-27 13:22:02",
     updated_at: "2020-06-02 06:34:24",
     deleted_at: null,
     activated_at: "2017-04-27 13:22:02",
     org: App\Repositories\Org\Model\Organisation {#4252
       org_id: 1,
       domain: "avengers",
       name: "XXXXX",
       owner_id: 3,
       asset_id: 6097,
       created_at: "2017-04-27 13:26:59",
       updated_at: "2018-10-01 07:57:56",
       deleted_at: null,
       unique_id: "e9f719afc8fbcd5259ac",
       asset: App\Repositories\Asset\Model\Asset {#4349
         asset_id: 6097,
         org_id: 1,
         name: "XXXX.png",
         unique_id: "a09b32c4-5f10-40da-9485-81ac29cbfd8d",
         created_at: "2018-10-01 07:57:50",
         updated_at: "2018-10-01 07:57:50",
         deleted_at: null,
         category: 4,
       },
     },
   }

You can see the difference between the examples. The first example returns my selectRaw() output as expected, but the second example does not return it.

[driesvints]

You're using find which will select all columns.

[vigneshgurusamy]

@driesvints I understand find() returns all columns from the users table. But my question is why my organisation eager load relation query returns to two different results when I change the order.

Note: My selectRaw() statement is in organisation relation not in users table.

[vigneshgurusamy]

@driesvints Could you reopen this issue until we find the solution to this problem? I'm happy to give more information if you need.

[driesvints]

@vigneshgurusamy can you please try a support channel before I reopen this?

[vigneshgurusamy]

@driesvints I have posted this issue in laracasts forum. But I haven't got any feedback until now. Could you reopen this issue?

[driesvints]

I'll move this to discussions instead.