Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
laravel-pgsql-n-related-models-per-parent
<?php
/**
* MYSQL Reference: http://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/
* PGSQL Reference: http://stackoverflow.com/questions/21154752/postgresql-limit-for-each-in-clause
*/
class BaseModel extends \Eloquent {
/**
* query scope nPerGroup
*
* @return void
*/
public function scopeNPerGroup($query, $group, $n = 10)
{
$table = ($this->getTable());
if (!$query->getQuery()->columns) {
$query->select("{$table}.*");
}
$sub_query = \DB::table($table)->select($query->getQuery()->columns);
$sub_query->addSelect(\DB::raw(
"row_number() over (partition by {$group} order by {$this->primaryKey}) as rn"
));
$query
->from(\DB::raw("({$sub_query->toSql()}) as {$table}"))
->mergeBindings($sub_query)
->where("rn", '<=', $n);
$query->getQuery()->orders = (array) $query->getQuery()->orders;
array_unshift($query->getQuery()->orders, ['column' => $group, 'direction' => 'asc']);
}
}
<?php
class Post extends BaseModel {
/**
* Get latest 5 comments from hasMany relation.
*
* @return Illuminate\Database\Eloquent\Relations\HasMany
*/
public function latestComments()
{
return $this->comments()->latest()->nPerGroup('post_id', 5);
}
/**
* Post has many Comments
*
* @return Illuminate\Database\Eloquent\Relations\HasMany
*/
public function comments()
{
return $this->hasMany('Comment');
}
}
@ariews

This comment has been minimized.

Copy link

commented Oct 28, 2015

Hello, how can we call like this:

Post::with(['latestComments' => function($q){
    $q->where('comment_status', 'approved');
}])->get();
@jsphpl

This comment has been minimized.

Copy link

commented Jul 28, 2017

Here's a fork that makes it work with (filtering) scopes: https://gist.github.com/jsphpl/25f2a295938a8497c6cdcf1160ef7507

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.