Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Enhanced version of https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/, tested on hasMany and belongsToMany relationships
<?php
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) {
$newQuery->select("{$table}.*");
}
// make sure column aliases are unique
$groupAlias = "{$table}_grp";//. md5(time());
$numAlias = "{$table}_rn";// . md5(time());
// apply mysql variables
$newQuery->addSelect(\DB::raw(
"@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->addSelect("{$groupTable}.{$group}");
$newQuery->mergeBindings($query->getQuery());
$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);
}
}
@kyawkyawsoezhu

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, https://gist.github.com/kyawkyawsoezhu/2976b0c26299fec0a620a6f8ade7ce45#file-limitpergroupable-php-L13-L14

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

@akalongman

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.