CODEWITHSUNDEEP
phplaraveleloquent
derdida
derdida

Reputation: 14904

Laravel Subselect with own table?

I have the following query:

    $users = User::join('countries', 'countries.id', '=', 'users.country_id')->select([
        'users.id',
        'users.promoter_id',
        'users.family_name',
        'users.name',
         ...

The users.promoter_id is still an ID from the users table. I want to add the family_name and the name for the promoter in that query too.

How can i make that subquery inside my OWN table?

Upvotes: 0

Views: 59

Answers (1)

Mahfuzul Alam
Mahfuzul Alam

Reputation: 3157

Self-joining scenario. You can do it by creating an alias of your Users table and then joining with Users.

$users = User::join('countries', 'countries.id', '=', 'users.country_id')
                ->leftJoin('users as promoters', 'promoters.id', '=', 'users.promoter_id')->select([
        'users.id',
        'users.promoter_id',
        'users.family_name',
        'users.name',
        'promoters.family_name as promoter_family_name',
        'promoters.name as promoter_name',
        ...

Another way, in your User model define a relation

public function promoter()
{
    return $this->belongsTo(User::class, 'promoter_id', 'id');
}

Now Rewrite your query in controller

$users = User::with('promoter')->join('countries', 'countries.id', '=', 'users.country_id')->select([
         ...

Now you can access promoter details by $user->promoter->name, $user->promoter->family_name and so on.

Also you can do the same for Country too, by defining a country relationship instead of join in your User model and then User::with('promoter', 'country')->...

Upvotes: 3

Related Questions