Relations

Usage

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;

The following methods apply to all methods!!!

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.

Sorting

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();

Load latest or oldest relation

Imagine you want to get a list of 50 accounts, each with 100 items. By default, you will get 5000 positions and select the first ones for each account. PHP smokes nervously on the sidelines.
Wow! Now you can load only one latest or oldest related model:

$invoices = Invoice::all();
$invoices->loadOneLatest('items');
$invoices->loadOneOldest('items');

or with conditions

$invoices->loadOneLatest(['items' => function ($query) {
    $query->orderBy('id', 'desc')->where('price', '<', 6);
}]);

You can use this with relation types hasMany, belongsToMany and hasManyThrough.

Working with columns

To add or multiply the required columns use this method:

$items = Item::withMath(['invoice_id', 'price'])->get();
echo $items[0]->sum_invoice_id_price;

Columns will be summed by default, you can choose one of the options +, -, *, / and set a new name.

$items = Item::withMath(['invoice_id', 'price', 'price2'], '*', 'new_column')->get();
echo $items[0]->new_column;

Limit relations

If you want to load related model with limit, simply use the following method:

$invoices = Invoice::all();
$invoices->loadLimit('items:1');

or with conditions

$invoices->loadLimit(['items:2', 'goods:1' => function ($query) {
    $query->orderBy('id', 'desc')->where('price', '<', 6);
}]);

Note that first you write the name of the relation, and then the number of rows.