Additional helper scopes for Laravel Eloquent Models: `->orderByRelation('author', 'name')`; `->orderByRelationCount('posts')`; `->withJoinnedRelated('author', 'name')`;

BaseModel.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Query\Expression;

abstract class BaseModel extends Model
{
    /**
     * Сортировка выборки по полю из связанной модели.
     *
     * :WARNING: only for hasOne relation.
     *
     * @param Builder|QueryBuilder $query
     * @param string $relation
     * @param string|string[] $column
     * @param string $direction
     * @return Builder|QueryBuilder
     */
    public function scopeOrderByRelation($query, $relation, $column, $direction = 'asc')
    {
        if (null === $query->getQuery()->columns) {
            $query->select([$this->getTable() . '.*']);
        }

        $relation = $query->getRelation($relation);
        $related_table = $relation->getRelated()->getTable();

        //for laravel "< 5.4"
        //$query->leftJoin($related_table, $relation->getForeignKey(), '=', $relation->getQualifiedParentKeyName());
        $query->leftJoin($related_table, $relation->getQualifiedForeignKeyName(), '=', $relation->getQualifiedParentKeyName());

        foreach ((array) $column as $order) {
            $query->orderBy($related_table . '.' . $order, $direction);
        }

        return $query;
    }

    /**
     * Сортировка выборки по кол-ву связанных записей.
     *
     * :INFO: for hasMany and belongsToMany relations
     *
     * @param Builder|QueryBuilder $query
     * @param string $relation
     * @param string $direction
     * @return Builder|QueryBuilder
     */
    public function scopeOrderByRelationCount($query, $relation, $direction = 'asc')
    {
        return $query->withCount($relation)->orderBy(snake_case($relation) . '_count', $direction);
    }

    /**
     * Выборка полей из связанной модели с помощью джоина.
     *
     * :WARNING: only for hasOne relation.
     *
     * @param Builder|QueryBuilder $query
     * @param string $relation
     * @param array|string $column
     * @return Builder|QueryBuilder
     */
    public function scopeWithJoinedRelated($query, $relation, $column)
    {
        if (null === $query->getQuery()->columns) {
            $query->select([$this->getTable() . '.*']);
        }

        $relation_name = snake_case($relation);
        $relation = $query->getRelation($relation);
        $related_table = $relation->getRelated()->getTable();

        //for laravel "< 5.4"
        //$query->leftJoin($related_table, $relation->getForeignKey(), '=', $relation->getQualifiedParentKeyName());
        $query->leftJoin($related_table, $relation->getQualifiedForeignKeyName(), '=', $relation->getQualifiedParentKeyName());

        foreach ((array) $column as $name) {
            $segments = explode(' ', $name);

            $alias = "{$relation_name}_{$name}";

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

            $query->addSelect("{$related_table}.{$name} as {$alias}");
        }

        return $query;
    }
    
    /**
     * Аналогично (с ограничениями) withCount только находит сумму связанного атрибута.
     */
    public function scopeWithSum(Builder $query, $relation, $attribute, $constraints = null)
    {
        if (is_null($query->getQuery()->columns)) {
            $query->getQuery()->select([$query->getQuery()->from.'.*']);
        }

        $name = $relation;

        $relation = Relation::noConstraints(function () use ($relation) {
            return $this->{$relation}();
        });

        $subQuery = $relation->getRelationExistenceQuery(
            $relation->getRelated()->newQuery(), $query, new Expression("sum({$attribute})")
        )->setBindings([], 'select');

        $subQuery = $subQuery->tap($constraints ?? function (){})->mergeConstraintsFrom($relation->getQuery())->toBase();

        if (count($subQuery->columns) > 1) {
            $subQuery->columns = [$subQuery->columns[0]];
        }

        $column = Str::snake(sprintf('%s_%s_sum', $name, $attribute));

        $query->selectSub($subQuery, $column);
    }
    
    
    /**
     * Сортировка выборки по сумме связанных записей.
     *
     * :INFO: for hasMany and belongsToMany relations
     *
     * @param Builder|QueryBuilder $query
     * @param string $relation
     * @param string $direction
     * @return Builder|QueryBuilder
     */
    public function scopeOrderByRelationSum($query, $relation, $attribute, $direction = 'asc')
    {
        $column = Str::snake(sprintf('%s_%s_sum', $relation, $attribute));
        return $query->withSum($relation, $attribute)->orderBy($column, $direction);
    }
    
    /**
     * Находит последнюю запись для значений определенного поля.
     *
     * Т.е. с групировкой по указанному полю.
     *
     * @param Builder|QueryBuilder $query
     * @param string $column
     * @param array $values
     * @param string $date
     * @return Builder|QueryBuilder
     */
    public function scopeLastFor($query, $column, array $values = [], $date = 'created_at', $mergeWheres = true)
    {
        if (!empty($values)) {
            $query->whereIn($column, $values);
        }

        $sub_query = static::select("{$column} AS __{$column}")->selectRaw("MAX({$date}) as __{$date}")->groupBy($column);

        if ($mergeWheres) {
            $sub_query->mergeWheres($query->getQuery()->wheres, $query->getQuery()->getBindings());
            $query->getQuery()->mergeBindings($sub_query->getQuery());
        }

        $query->join(new Expression("({$sub_query->toSql()}) as last_for_t"), function ($join) use ($column, $date) {
            $join->on("last_for_t.__{$column}", '=', $column)
                ->on("last_for_t.__{$date}", '=', $date);
        });

        return $query;
    }
}