Skip to content

Instantly share code, notes, and snippets.

@jsphpl jsphpl/NPerGroup.php forked from tureki/BaseModel.php
Last active Oct 26, 2018

Embed
What would you like to do?
laravel-pgsql-n-related-models-per-parent-with-scopes
<?php
namespace App\Models\Traits;
use DB;
/**
* This trait is to work around a problem where a limit on an Eloquent
* relation by default applies to all queried models in total.
*
* Assuming a `Post` model that hasMany `Comment`, where you want to
* side-load the 5 latest comments for each post, the naive approach
* would be to define the following relationships:
*
* class Post extends Model
* {
* public function comments() { return $this->hasMany('App\Models\Comment'); }
* public function latestComments() { return $this->comments()->latest()->limit(5); }
* }
*
* class Comment extends Model
* {
* public function scopeLatest($query) { return $query->orderBy('created_at', 'DESC'); }
* }
*
* This works fine when you query for a single `Post` at a time. However,
* when you query for more than one post, the limit will apply to
* the sum of all comments on all posts.
*
* Here's how to solve it using this trait:
*
* class Post extends Model
* {
* use NPerGroup;
*
* public function latestComments()
* {
* return $this->comments()
* ->nPerGroupWithScopes('post_id', 5, ['latest' => []]);
* }
* }
*
* This work is based on prior research and efforts that can be found here:
* - https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/
* - https://gist.github.com/tureki/7ea51872a9d9f734cc56
*
* Tested with HasMany relationships in Laravel 5.4 on a PSQL 9.6 database
*/
trait NPerGroup {
/**
* A query scope for Eloquent models that enables side-loading a relation with n records per parent.
*
* @param Builder $query
* @param string $group Name of the field on the related table to group by (usually the column with the foreign key)
* @param int $n Number of results to pick per group
* @param array $scopes Scopes to apply on the related table ['nameOfScope' => ['argument1', 'argument2', …]]
*
* @return void
*/
public function scopeNPerGroupWithScopes($query, $group, $n, $scopes = [])
{
$table = $this->getTable();
$pk = $this->getKeyName();
// Query the same model in a join using `over`, to assign row numbers starting at 1 for each group
$partitioned_query = $this->newQuery()
->addSelect($pk)
->addSelect(DB::raw("row_number() over (partition by {$group} order by {$this->primaryKey}) as rn"));
foreach ($scopes as $scope => $args) {
$partitioned_query->$scope(...$args);
}
$partitioned_sql = $partitioned_query->toSql();
$partitioned_bindings = $partitioned_query->getBindings();
$query
->join(DB::raw("( $partitioned_sql ) AS partitioned"), "$table.$pk", '=', "partitioned.$pk")
->where("partitioned.rn", '<=', $n);
$query->setBindings(array_merge_recursive($partitioned_bindings, $query->getBindings()));
}
}
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.