Skip to content

Instantly share code, notes, and snippets.

@kyawkyawsoezhu
Last active May 25, 2020 11:44
Show Gist options
  • Select an option

  • Save kyawkyawsoezhu/2976b0c26299fec0a620a6f8ade7ce45 to your computer and use it in GitHub Desktop.

Select an option

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
Copy Markdown

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