Limit related records in polymorphic many-to-many relationship with Laravel

Here's a "half answer". The half I give you is to show you the MySQL code. The half I can't give you is translating it into Laravel.

Here's an example of finding the 3 most populous cities in each province of Canada:

SELECT
    province, n, city, population
FROM
  ( SELECT  @prev := '', @n := 0 ) init
JOIN
  ( SELECT  @n := if(province != @prev, 1, @n + 1) AS n,
            @prev := province,
            province, city, population
        FROM  Canada
        ORDER BY
            province   ASC,
            population DESC
  ) x
WHERE  n <= 3
ORDER BY  province, n;

More details and explanation are found in my blog on groupwise-max.


Edit your Category model

public function fourPosts() {
    // This is your relation object
    return $this->morphedByMany('App\Post', 'categorizable')
    // We will join the same instance of it and will add a temporary incrementing
    // Number to each post
    ->leftJoin(\DB::raw('(' . $this->morphedByMany('App\Post', 'categorizable')->select(\DB::raw('*,@post_rank := IF(@current_category = category_id, @post_rank + 1, 1) AS post_rank, @current_category := category_id'))
    ->whereRaw("categorizable_type = 'App\\\\Post'")
    ->orderBy('category_id', 'ASC')->toSql() . ') as joined'), function($query) {
        $query->on('posts.id', '=', 'joined.id');
    // And at the end we will get only posts with post rank of 4 or below
    })->where('post_rank', '<=', 4);
}

Then in your controller all categories you get with this

$categories = Category::whereParentId(1)->with('fourPosts')->get();

Will have only four posts. To test this do (remember that now you will load your posts with fourPosts method, so you have to access the four posts with this property):

foreach ($categories as $category) {
    echo 'Category ' . $category->id . ' has ' . count($category->fourPosts) . ' posts<br/>';
}

In short you add a subquery to the morph object that allows us to assign temporary incrementing number for each post in category. Then you can just get the rows that have this temporary number less or equal to 4 :)


I think the most cross-DBMS way to do this would be using union all. Maybe something like this:

public function index(Category $category)
{
    $categories = $category->whereParentId(2)->get();

    $query = null;

    foreach($categories as $category) {
        $subquery = Post::select('*', DB::raw("$category->id as category_id"))
            ->whereHas('categories', function($q) use ($category) {
                $q->where('id', $category->id);
            })->take(4);

        if (!$query) {
            $query = $subquery;
            continue;
        }

        $query->unionAll($subquery->getQuery());
    }

    $posts = $query->get()->groupBy('category_id');

    foreach ($categories as $category) {
        $categoryPosts = isset($posts[$category->id]) ? $posts[$category->id] : collect([]);
        $category->setRelation('posts', $categoryPosts);
    }

    return view('posts.index')->with(compact('categories'));
}

And then you'd be able to loop through the categories and their posts in the view. Not necessarily the nicest looking solution but it would cut it down to 2 queries. Cutting it down to 1 query would probably require using window functions (row_number(), in particular), which MySQL doesn't support without some tricks to emulate it (More on that here.). I'd be glad to be proven wrong, though.


Does this work for you?:

$categories = $category->with(['posts' => function($query)
    {
        $query->take(4);
    })
    ->whereParentId(2)
    ->get();