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

NPerGroup.php

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

@rela589n: this is supposed to only work with Postgres. Looks like you're using MySQL/MariaDB