limit post number per category

$categories = Category::all();

foreach($categories as $category):

$posts[] = $category->posts()->latetest()->take(10)->get();

endforeach;

I think this is fine

@Mohsen

This is a returning question in the SQL community as there is no simple way to do it, you'll have to do joins, sub queries, groups, etc..

The method @nagavinod424 suggest is not good either because it will execute n+1 queries, that is one query per relationship..

So perhaps a better way would be to load the relationships the Laravel way since that will only be 2 queries and then remove the unwanted relationship objects..

$users = App\User::with('foo')->get()->each(function($user) {
    $user->limitRelationship('foo', 5);
});

For this to work, we need to add the limitRelationship() to your model..

public function limitRelationship(string $name, int $limit)
{
    $this->relations[$name] = $this->relations[$name]->slice(0, $limit);
}

Solving as a single query is indeed difficult.

@nagavinod424 method produces a query per category (but I would use load instead so that the posts are still relations of categories)

@SaeedPrez loads all posts in one go but then discards some of the results. No offence Saeed, but I would probably rather limit the loop in the view

The 'simplest' answer for you will depend;

if you have a small number of categories and a lot of posts to do the load per category.

If you have a large number of categories and relatively few posts, load all the posts and truncate results.

When I say load the related posts, I mean like;

        $categories = Category::get();

        foreach($categories as $category)
        {
            $category->load('latest_posts');
        }

and then in the category model, create a new relationship;

    public function latest_posts()
    {
        return $this->hasMany(Post::class)->latest()->limit(10);
    }

If you use this method, don't forget that you will need to iterate over latest_posts (and not posts) because that is what you called the relations

@Snapey I have 12 categories and lots of posts per category . load doesn't help and I think Its not efficient for this case same as the @nagavinod424 solution .

@SaeedPrez I used your code but in laravel debugger there is no any "LIMIT" or "OFFSET" in queries . I realized that all posts was selected from database . how does it work actually ?

@Snapey

I do agree, it's not a very good option if you have a lot of rows in your database. I wish they would add better support for this in SQL.

@Mohsen

It loads the relationship into a collection and then takes the needed number of items. It's not the most optimal option but at least it limits the database queries to only two even if you have many categories.

Edit: misunderstood, removed that..

Edit2: Maybe a good solution would be to cache the posts? Update cache when new post is created.

Here's one article on the way to do it with less server effort

https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/

but as you will see, its not a trivial problem.

@Snapey great idea for a pull request.. Model::withLimit(['posts', 'comments'], 5)->get()

@saeedprez might be hard without understanding how you might group the relation (implementation specific)

@Snapey The url you shared seemed to me the best way to achieve this task. But when i added the scope to my model and called from controller it throw an error saying

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' (SQL: select count(*) as aggregate from...

I am not sure what this mean though is it because that is very old post or is there anyway to fix this issue?? Thank you in advance

I created a package for this: https://github.com/staudenmeir/eloquent-eager-limit

Use the HasEagerLimit trait in both the parent and the related model.

class Post extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

class File extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

Then you can apply ->take(10) to your relationship.