laravel-pgsql-n-related-models-per-parent

BaseModel.php

<?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']);

    }
}

Post.php

<?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');
    }
}

Hello, how can we call like this:

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

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