Framework: [Bug] Self-referencing HasManyThrough relationship

[JayBizzle]

• Laravel Version: 5.5.7 and before
• PHP Version: 7.1
• Database Driver & Version: MySQL 5.6.3

Description:

This is a really basic example, but here goes.

Say you have a users table and an orders table, and you have a feature where I can refer a friend to order a product, on the orders table you may have referrer_id column which stores the user_id of the user that initiated the referral.

Given that scenario, you could potentially have a relationship on the User model that looks like this

public function referrals()
{
    return $this->hasManyThrough(self::class, Order::class, 'referrer_id', 'id');
}

Then, you may do something like this to get all Users that have referred someone...

$users_that_have_referred = User::has('referrals')->get();

This will return incorrect results, it will just return all users.

The SQL generated looks like this...

select * from `users` where exists (select * from `users` inner join `orders` on `orders`.`id` = `users`.`id` where `users`.`id` = `orders`.`referrer_id`)

The problem with this is that the outer query users table conflicts with the inner query's users table.

To get that query to actually work, you would need to something like aliasing the outer table name like this....

select * from `users` as `users_outer` where exists (select * from `users` inner join `orders` on `orders`.`id` = `users`.`id` where `users_outer`.`id` = `orders`.`referrer_id`)

Steps To Reproduce:

I have setup a repo with this all setup ready for testing, it just dumps the results from the web.php route file.

git clone https://github.com/JayBizzle/HasManyThroughBug.git
cd HasManyThroughBug
composer install
php artisan key:generate
php artisan migrate
php artisan serve

[mikaldl]

Same problem over here with a different database structure.

When using a BelongsTo relation on itself, it doesn't alias the related table.

public function parent()
{
    return $this->belongsTo(self::class, 'entity_id');
}

When using the given relation on a table (id, entity_id, flag, slug, timestamps), in combination with this: $model->doesntHave('parent'), it returns an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'laravel_reserved_0.id' in 'where clause' (SQL: select * from `entities` where `flag` = leaflet_category and not exists (select * from `entities` where updated_at >= 1970-01-01 01:00:00 and `laravel_reserved_0`.`id` = `entities`.`entity_id` and `laravel_reserved_0`.`deleted_at` is null) and `entities`.`deleted_at` is null)

Looks like it's not giving the subquery the alias it needs to the table.

[JayBizzle]

I've updated the sample repo to make the issue a little more obvious (see above for details on how to replicate the issue)

https://github.com/JayBizzle/HasManyThroughBug

@themsaid Would like the hear your thoughts on this? Either just to know if it is possible to fix, or if it is a no-fix issue.

Thanks

[themsaid]

Not quite sure but I think it's worth an attempt, feel free to open a PR with the idea of aliasing when you get the chance.

[JayBizzle]

Thanks! I'll try take a look when I get the chance to sit down and get my head around it. Unfortunately, the query builder is one of my weakest Laravel knowledge areas.

[JayBizzle]

Gave myself a quick crash course in the query builder and I seem to have fixed this issue. Will take me a couple of days to tidy it up, test it properly and put together a proper PR...watch this space 👍

[JayBizzle]

PR submitted #21792

[JayBizzle]

Fixed in #21883