Eager Loading a relation that calls another relation returns incorrect results

[allandantasdev]

Laravel Version

11.7.0

PHP Version

8.3.7

Database Driver & Version

PostgreSQL 15.7 and MySQL 8.0.37

Description

When eager loading a model relationships, the results differ from when they are lazy loaded.
This issue occurs whenever a Relation is called inside the definition of another Relation, but only when eager loading is used on the main one.

After investigation I realized that the cause lies in Illuminate\Database\Eloquent\Builder@eagerLoadRelation:

 // First we will "back up" the existing where conditions on the query so we can
 // add our eager constraints.
 $relation = $this->getRelation($name);
 $relation->addEagerConstraints($models);
 
 // Then we will merge the wheres that were on the
 // query back to it in order that any where conditions might be specified.
 $constraints($relation);

Which calls the Illuminate\Database\Eloquent\Builder@getRelation method:

 // We want to run a relationship query without any constraints so that we will
 // not have to remove these where clauses manually which gets really hacky
 // and error prone. We don't want constraints because we add eager ones.
 $relation = Relation::noConstraints(function () use ($name) {
     try {
         return $this->getModel()->newInstance()->$name();
     } catch (BadMethodCallException) {
         throw RelationNotFoundException::make($this->getModel(), $name);
     }
 });

Which gets to the root cause of the problem in Illuminate\Database\Eloquent\Relations\Relation@noConstraints:

$previous = static::$constraints;

 static::$constraints = false;

 // When resetting the relation where clause, we want to shift the first element
 // off of the bindings, leaving only the constraints that the developers put
 // as "extra" on the relationships, and not original relation constraints.
 try {
 return $callback();
 } finally {
 static::$constraints = $previous;
 }

The method Illuminate\Database\Eloquent\Relations\Relation@noConstraints is called during eager loading and uses a boolean attribute to manage the constraints. However, this flag is static and seems to be causing the where clauses of other relations to be omitted, leading to incorrect results.

Steps To Reproduce

1. Create the following schema

        // 0001_01_01_000000_create_users_table.php
        Schema::create('categories', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');
            $table->text('name');
            $table->timestamps();
        });
        Schema::create('examples', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('category_id');
            $table->foreign('category_id')->references('id')->on('categories');
            $table->text('name');
            $table->boolean('restricted');
            $table->timestamps();
        });

2. Create the following seeder:

        $user = User::factory()->create();

        $categories = [
            Category::query()->create(['user_id' => $user->id, 'name' => 'Category 1']),
            Category::query()->create(['user_id' => $user->id, 'name' => 'Category 2']),
            Category::query()->create(['user_id' => $user->id, 'name' => 'Category 3']),
        ];

        Example::insert([
            ['category_id' => $categories[0]->id, 'name' => 'Example 1', 'restricted' => false],
            ['category_id' => $categories[1]->id, 'name' => 'Example 2', 'restricted' => true],
            ['category_id' => $categories[2]->id, 'name' => 'Example 3', 'restricted' => false],
            ['category_id' => $categories[2]->id, 'name' => 'Example 4', 'restricted' => false],
            ['category_id' => $categories[2]->id, 'name' => 'Example 5', 'restricted' => true],
        ]);

        User::factory()->create(); // another user just for demonstration

3. Create a scope in the Example model:

class Example extends Model
{
    // ...
    /**
     * The authenticated user should only have access to not restricted Examples
     * or to the examples he owns.
     */
    public function scopeHasAccess(Builder $query, ?User $user = null): Builder
    {
        return $query->where(
            fn ($query) => $query->where('restricted', false)
                ->when(
                    $user !== null,
                    fn($query) => $query->orWhereIn('category_id', $user->categories->pluck('id'))
                )
        );
    }
}

4. Add the following relations to the models:

class User extends Authenticatable
{
    // ...
    public function categories(): HasMany
    {
        return $this->hasMany(Category::class);
    }
}

class Category extends Model
{
    // ...
    public function examples(): HasMany
    {
        return $this->hasMany(Example::class);
            ->hasAccess(Auth::user());
     }
}

5. Authenticate:

    Auth::login(User::find(1));
    // Auth::login(User::find(2));

6. Fetch all categories with their respective examples

        dump('Authenticated user: '.Auth::user()->id);
        Category::get()->each(
            fn(Category $category) => dump(sprintf('- %s: %d examples', $category->name, $category->examples->count()))
        );
        
// Authenticated user: 1
// - Category 1: 1 examples"
// - Category 2: 1 examples"
// - Category 3: 3 examples"
// ----------------------------
// Authenticated user: 2"
// - Category 1: 1 examples"
// - Category 2: 0 examples"
// - Category 3: 2 examples"

7. Execute the code again but eager loading the examples relation:

        dump('Authenticated user: '.Auth::user()->id);
        Category::with('examples')->get()->each(
            fn(Category $category) => dump(sprintf('- %s: %d examples', $category->name, $category->examples->count()))
        );
        
// Authenticated user: 1
// - Category 1: 1 examples"
// - Category 2: 1 examples"
// - Category 3: 3 examples"
// ----------------------------
// Authenticated user: 2"
// - Category 1: 1 examples"
// - Category 2: 1 examples"
// - Category 3: 3 examples"

• Expected behavior:
  The fetched relations should be consistent regardless of whether they are lazy or eager loaded.

• Actual behavior:

  • Without eager loading: The user of id 2 has access to 3 examples (correct)
  • With eager loading: The user of id 2 has access to 5 examples (wrong)

[crynobone]

Hey there, thanks for reporting this issue.

We'll need more info and/or code to debug this further. Can you please create a repository with the command below, commit the code that reproduces the issue as one separate commit on the main/master branch and share the repository here?

Please make sure that you have the latest version of the Laravel installer in order to run this command. Please also make sure you have both Git & the GitHub CLI tool properly set up.

laravel new bug-report --github="--public"

Do not amend and create a separate commit with your custom changes. After you've posted the repository, we'll try to reproduce the issue.

Thanks!

[allandantasdev]

Here it is: allandantasdev/laravel-bug-report-51825@ce9741a

[allandantasdev]

Additional info:

• This is the query generated by Auth::user()->categories->pluck('id') at Example@scopeHasAccess:24 while lazy loading
  

• And is the same query generated after trying to eagerloading Category::with('examples'):
  

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[Tofandel]

I found a quick and dirty solution, in the constructor of Relation (beware that it breaks some other cases)

   public function __construct(Builder $query, Model $parent)
    {
        $this->query = $query;
        $this->parent = $parent;
        $this->related = $query->getModel();

        $this->addConstraints();

        static::$constraints = true; // This
    }

I've been working on getting a proper fix, but there doesn't seem to be a straight path forward as changing one thing breaks another one, this will likely require some debug_backtrace to fix this without breaking some test cases

[crynobone]

I believe #52461 have fixed this issue, please open a new issue if you still face the problem

[Tofandel]

Sorry, that PR doesn't address this issue at all, I was only talking about how using static causes this kind of issues

You can reopen it

[marius-mcp]

        // We want to run a relationship query without any constrains so that we will
        // not have to remove these where clauses manually which gets really hacky
        // and error prone. We don't want constraints because we add eager ones.
        $relation = Relation::noConstraints(function () use ($name) {
            try {
                return $this->getModel()->newInstance()->$name();
            } catch (BadMethodCallException $e) {
                throw RelationNotFoundException::make($this->getModel(), $name);
            }
        });

@allandantasdev this makes sense because the conditions might imply columns from the model (other than the foreign key), that will not be in the query for the related model.

the solution might be to add the constraints on the collection of related models based on each model

    protected function eagerLoadRelation(array $models, $name, Closure $constraints)
    {
        // First we will "back up" the existing where conditions on the query so we can
        // add our eager constraints. Then we will merge the wheres that were on the
        // query back to it in order that any where conditions might be specified.
        $relation = $this->getRelation($name);

        $relation->addEagerConstraints($models);

        $constraints($relation);
///////////////////////////////////////////////// HERE
        // Once we have the results, we just match those back up to their parent models
        // using the relationship instance. Then we just return the finished arrays
        // of models which have been eagerly hydrated and are readied for return.
        return $relation->match(
            $relation->initRelation($models, $name),
            $relation->getEager(), $name
        );
    }

[macropay-solutions]

Sorry for the previous replies. Now we understood the real issue.
This is called

$user->categories->pluck('id')

while the

static::$constraints

is false because of the eager load of the relation in which it is called, resulting in all categories being retrieved not only the categories from that user.

[macropay-solutions]

@Tofandel @allandantasdev

If the Relation had this function(which is doable via macros as static function):

    /**
     * Run a callback with constraints enabled on the relation.
     *
     * @param  \Closure  $callback
     * @return mixed
     */
    public static function yesConstraints(Closure $callback)
    {
        $previous = static::$constraints;

        static::$constraints = true;

        try {
            return $callback();
        } finally {
            static::$constraints = $previous;
        }
    }

then the scope or condition could be written like this:

class Example extends Model
{
    // ...
    /**
     * The authenticated user should only have access to not restricted Examples
     * or to the examples he owns.
     */
    public function scopeHasAccess(Builder $query, ?User $user = null): Builder
    {
        return $query->where(
            fn ($query) => $query->where('restricted', false)
                ->when(
                    $user !== null,
                    function ($query) use ($user) {
                          $userCategories = $user->relationLoaded('categories') ?
                              $user->categories :
                              Relation::yesConstraints(function () use ($user) {
                                  try {
                                      return $user->categories();
                                  } catch (BadMethodCallException $e) {
                                      throw RelationNotFoundException::make($user, 'categories');
                                  }
                              });

                        return $query->orWhereIn('category_id', $userCategories->pluck('id');); // also, this can be written with a sub select and the issue is avoided in that way
                    }
                )
        );
    }
}

Can this solution be embedded in laravel somehow so the user does not need to handle it in the scope or relation definition?

[macropay-solutions]

UPDATE: #51825 (comment)

[Tofandel]

@macropay-solutions I already tried this solution but it breaks some special cases

The withConstraints approach might be the easieast workaround to get into the core

[macropay-solutions]

@Tofandel Your code always set static:$constraints = true; #51825 (comment)

Our suggestion sets it only once (with the previous value not with hard codded true) at the construct's end and not in that finally clause.

But if you say it breaks special cases, we believe you.

[marius-mcp]

@Tofandel can you please share those special cases?

[Tofandel]

It will fail on those kind of relations because the Relation constructor is called twice in there and so it restores constraints too early

    public function price_without_key_in_aggregates()
    {
        return $this->hasOne(HasOneOfManyTestPrice::class, 'user_id')->ofMany(['published_at' => 'MAX']);
    }

    public function price_with_shortcut()
    {
        return $this->hasOne(HasOneOfManyTestPrice::class, 'user_id')->latestOfMany(['published_at', 'id']);
    }
    
    
    public function teamMatesWithPendingRelation()
    {
        return $this->through($this->ownedTeams())
            ->has(fn (Team $team) => $team->members());
    }

[Tofandel]

Just run the vendor/bin/phpunit tests on the repo with your changes and see how it goes, likely it will be very unreliable, I doubt where is the only method that needs this

[marius-mcp]

@Tofandel @macropay-solutions There is another case that would be uncovered:

public function relationName(): HasMany|HasManyThrough
{
    if ($this->exists && $this->children()->exist()) { // construct of Relation is called on eager loading relations in an existing model which I remember had issues also
        return $this->hasManyThrough...; // construct of Relation is called
    }

    return $this->hasMany...; // construct of Relation is called
}

[macropay-solutions]

@marius-mcp
Good catch. Then the whole Relation::noConstraints logic from \Illuminate\Database\Eloquent\Builder::getRelation is not fitting in...

[macropay-solutions]

@Tofandel

Another solution that sadly can't be implemented via macros for older versions:

\Illuminate\Database\Eloquent\Relations\Relation

    protected static ?string $noConstraintsForRelationName = null;

    public function __construct(Builder $query, Model $parent)
    {
        $this->query = $query;
        $this->parent = $parent;
        $this->related = $query->getModel();

        /** @see self::noConstraints */
        if (
            '' !== (string)static::$noConstraintsForRelationName
            && '' !== (string)$parent->nowEagerLoadingRelationNameWithNoConstraints
        ) {
            /**   1st execution is for ExampleModel $exampleModel on 'rel' relation
                 with nowEagerLoadingRelationNameWithNoConstraints = 'rel'
                           and with $noConstraintsForRelationName = 'rel'
             */
            //           2nd execution is for UserModel $userModel on 'categories' relation
            //     with nowEagerLoadingRelationNameWithNoConstraints = null
            //                and with $noConstraintsForRelationName = 'rel'

            //    1st execution is for ExampleModel $exampleModel on 'children' relation
            //    with nowEagerLoadingRelationNameWithNoConstraints = null
            //              and with $noConstraintsForRelationName = 'rel'
            /**   2nd execution is for ExampleModel $exampleModel on 'rel' relation
                with nowEagerLoadingRelationNameWithNoConstraints = 'rel'
                          and with $noConstraintsForRelationName = 'rel'
             */
            // 3rd execution is for UserModel $userModel on 'categories' relation
            //    with nowEagerLoadingRelationNameWithNoConstraints = null
            //       and with $noConstraintsForRelationName = 'rel'
            static::$constraints =
                static::$noConstraintsForRelationName !== $parent->nowEagerLoadingRelationNameWithNoConstraints;
        }

        $this->addConstraints();
    }

    /**
     * Run a callback with constraints disabled on the relation based on relationName.
     */
    public static function noConstraints(\Closure $callback, ?string $relationName = null): mixed
    {
        $previous = static::$constraints;
        $previousNoConstraintsForRelationName = static::$noConstraintsForRelationName;

        if ('' !== (string)$relationName) {
            static::$noConstraintsForRelationName = $relationName;
        } else {
            static::$constraints = false;
        }

        try {
            return $callback();
        } finally {
            static::$constraints = $previous;
            static::$noConstraintsForRelationName = $previousNoConstraintsForRelationName;
        }
    }

\Illuminate\Database\Eloquent\Builder

    /**
     * Get the relation instance for the given relation name (for eager loading)
     *
     * @param  string  $name
     * @return \Illuminate\Database\Eloquent\Relations\Relation
     */
    public function getRelation($name)
    {
        // We want to run a relationship query without any constrains so that we will
        // not have to remove these where clauses manually which gets really hacky
        // and error prone. We don't want constraints because we add eager ones.
        $relation = Relation::noConstraints(function () use ($name) {
            try {
                $model = $this->getModel()->newInstance();
                $model->nowEagerLoadingRelationNameWithNoConstraints = $name;

                return $model->$name();
            } catch (BadMethodCallException) {
                throw RelationNotFoundException::make($this->getModel(), $name);
            }
        }, $name);

        $nested = $this->relationsNestedUnder($name);

        // If there are nested relationships set on the query, we will put those onto
        // the query instances so that they can be handled after this relationship
        // is loaded. In this way they will all trickle down as they are loaded.
        if (count($nested) > 0) {
            $relation->getQuery()->with($nested);
        }

        return $relation;
    }

UPDATE

    protected function getRelationWithoutConstraints($relation): Relation
    {
        return Relation::noConstraints(function () use ($relation) {
            $model = $this->getModel();
            /** @var Model $model */
            $model->nowEagerLoadingRelationNameWithNoConstraints = $relation;

            return $model->{$relation}();
        }, $relation);
    }

    protected function getBelongsToRelation(MorphTo $relation, $type): BelongsTo
    {
         /** here it would work as before ! */
        $belongsTo = Relation::noConstraints(function () use ($relation, $type) { 
            return $this->model->belongsTo(
                $type,
                $relation->getForeignKeyName(),
                $relation->getOwnerKeyName()
            );
        });

        $belongsTo->getQuery()->mergeConstraintsFrom($relation->getQuery());

        return $belongsTo;
    }

\Illuminate\Database\Eloquent\Concerns\HasRelationships

    public ?string $nowEagerLoadingRelationNameWithNoConstraints = null;

The definition of the relation:

    public function productsValueScope(): HasManyThrough
    {
        return $this->products()->where(
            fn ($query) => $query->where('value', '>',  10)->when(
                true,
                fn($query) => $query->orWhereIn('id', Operation::query()->where('id', 2)->first()->children->pluck('id')->toArray())
                )
        );
    }

RESULTS:

GET page=1&withRelations[0]=productsValueScope&limit=2

        -- Operation::query()->where('id', 2)->first()
        "0.48 ms, sql: select * from `operations` where `id` = 2 limit 1",

        -- ->children->pluck('id')
        "0.55 ms, sql: select * from `operations` where `operations`.`parent_id` = 2 and `operations`.`parent_id` is not null",

        -- count
        "0.56 ms, sql: select count(*) as aggregate from `operations`",

        -- fetch the list
        "1 ms, sql: select * from `operations` order by `created_at` desc limit 2 offset 0",

        -- eager load
        "0.63 ms, sql: select * from `operations` where `id` = 2 limit 1",
        "0.56 ms, sql: select * from `operations` where `operations`.`parent_id` = 2 and `operations`.`parent_id` is not null",
        "1.15 ms, sql: select distinct `products`.*, `operations_products_pivot`.`operation_id` as `laravel_through_key` from `products` inner join `operations_products_pivot` on `operations_products_pivot`.`product_id` = `products`.`id` where `operations_products_pivot`.`operation_id` is null and (`value` > 10 or `id` in (3)) and `operations_products_pivot`.`operation_id` in (3748917, 3748918)"

@crynobone we will not open a MR or PR so feel free to do it if you want to fix the issue.

[marius-mcp]

@macropay-solutions

Another solution that sadly can't be implemented via macros for older versions:

The good thing is that if the relations are defined with

if ($this->exists && {{other relations calls}}) {

or no other relations are called after the relation is instantiated, then this bug is not affecting older versions.

For the ->load( function call on the existing model, the fix is to not use it until this gets in the core.

So there is a way of avoiding it.

I tested the change. Works also for me.

[jkpeyi]

@allandantasdev Hello

Are you sure this code is correct ?

{
    // ...
    public function examples(): HasMany
    {
        return $this->hasMany(Example::class);  // this ends by a semicolon , so how the ->hasAccess() will work ?
            ->hasAccess(Auth::user());  
     }
}```

[marius-mcp]

@jkpeyi that is just a typo.

[macropay-solutions]

FYI
We will not be issuing a merge request or pull request with this solution.
We shared the solution to help the community but only if the community wants to be helped.

It would be pity to see here "closing this issue because it is too old" or something similar just like we saw happening in the past with other bugs that were not solved.

Please do not bury this under the carpet.

[macropay-solutions]

@taylorotwell do you see any uncovered situations by this solution #51825 (comment) ?

[marius-mcp]

@macropay-solutions this will be buried as you said. Or would not @Tofandel ?

[Tofandel]

@macropay-solutions Why exactly would you not be issuing a PR? This seems very "here is a 'solution' have fun actually implementing and then fixing when it doesn't work"

Sorry but I'm not being paid to work on this kind of issues and do it of my own volition and free time, which I do not have any right now to take this on. If you want it fixed, you should really submit that PR yourself

[macropay-solutions]

@Tofandel
We respect your point of view, as you should also respect ours.
We spent 2 days (also not paid, and we are a team not just a dev) figuring out this solution after several months in which no one bothered to find one (thanks for your help as input by the way). If that is not enough, then we simply don't care.

The process of submitting PRs is way too complicated, we have to fork the repository and so on. Not to mention that we must follow the laravel community's codding standards which differ almost 180 degrees from ours.

We feel that someone with more experience in laravel PRs should take this solution and test it to see if all edge cases are covered.

The day will come for us to fork laravel (because we will be forced not because we want to) and then the fork will not be for submitting PRs.

@marius-mcp it is up to laravel team. They have a habit of "ghosting" proposals, solutions and issues from our experience.

[Tofandel]

We spent 2 days (also not paid, and we are a team not just a dev) figuring out this solution after several months in which no one bothered to find one

I did (even though this bug never affected me), spent 3 hours and simply dropped out when I got caught by the edge cases I mentionned which pushed my PR into too time consuming

I would like to point out that you are a company using an open source software, so while you might not be paid to fix bug in said software, you do have the resources to take on a small PR if you have the resources to spend 2 days writing a solution for yourself, using a software provided for free.
If you want a bug fixed in OSS you usually have to open a PR, that is how it has always worked in open source, because devs simply don't have time to fix every niche bug anyone finds. It is simply good etiquette for a company to do that and as MIT licensed you are under no obligation to open a PR or share your solution as well but you also cannot expect anyone else to do that for you if you fail to do so (different story for GPL though where it's required by the license that you have to contribute your fixes)

The process of submitting PRs is way too complicated, we have to fork the repository and so on. Not to mention that we must follow the laravel community's codding standards which differ almost 180 degrees from ours.

It's not that complicated, but if you think it is then that is hypocritical in that you expect someone else to do that complicated thing just because you don't want to

• you just fork via github
• clone your fork
• composer install
• apply your patch and run the test suite to make sure that nothing breaks (this is the hard part of the PR which you just cannot put on someone else)
• for the coding standard I believe you just have vendor/bin/pint and it formats your code automatically
• Commit and push
• Open a PR via github

[macropay-solutions]

@Tofandel thanks for the detailed response and sorry we ruled you out when we said "no one". The tests will definitely fail if that static is not false during the whole process if they were wrote to check it so, they need change (as most of the unit tests when the logic changes).

The best thing would be for the dev that implemented that static property to check this solution.

[marius-mcp]

Maybe this can help users that are in this edge case situation: https://github.com/cweagans/composer-patches that need to fix this issue.

[macropay-solutions]

We attempted a retroactive fix without patches for this issue here macropay-solutions/laravel-crud-wizard-free@e1a6899 that should work for Laravel/lumen >=8.

While doing it, we found 2 more places where Relation::noConstraints was used in the Eloquent Builder (out of which one would go on the old path/logic) and updated the above solution

We will begin testing now and we would appreciate if experienced people could have a look and also @allandantasdev . Thank you.

UPDATE.

Deployed the fix on our demo http://89.40.19.34/laravel-10-free/laravel-lumen-crud-wizard#operations

url query to be used in the text area of operations under Filter: not in URL of the webpage : withRelations[0]=productsValueScopeIssue51825&limit=10&page=1

UPDATE 2:
We tested also with relation alias

    public function products(): HasManyThrough
    {
        return $this->hasManyThrough(
            Product::class,
            OperationProductPivot::class,
            'operation_id',
            'id',
            'id',
            'product_id'
        );
    }

    public function productsValueScopeIssue51825(): HasManyThrough
    {
        return $this->products()->where(
            fn ($query) => $query->where('value', '>',  10)->when(
                true,
                fn($query) => $query->orWhereIn('id', Operation::query()->where('id', 2)->first()->children->pluck('id')->toArray())
                )
        );
    }
    
    public function productsValueScopeIssue51825Alias(): HasManyThrough
    {
        return $this->productsValueScopeIssue51825();
    }

[macropay-solutions]

No one (including both laravel community and users of laravel) seems to be interested in fixing this, not realizing that it is a possible data leak scenario... until that data is leaked and then there will be a big fuss about it...