In Laravel, I have a blog table. I have categories within that table. I am looking to grab the latest blog for each category; as a result set.

Example

For the above, I would want to return rows with ids of 4, 5 and 6 because this would be the latest created_at for each category_id within the table.

I have tried groupBy, but this isn't working. However, I feel like this should be easy. So below is my latest try.

blogs = Blogs::where('user_id', $this->id)
    ->groupBy('category_id')
    ->orderBy('created_at','desc')
    ->get();

CodePudding user response：

If you are using laravel 8 or higher, you can write below relationship on Category model:

/**
 * Get the category's most recent blogs.
 */
public function latestBlogs()
{
    return $this->hasMany(Blog::class)->latest();
}

Than, when you need to get all categories with latest Blogs, you can call:

Category::query()
     ->with('lastestBlogs')
     ->get();

When you need to get all latest Blogs based on categories, you can call:

Category::query()
     ->with('lastestBlogs')
     ->get()
     ->pluck('lastestBlogs')
     ->flatten(); 

CodePudding user response：

You can try with a DB raw query like this:

DB::table('blogs as b')
    ->select('b.*')
    ->leftJoin('blogs as b2', function ($join) {
        $join->on('b.id','=','b2.id')
             ->where('t.created_at', '<', 't1.created_at');
    })
    ->whereNull('b1.id')
    ->get();