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