[8.x] Eager Loaded Pseudo Relationships #37656

browner12 · 2021-06-10T23:30:18Z

TLDR

Create pseudo relationship that can be eager loaded:

$salesperson = Salesperson::with(['leads as openLeads' => fn($query) => $query->where('status', 'open')])
    ->with(['leads as completedLeads' => fn($query) => $query->where('status', 'completed')])
    ->with(['leads as completedLeadsWithSale' => fn($query) => $query->where('status', 'completed')->where('result', 'sale')])
    ->with(['leads as completedLeadsWithNoSale' => fn($query) => $query->where('status', 'completed')->where('result', 'no sale')])
    ->first();

$salesperson->openLeads;
$salesperson->completedLeads;
$salesperson->completedLeadsWithSale;
$salesperson->completedLeadsWithNoSale;

Current Scenario

Imagine you have a Salesperson with many Leads. Leads have a status of "open" or "completed", and a result of "sale" or "no sale".

You are trying to build a dashboard that shows the Salesperson's metrics.

Salesperson	Open	Completed	No Sale	Sale	Close Rate
Taylor	15	20	15	5	25%
Mohammed	7	6	1	5	83%
Dries	12	15	5	10	66%

It is a simple 1:many relationship between the models.

class Salesperson extends Model
{
    public function leads()
    {
        return $this->hasMany(Lead::class);
    }
}

In your controller you fetch all the Salespersons.

class SalespersonController
{
    public function index()
    {
        $salespersons = Salesperson::with('leads')->get();
    }
}

In your view you loop through the Salespersons.

@foreach($salespersons as $salesperson)
    <tr>
        <td>{{ $salesperson->name }}</td>
        <td>{{ count($salesperson->leads()->where('status', 'open')->get()) }}</td>
        <td>{{ count($salesperson->leads()->where('status', 'completed')->get()) }}</td>
        <td>{{ count($salesperson->leads()->where('status', 'completed')->where('result', 'no sale')->get()) }}</td>
        <td>{{ count($salesperson->leads()->where('status', 'completed')->where('result', 'sale')->get()) }}</td>
        <td>{{ count($salesperson->leads()->where('status', 'completed')->where('result', 'sale')->get()) / count($salesperson->leads()->where('status', 'completed')->get()) }}</td>
    </tr>
@endforeach

Because we are calling ->leads() as a method and not a property (in order to tack on our additional conditions), this will result in new queries being run for each of these groupings, and we don't benefit from the eager loading we did in the controller.

There are 2 ways we could currently handle this. We could create additional constrained relationships in the Model:

class Salesperson extends Model
{
    public function leads()
    {
        return $this->hasMany(Lead::class);
    }

    public function openLeads()
    {
        return $this->hasMany(Lead::class)->where('status', 'open');
    }

    public function completedLeads()
    {
        return $this->hasMany(Lead::class)->where('status', 'completed');
    }
    
    public function completedLeadsWithSale()
    {
        return $this->hasMany(Lead::class)->where('status', 'completed')->where('result', 'sale');
    }
    
    public function completedLeadsWithNoSale()
    {
        return $this->hasMany(Lead::class)->where('status', 'completed')->where('result', 'no sale');
    }
}

and then eager load these in the Controller:

class SalespersonController
{
    public function index()
    {
        $salespersons = Salesperson::with('leads')
            ->with('openLeads')
            ->with('completedLeads')
            ->with('completedLeadsWithSale')
            ->with('completedLeadsWithNoSale')
            ->get();
    }
}

This gets rid of our N+1, but the problem with this is it quickly becomes very verbose and unmanageable.

The other option is to filter in PHP (rather than the query) using the returned Collections. In our controller we revert to our simple eager load on the leads relationship:

class SalespersonController
{
    public function index()
    {
        $salespersons = Salesperson::with('leads')->get();
    }
}

and now in our view we use Collection methods to filter out our desired data:

@foreach($salespersons as $salesperson)
    <tr>
        <td>{{ $salesperson->name }}</td>
        <td>{{ count($salesperson->leads->where('status', 'open')) }}</td>
        <td>{{ count($salesperson->leads->where('status', 'completed')) }}</td>
        <td>{{ count($salesperson->leads->where('status', 'completed')->where('result', 'no sale')) }}</td>
        <td>{{ count($salesperson->leads->where('status', 'completed')->where('result', 'sale')) }}</td>
        <td>{{ count($salesperson->leads->where('status', 'completed')->where('result', 'sale')) / count($salesperson->leads()->where('status', 'completed')) }}</td>
    </tr>
@endforeach

This option benefits from the eager loading, so our query count is very low. However, there is a lot of "logic" in the view, which some people do not like. We also duplicate 2 conditions in our "Close Rate" column that we've already determined. Finally, this does offload the filtering and sorting to PHP rather than the database, which could be undesirable in some scenarios, possibly for performance reasons.

Proposed Improvement

This PR allows us to define eager loaded pseudo relationships. The benefit to this is it avoids our N+1 issues, keeps the filtering and sorting in the database, and keeps the majority of the logic out of the view.

class SalespersonController
{
    public function index()
    {
        $salespersons = Salesperson::with(['leads as openLeads' => fn($query) => $query->where('status', 'open')])
            ->with(['leads as completedLeads' => fn($query) => $query->where('status', 'completed')])
            ->with(['leads as completedLeadsWithSale' => fn($query) => $query->where('status', 'completed')->where('result', 'sale')])
            ->with(['leads as completedLeadsWithNoSale' => fn($query) => $query->where('status', 'completed')->where('result', 'no sale')])
            ->get();
    }
}

These "relationships" are then available on the models in the view:

@foreach($salespersons as $salesperson)
    <tr>
        <td>{{ $salesperson->name }}</td>
        <td>{{ count($salesperson->openLeads) }}</td>
        <td>{{ count($salesperson->completedLeads) }}</td>
        <td>{{ count($salesperson->completedLeadsWithNoSale) }}</td>
        <td>{{ count($salesperson->completedLeadsWithSale) }}</td>
        <td>{{ count($salesperson->completedLeadsWithSale) / count($salesperson->completedLeads) }}</td>
    </tr>
@endforeach

Thanks!

this change allows you to alias and eager load relationships with additional constraints. ```php $posts = \App\Post::with(['comments as activeComments', function($query) { return $query->where('status', 'active'); }])->get(); ```

taylorotwell · 2021-06-11T13:34:45Z

In your examples you pass an array to with that has two elements... a string with a relationship and an alias and a second value that is a closure. Is that correct? Is it not supposed to be like:

::with(['leads as openLeads' => fn ($query) ... ])

browner12 · 2021-06-11T13:42:04Z

You are correct. Tried coding freehand in the markdown, clearly not a good idea.

Comment has been updated.

fredsal · 2021-06-22T22:14:03Z

@taylorotwell Maybe a duplicate of PR #31976, i really need this, it´s like withCount(['leads as leads_sum' => fn ($query) ... ])

If i use AS instead of as works the same like the other methods? Not, right

framework/src/Illuminate/Database/Eloquent/Concerns/QueriesRelationships.php

Lines 375 to 381 in 11387ec

    
           $segments = explode(' ', $name); 
        
           unset($alias); 
        
           if (count($segments) === 3 && Str::lower($segments[1]) === 'as') { 
        
               [$name, $alias] = [$segments[0], $segments[2]]; 
        
           }

fredsal · 2021-06-22T22:51:26Z

Works on nested eager loading with('author.contacts as contacts_data')?
Like this branch
https://github.com/CrissiDev/framework/blob/f8004c930bf64055e9687c85356195e0e04304d3/tests/Database/DatabaseEloquentIntegrationTest.php#L841-L857

PaolaRuby · 2021-06-28T20:16:46Z

Can you take a look on this?

framework/src/Illuminate/Database/Eloquent/Concerns/QueriesRelationships.php

Lines 368 to 379 in 09feffb

    
           foreach ($this->parseWithRelations($relations) as $name => $constraints) { 
        
               // First we will determine if the name has been aliased using an "as" clause on the name 
        
               // and if it has we will extract the actual relationship name and the desired name of 
        
               // the resulting column. This allows multiple counts on the same relationship name. 
        
               $segments = explode(' ', $name); 
        
               unset($alias); 
        
               if (count($segments) === 3 && Str::lower($segments[1]) === 'as') { 
        
                   [$name, $alias] = [$segments[0], $segments[2]]; 
        
               }

donnysim · 2021-06-29T05:17:09Z

We should not forget about

->with(['leads:id,name'])

as in will this:

->with(['leads:id,name as completedLeads'])

still work?

Also, I don't know how many in the community know, but:

->with(['leads:id,name as other_name,tilte'])

is also a thing.

taylorotwell · 2021-06-29T13:33:22Z

Thanks for your pull request to Laravel!

Unfortunately, I'm going to delay merging this code for now. To preserve our ability to adequately maintain the framework, we need to be very careful regarding the amount of code we include.

If possible, please consider releasing your code as a package so that the community can still take advantage of your contributions!

If you feel absolutely certain that this code corrects a bug in the framework, please "@" mention me in a follow-up comment with further explanation so that GitHub will send me a notification of your response.

PaolaRuby · 2021-06-29T14:47:08Z

@taylorotwell @donnysim already works with leads:id,name as autor

public function testBasicEagerLoadingWithAlias()
{
    $user = EloquentTestUser::create(['email' => '[email protected]']);
    $user->posts()->create(['name' => 'First Post']);
    $user->posts()->create(['name' => 'Second Post']);
    $user = EloquentTestUser::with([
        'posts:id,user_id,name AS autor',
        'posts AS posts1:id,user_id,name AS autor,parent_id',
        'posts AS posts2' => function ($q) {
            $q->where('name', 'Second Post');
        },
    ])->where('email', '[email protected]')->first();
    $this->assertSame('First Post', $user->posts->first()->autor);
    $this->assertSame('First Post', $user->posts1->first()->autor);
    $this->assertSame('Second Post', $user->posts2->first()->name);
}

.\vendor\bin\phpunit --filter=testBasicEagerLoadingWithAlias

PHPUnit 9.5.6 by Sebastian Bergmann and contributors.

Runtime:       PHP 8.0.5
Configuration: laravel/phpunit.xml.dist

.                                                                   1 / 1 (100%)

Time: 00:00.088, Memory: 126.00 MB

OK (1 test, 3 assertions)

Lemmings19 · 2021-07-02T00:13:53Z

Different PR for the same feature a little over a year ago, same outcome/response. :(

#31976 (comment)

OzanKurt · 2022-09-28T11:32:48Z

Did anybody care to extract this funtionality to a package yet?

angeljqv · 2022-09-28T13:32:37Z

@OzanKurt no, feel free to make it possible

browner12 added 3 commits Jun 10, 2021

add aliased eager loads

ceafbd0

this change allows you to alias and eager load relationships with additional constraints. ```php $posts = \App\Post::with(['comments as activeComments', function($query) { return $query->where('status', 'active'); }])->get(); ```

minor styling

d3bb6d0

minor formatting

071e4c0

crissi mentioned this pull request Jun 22, 2021

Alias for eager loading laravel/ideas#2183

Open

fredsal mentioned this pull request Jun 22, 2021

[Proposal] Aliasing for with() laravel/ideas#1396

Open

browner12 mentioned this pull request Jun 25, 2021

[8.x] Eager Loaded Pseudo Relationships #37830

Closed

taylorotwell closed this Jun 29, 2021

Sep	OCT	Nov
	12
2021	2022	2023

[8.x] Eager Loaded Pseudo Relationships #37656

[8.x] Eager Loaded Pseudo Relationships #37656

browner12 commented Jun 10, 2021 •

edited

taylorotwell commented Jun 11, 2021

browner12 commented Jun 11, 2021

fredsal commented Jun 22, 2021 •

edited

fredsal commented Jun 22, 2021

PaolaRuby commented Jun 28, 2021

donnysim commented Jun 29, 2021 •

edited

taylorotwell commented Jun 29, 2021

PaolaRuby commented Jun 29, 2021

Lemmings19 commented Jul 2, 2021

OzanKurt commented Sep 28, 2022

angeljqv commented Sep 28, 2022

[8.x] Eager Loaded Pseudo Relationships #37656

[8.x] Eager Loaded Pseudo Relationships #37656

Conversation

browner12 commented Jun 10, 2021 • edited

TLDR

Current Scenario

Proposed Improvement

taylorotwell commented Jun 11, 2021

browner12 commented Jun 11, 2021

fredsal commented Jun 22, 2021 • edited

fredsal commented Jun 22, 2021

PaolaRuby commented Jun 28, 2021

donnysim commented Jun 29, 2021 • edited

taylorotwell commented Jun 29, 2021

PaolaRuby commented Jun 29, 2021

Lemmings19 commented Jul 2, 2021

OzanKurt commented Sep 28, 2022

angeljqv commented Sep 28, 2022

browner12 commented Jun 10, 2021 •

edited

fredsal commented Jun 22, 2021 •

edited

donnysim commented Jun 29, 2021 •

edited