Skip to content

Instantly share code, notes, and snippets.

@kyawkyawsoezhu
Last active May 25, 2020 11:44
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save kyawkyawsoezhu/2976b0c26299fec0a620a6f8ade7ce45 to your computer and use it in GitHub Desktop.
Save kyawkyawsoezhu/2976b0c26299fec0a620a6f8ade7ce45 to your computer and use it in GitHub Desktop.
<?php
namespace App\Traits;
use DB;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\JoinClause;
use phpDocumentor\Reflection\Types\Mixed_;
trait LimitPerGroupable
{
/**
* @param Builder $query
* @param int $limit
* @return mixed
*/
public function scopeLimitPerGroup(Builder $query, $limit = 15)
{
$judgement = !$query->getQuery()->wheres ?: $query->getQuery()->wheres[0];
// check if query is actual eager load
if (!$judgement ||
(!strpos($judgement['column'], '.') && !isset($judgement['values']))
) {
$query->limit((int)$limit);
$query->getModel()->setPerPage((int)$limit);
return $query;
}
/** @var JoinClause $join */
$join = $query->getQuery()->joins[0];
$table = $this->getTable();
$eagerLoadFullColumn = $judgement['column'];
$bindings = $query->getBindings();
// make sure column aliases are unique
$numAlias = "{$table}_rn_" . uniqid();
$groupAlias = "{$table}_grp_" . uniqid();
/** @var Builder $newBaseQuery */
$newBaseQuery = $this->newQueryWithoutScopes()->getQuery();
$subQuery = clone $query;
$subQuery->select("{$table}.*");
// initialize MySQL variables inline
$subQuery->from(DB::raw("(select @num:=0, @group:=0) as vars, {$table}"));
// apply mysql variables
$subQuery->selectRaw("@num := if(@group = {$eagerLoadFullColumn}, @num + 1, 1) AS {$numAlias}");
$subQuery->selectRaw("@group := {$eagerLoadFullColumn} AS {$groupAlias}");
// make sure first order clause is the group order
$subQuery->getQuery()->orders = (array)$subQuery->getQuery()->orders;
array_unshift($subQuery->getQuery()->orders, [
'column' => $eagerLoadFullColumn,
'direction' => 'asc'
]);
// for many to many table relationship
if ($join) {
$subQuery->addSelect($eagerLoadFullColumn);
$newBaseQuery->joins = $subQuery->getQuery()->joins;
$eagerLoadColumn = explode('.', $eagerLoadFullColumn)[1];
$inspectColumn = "{$table}.{$eagerLoadColumn}";
$newBaseQuery->whereRaw("{$inspectColumn} = {$eagerLoadFullColumn}");
}
$query->setQuery($newBaseQuery);
$query->from(DB::raw("({$subQuery->toSql()}) as {$table}"));
$query->setBindings($bindings);
$query->where($numAlias, '<=', (int)$limit);
}
}
@rela589n
Copy link

Could you explain how to use this scope?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment