Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Enhanced version of, tested on hasMany and belongsToMany relationships
namespace App\Traits;
trait NPerGroup {
* query scope nPerGroup
* @return void
public function scopeNPerGroup($query, $relatedTable = NULL, $group, $n = 10) {
// queried table
$table = ($this->getTable());
$newQuery = $this->newQueryWithoutScopes();
// initialize MySQL variables inline
$newQuery->from(\DB::raw("(select @num:=0, @group:=0) as vars, {$table}"));
$groupTable = $relatedTable ?: $table;
// if no columns already selected, let's select *
if (!$query->getQuery()->columns) {
// make sure column aliases are unique
$groupAlias = "{$table}_grp";//. md5(time());
$numAlias = "{$table}_rn";// . md5(time());
// apply mysql variables
"@num := if(@group = {$groupTable}.{$group}, @num+1, 1) as {$numAlias}, @group := {$groupTable}.{$group} as {$groupAlias}"
// make sure first order clause is the group order
$newQuery->getQuery()->orders = (array) $query->getQuery()->orders;
array_unshift($newQuery->getQuery()->orders, [
'column' => "{$groupTable}.{$group}",
'direction' => 'asc'
if ($relatedTable) {
$newQuery->getQuery()->joins = (array) $query->getQuery()->joins;
$query->whereRaw("{$table}.{$group} = {$groupTable}.{$group}");
// prepare subquery
$subQuery = $query->toSql();
$query->from(\DB::raw("({$newQuery->toSql()}) as {$table}"))
->where($numAlias, '<=', $n);

This comment has been minimized.

Copy link

commented Sep 6, 2017

Hi @juavidn your code is really helpful, I've modified a little bit to suite with my pattern, I don't want to provide $relatedTable and $group, I get $relatedTable and $group from $query->getQuery()->wheres[0]['column'], here my code,

do you think it will have a problem in the future.


This comment has been minimized.

Copy link

commented Oct 26, 2017

@juavidn @kyawkyawsoezhu both implementations have one big problem, generated query selects ALL records from the pivot (relations) table. You can easily check it via EXPLAIN {$generatedSelect} 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.