Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Additional helper scopes for Laravel Eloquent Models: `->orderByRelation('author', 'name')`; `->orderByRelationCount('posts')`; `->withJoinnedRelated('author', 'name')`;
<?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;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.