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.
     * :FIXME: check query->column before reset select.
     *
     * @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: для связей hasMany и belongsToMany
     *
     * @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;
    }
}