I'm trying to load only the last 3 comments on every post

Question

i want get all posts with last three comment on each post. my relation is

public function comments()
{
   return $this->hasMany('App\Commentpostfeed','post_id')->take(3);
}

This would return only 3 comments total whenever I called it instead of 3 comments per post. i use this way :

1 :

Postfeed::with(['comment' => function($query) {
         $query->orderBy('created_at', 'desc')->take(3); }]);

2 :

 $postings = Postfeed::with('comments')->get();

but getting same result. please help me out for this problem.

Answer 1

Can you try like that ?;

Postfeed::with('comment')->orderBy('id','desc')->take(3);

Answer 2

Using plain mysql (If using Mysql) query you can get 3 recent comments per post using following query which rejoins comment table by matching created_at

SELECT p.*,c.*
FROM posts p 
JOIN comments c ON p.`id` = c.`post_id`
LEFT JOIN comments c1 ON c.`post_id` = c1.`post_id` AND c.`created_at` <= c1.`created_at`
GROUP BY p.`id`,c.`id`
HAVING COUNT(*) <=3
ORDER BY p.`id`,c.`created_at` DESC

Sample Demo

Using laravel's query builder you can write similar to

$posts = DB::table('posts as p')
    ->select('p.*,c.*')
    ->join('comments c', 'p.id', '=', 'c.post_id')
    ->leftJoin('comments as c1', function ($join) {
                $join->on('c.post_id', '=', 'c1.post_id')->where('c.created_at', '<=', 'c1.created_at');
            })
    ->groupBy('p.id')
    ->groupBy('c.id')
    ->having('COUNT(*)', '<=', 3)
    ->orderBy('p.id', 'asc')
    ->orderBy('c.created_at', 'desc')
    ->get();

Answer 3

You can do it like this,

     Postfeed::with('comments',function($query){
        $query->orderBy('created_at', 'desc')->take(3);
    })
    ->get();

Answer 4

You can create a scope in the BaseModel like this :

<?php

class BaseModel extends \Eloquent {

    /**
     * query scope nPerGroup
     * 
     * @return void
     */
    public function scopeNPerGroup($query, $group, $n = 10)
    {
        // queried table
        $table = ($this->getTable());

        // initialize MySQL variables inline
        $query->from( DB::raw("(SELECT @rank:=0, @group:=0) as vars, {$table}") );

        // if no columns already selected, let's select *
        if ( ! $query->getQuery()->columns) 
        { 
            $query->select("{$table}.*"); 
        }

        // make sure column aliases are unique
        $groupAlias = 'group_'.md5(time());
        $rankAlias  = 'rank_'.md5(time());

        // apply mysql variables
        $query->addSelect(DB::raw(
            "@rank := IF(@group = {$group}, @rank+1, 1) as {$rankAlias}, @group := {$group} as {$groupAlias}"
        ));

        // make sure first order clause is the group order
        $query->getQuery()->orders = (array) $query->getQuery()->orders;
        array_unshift($query->getQuery()->orders, ['column' => $group, 'direction' => 'asc']);

        // prepare subquery
        $subQuery = $query->toSql();

        // prepare new main base Query\Builder
        $newBase = $this->newQuery()
            ->from(DB::raw("({$subQuery}) as {$table}"))
            ->mergeBindings($query->getQuery())
            ->where($rankAlias, '<=', $n)
            ->getQuery();

        // replace underlying builder to get rid of previous clauses
        $query->setQuery($newBase);
    }

}

And in the Postfeed Model :

<?php

class Postfeed extends BaseModel {

    /**
     * Get latest 3 comments from hasMany relation.
     *
     * @return Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function latestComments()
    {
        return $this->comments()->latest()->nPerGroup('post_id', 3);
    }

    /**
     * Postfeed has many Commentpostfeeds
     *
     * @return Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function comments()
    {
        return $this->hasMany('App\Commentpostfeed','post_id');
    }

}

And to get the posts with the latest comments :

$posts = Postfeed::with('latestComments')->get();

Ps :

• Source
• For many to many relationships