Question 17092 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel orm – Get top rows from relation

I have a categories table. every category has some posts.
I want to get categories with their last 10 posts.
So I tried this:

Category::query()->with(['posts' => function($q) {
     $q->take(10);
}])->get();

The problem is instead of putting 10 posts in each category record, it returns a total of 10 posts in all categories items.

Expected:

C1:
   id: 1
   posts: 10 post
C2:
   id: 2
   posts: 10 post

What I got

C1:
   id: 1
   posts: 4 post
C2:
   id: 2
   posts: 6 post
Tags:

2

Answers


  1. 0

    Would try this version

    
Category::with('posts')->get()->map(function($category) {
  $category->posts = $category->posts->take(10);
  return $category;
});

or optimize version try this package [link][1]


  


  [1]: https://github.com/staudenmeir/eloquent-eager-limit
    Login or Signup to reply.
  2. 0

    In fact, this is not a trivial task. The code you provided generates two queries:

    SELECT * FROM categories;
SELECT * FROM posts WHERE category_id IN (1, 2, 3, ...) LIMIT 10;

    The 2nd query selects only 10 posts, not 10 per category.

    To select 10 posts per category, you can make the following query:

    SELECT * FROM (
    SELECT *, row_number() OVER(PARTITION BY category_id) rn FROM posts
) posts
WHERE rn <= 10 AND category_id IN (1, 2, 3 ...);

    Now let’s implement it through Laravel’s queryBuilder:

    $result = Category::with(['posts' => static function($q) {
    $q->fromSub(
        Post::selectRaw('*, row_number() OVER(PARTITION BY category_id) rn'),
        'posts'
    )->where('rn', '<=', 10);
}])->get();

    As a result, we still make only 2 queries and select only necessary posts (do not pull the entire table)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search