'whereHas' using wrong table name when relationship refers to self

[tiagof]

• Laravel Version: 9.1.0
• PHP Version: 8.1
• Database Driver & Version: Mysql

Description:

When using whereHas on a Model that implements SoftDeletes and has a relationship to itself, the generated query is wrong.

Steps To Reproduce:

Create 2 models like this

class Client extends Model {
  use SoftDeletes;
}

class Contract extends Model {
  use SoftDeletes;

  public function relationSelf(): HasMany
  {
      return $this->hasMany(static::class)->onlyTrashed();
  }

  public function relationOther(): HasMany
   {
      return $this->hasMany(Client::class)->onlyTrashed();
   }
}

➜ art --version
Laravel Framework 9.1.0

➜ art tinker
Psy Shell v0.11.1 (PHP 8.1.4 — cli) by Justin Hileman

>>> Contract::whereHas('relationSelf')->toSql();
[!] Aliasing 'Contract' to 'Domains\Contracts\Models\Contract' for this Tinker session.
=> "select * from `contracts` where exists (select * from `contracts` as `laravel_reserved_0` where `contracts`.`id` = `laravel_reserved_0`.`contract_id` and `contracts`.`deleted_at` is not null) and `contracts`.`deleted_at` is null"

>>> Contract::whereHas('relationOther')->toSql();
=> "select * from `contracts` where exists (select * from `clients` where `contracts`.`id` = `clients`.`contract_id` and `clients`.`deleted_at` is not null) and `contracts`.`deleted_at` is null"
>>>

Notice that in the first instruction, we have contracts.deleted_at is not null when I believe we should get laravel_reserved_0.deleted_at is not null.
The complete correct sql query would be
select * from `contracts` where exists (select * from `contracts` as `laravel_reserved_0` where `contracts`.`id` = `laravel_reserved_0`.`contract_id` and `laravel_reserved_0`.`deleted_at` is not null) and `contracts`.`deleted_at` is null

In the second instruction, because this relationship refers to another Model, the query is generated correctly.

[driesvints]

Can you try the very latest Laravel version? This is already fixed I think.

[tiagof]

Don't think it is ...

site on  upg/laravel9 [!] via ⬢ v17.7.2 via 🐘 v8.1.4 took 11s
➜ art --version
Laravel Framework 9.9.0

site on  upg/laravel9 [!] via ⬢ v17.7.2 via 🐘 v8.1.4
➜ art tinker
Psy Shell v0.11.2 (PHP 8.1.4 — cli) by Justin Hileman
>>> Contract::whereHas('relationSelf')->toSql();
[!] Aliasing 'Contract' to 'Domains\Contracts\Models\Contract' for this Tinker session.
=> "select * from `contracts` where exists (select * from `contracts` as `laravel_reserved_0` where `contracts`.`id` = `laravel_reserved_0`.`contract_id` and `contracts`.`deleted_at` is not null) and `contracts`.`deleted_at` is null"
>>> Contract::whereHas('relationOther')->toSql();
=> "select * from `contracts` where exists (select * from `clients` where `contracts`.`id` = `clients`.`contract_id` and `clients`.`deleted_at` is not null) and `contracts`.`deleted_at` is null"
>>>

EDIT:

At some point in the query generation it is correct

but then the mergeConstraintsFrom function messes it up:

[driesvints]

Does it work if you do $this->hasMany(self::class)->onlyTrashed();?

[tiagof]

Same result

[driesvints]

Heya, thanks for reporting.

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 separate commits 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"

Please 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!

[tiagof]

Sure, i'll do that, but again, the 2 classes in here
are enough.

[tiagof]

Here it is: https://github.com/tiagof/bug-report

[driesvints]

@tiagof can you add a test for me to run to reproduce the output?

[tiagof]

Added.
(testing the output of the toSql() method, otherwise would need to create migrations and populate the DB)

EDIT: art test --filter BugTest

[taylorotwell]

It's because SoftDeletingScope simply isn't built to handle self-referencing relationships and aliases. The framework would need a PR to enable this behavior.

It's basically same situation as:

return $this->hasMany(static::class)->where('foo', 'bar');

This also generates the "wrong query" with whereHas + self-referential relations. ^

[tiagof]

I see. The relationship itself works fine:
Contract::find(1)->relationSelf
returns the expected related models.

It happens as you say, only when alias are involved.

I'll see if I can find a fix but the internals of Eloquent don't look so easy :)

[driesvints]

Seems like this indeed isn't supported right now. Like Taylor said, we'd appreciate PR's for this. Thanks

[tiagof]

@driesvints: PR submitted. #42100
Maybe there is a better way to achieve this, but this one seems to work.

Hope you can review it