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

Question

I am using Laravel 5.1 and I need to limit the number of related records I am pulling using a polymorphic many-to-many relationship.

What I would like to do is get a list of categories by parent_id. For each category then I'd like to only pull four posts.

I have have this working with the code below, but it results in a bunch of extra queries. I'd like to just hit the database once if possible. I'd like to use the Laravel/Eloquent framework if at all possible, but am open to whatever works at this point.

@foreach ($categories as $category)
  @if ($category->posts->count() > 0)
    <h2>{{ $category->name }}</h2>
    <a href="/style/{{ $category->slug }}">See more</a>

    {-- This part is the wonky part --}

    @foreach ($category->posts()->take(4)->get() as $post)

      {{ $post->body }}

    @endforeach

  @endif
@endforeach

PostsController

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

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

Database

posts
    id - integer
    body - string

categories
    id - integer
    name - string
    parent_id - integer

categorizables
    category_id - integer
    categorizable_id - integer
    categorizable_type - string

Post Model

<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
    public function categories()
    {
        return $this->morphToMany('App\Category', 'categorizable');
    }

Category Model

<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Category extends Model
{
    public function category()
    {
        return $this->belongsTo('App\Category', 'parent_id');
    }
    public function subcategories()
    {
        return $this->hasMany('App\Category', 'parent_id')->orderBy('order', 'asc');
    }
    public function posts()
    {
        return $this->morphedByMany('App\Post', 'categorizable');
    }

I have seen a number of links to this on the web, but nothing that has actually worked for me.

I have tried this solution without any luck.

$categories = $category->with('posts')
->whereParentId(2)
->posts()
->take(4)
->get();

I have looked into this solution by Jarek at SoftOnTheSofa, but it is for a hasMany relationship and to be honest is a bit beyond my sql skill for me to adapt it for my needs.

Edit

I added a github repo for this setup, if it is useful to anybody.

Answer 1

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 :)

Answer 2

Does this work for you?:

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

Answer 3

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.

Answer 4

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.

Answer 5

I think the best option (without using raw queries) would be to setup a custom method to be used with the eager loading.

So in the Category model you could do something like:

<?php

public function my_custom_method()
{
    return $this->morphedByMany('App\Post', 'categorizable')->take(4);
}

And then use that method when eager loading:

<?php

App\Category::with('my_custom_method')->get();

Make sure to use my_custom_method() instead of posts() in your view:

$category->my_custom_method()

Answer 6

You can try this one, that work for me!

$projects = Project::with(['todo'])->orderBy('id', 'asc')->get()->map(function($sql) {
            return $sql->setRelation('todo', $sql->todo->take(4));
        });