Self-referencing table HasManyThrough relationship exception

[PaolaRuby]

• Laravel Version: 8.61.0
• PHP Version: 8.0.5
• Database Driver & Version: MariaDB 10.5

Description:

This is a really basic examples, but here goes.
Say you have a company table, it have categories table, and categories have self referencing(only two levels tree, it's a example), and you want main categories list

class Company extends Model{
     public function main_categories(){
        return $this->hasManyThrough(Category::class, Category::class, 'comp_id', 'cate_id', 'comp_id', 'cate_recu');
    }
    // Company::with('main_categories')->get() give exception
}

This give me a exception because it is not using alias for through model

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'categories '
(SQL: select `categories`.*, `categories`.`comp_id` as `laravel_through_key` from `categories`
inner join `categories` on `categories`.`cate_recu` = `categories`.`cate_id` 
where `categories`.`comp_id` in (1) and `categories`.`deleted_at` is null and `categories`.`deleted_at` is null)

Maybe must be like

select `categories`.*, `laravel_through_model`.`comp_id` as `laravel_through_key` from `categories`
inner join `categories` as `laravel_through_model` on `laravel_through_model`.`cate_recu` = `categories`.`cate_id` 
where `laravel_through_model`.`comp_id` in (1) 
and `categories`.`deleted_at` is null and `laravel_through_model`.`deleted_at` is null

[hafezdivandari]

I had a similar issue before (staudenmeir/eloquent-has-many-deep#170), when using whereHas on self-related relations, the id column would be ambiguous. Using $query->qualifyColumn() helped as a workaround, assume teammates is a self-related relation on user model:

\App\Models\User::whereHas('teammates', function (Builder $query) {
    $query->where($query->qualifyColumn('id'), 2);
})->get();

I think these 2 issues are related somehow and we should have better column quilifaction on self-related relations.