Now, if you want the sum or find the maximum column value in the related model, you will have two database queries.
With this methods, it all turns into one query to the database and there is no need to load extra data.
It is also possible to sort by related models. And this sorting works with all types of relations.
I often use this in my work and I hope it will be useful to you!
If you liked this package, please give me a star.
Install via composer
composer require alexmg86/laravel-sub-query
Use LaravelSubQueryTrait trait in your model.
use Alexmg86\LaravelSubQuery\Traits\LaravelSubQueryTrait;
use Illuminate\Database\Eloquent\Model;
class Invoice extends Model
{
use LaravelSubQueryTrait;
If you want to get results from a relationship without actually loading them and by one request to the database you may use the these methods, which will place a new columns on your resulting models. For example:
$invoices = Invoice::withSum('items:price')
->withMin('items:price')
->withMax('items:price')
->withAvg('items:price')
->get();
echo $invoices[0]->items_price_sum;
echo $invoices[0]->items_price_min;
echo $invoices[0]->items_price_max;
echo $invoices[0]->items_price_avg;
You may add the sum for multiple relations as well as add constraints to the queries:
use Illuminate\Database\Eloquent\Builder;
$invoices = Invoice::withSum(['items:price', 'goods:price,price2' => function (Builder $query) {
$query->where('price','>',6);
}])->get();
echo $invoices[0]->items_price_sum;
echo $invoices[0]->goods_price_sum;
echo $invoices[0]->goods_price2_sum;
You may also alias the relationship sum result, allowing multiple sums on the same relationship:
use Illuminate\Database\Eloquent\Builder;
$invoices = Invoice::withSum(['items:price', 'goods:price as sum_goods_price' => function (Builder $query) {
$query->where('price','!=',1);
}])->get();
echo $invoices[0]->items_price_sum;
echo $invoices[0]->sum_goods_price;
If you're combining withSum
with a select
statement, ensure that you call withSum
after the select
method:
$invoices = Invoice::select(['id'])->withSum('items:price')->get();
echo $invoices[0]->id;
echo $invoices[0]->items_price_sum;
In addition, using the loadSum
method, you may load a relationship sum columns after the parent model has already been retrieved:
$invoice = Invoice::first();
$invoice->loadSum('items:price');
If you need to set additional query constraints on the eager loading query, you may pass an array keyed by the relationships you wish to load. The array values should be Closure instances which receive the query builder instance:
$invoice = Invoice::first();
$invoice->loadSum(['items:price' => function ($query) {
$query->where('price', '>', 5);
}]);
And of course it is all compatible with scopes in models.
If you want to sort by field in a related model, simply use the following method:
$invoices = Invoice::orderByRelation('items:price')->get();
or with conditions
$invoices = Invoice::orderByRelation(['items:price' => function (Builder $query) {
$query->where('price', '>', 6);
}, 'desc', 'max'])->get();
By default, sorting is by max
and desc
, you can choose one of the options max
, min
, sum
, avg
, desc
, acs
.
$invoices = Invoice::orderByRelation('items:price', 'asc', 'sum')->get();