Aliasing on with() for eager loading, like withCount

[erikn69]

For withCount() method, we can happily define an alias to count on the same relationship multiple times in different context. Example from the official documentation:

$posts = Post::withCount([
    'comments', 'comments as pending_comments_count' 
])->get();

Create pseudo relationship that can be eager loaded, example:

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

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

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

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. As withCount set a alias, with must replay the same behaivor.

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

Lines 368 to 378 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]];
	}

---

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::select()
            ->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.

[mateusjunges]

Check it out: #39067

[PaolaRuby]

Unfortunately, closed with no reason and with the same copy/paste answer like others