show last post from each category

Question

I have two models Post and Category

// migration post

public function up()
{
    Schema::create('posts', function (Blueprint $table) {
        $table->increments('id');
        $table->string('title');
        $table->string('body');
        $table->string('image');
        $table->integer('category_id')->unsigned();
        $table->foreign('category_id')->references('id')->on('categories');
        $table->timestamps();
    });
}

// migration category

public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->timestamps();
    });
}

How can I display only the last post from each category in home page?

Try: `$catList = Category::lists('id'); $post = Post::wherein('category_id', $catList)->groupby('category_id')->latest()->get();` — Hiren Gohel, Jan 02 '18 at 06:48
@HirenGohel thanks ,but I have this error Call to undefined method Illuminate\Database\Query\Builder::lists() — user5565240, Jan 02 '18 at 07:11
You can use method pluck. Method lists' is removed in Laravel 5.3. Try: `$catList = Category::pluck('id'); $post = Post::wherein('category_id', $catList)->groupby('category_id')->latest()->get();` — Hiren Gohel, Jan 02 '18 at 07:13
SQLSTATE[42000]: Syntax error or access violation: 1055 'ex.posts.id' isn't in GROUP BY (SQL: select * from `posts` where `category_id` in (1, 2) group by `category_id` order by `created_at` desc) — user5565240, Jan 02 '18 at 07:19

Ohgodwhy · Answer 1 · 2018-01-02T08:16:24.617

3

Hiren was close, but you need to go from the category since your post is owned by the category

$category->posts()->latest()->first();

Alternatively you could work backwards:

$post = Post::latest()->whereHas('category', function($q) use($category_id) {
    return $q->where('id', $category_id);
})->first();

For this to work you'll need to define your model relationships:

Category Model needs this function:

public function posts() 
{
    return $this->hasMany(App\Post::class);
}

Post Model needs this function:

public function category()
{
    return $this->belongsTo(App\Category::class);
}

To respond to Alexey Mezenin, we can just pass a callback to with() to define which posts we want to pull in for each category, performing the correct eager load.

Category::with(['posts' => function($q) {
    return $q->latest()->first();
})->get();

edited Jan 02 '18 at 08:16

answered Jan 02 '18 at 06:53

Ohgodwhy

49,779
11
80
110

@user5565240 added additional info about model relationship definitions. – Ohgodwhy Jan 02 '18 at 07:42
Just want to say that this solution will take post only for one category and if you'll use it in a loop, it will create [N+1 queries](https://laravel.com/docs/5.5/eloquent-relationships#eager-loading) (101 query for 100 categories). – Alexey Mezenin Jan 02 '18 at 08:06
@Ohgodwhy you've added another solution (the `with()` one), but it doesn't work, because it will load only one post total and not one post per each category. – Alexey Mezenin Jan 13 '18 at 19:48

score 1 · Answer 2 · answered Jan 02 '18 at 07:30

1

An Eloquent solution for loading categories with latest post is to create an additional hasOne() relationship in the Category model:

public function latestPost()
{
    return $this->hasOne(Post::class)->latest();
}

And then use eager loading:

Category::with('latestPost')->get();

This will generate just 2 queries to DB.

answered Jan 02 '18 at 07:30

Alexey Mezenin

158,981
26
290
279

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category.category_id' in 'where clause' (SQL: select * from `categories` where `categories`.`category_id` in (1, 2) order by `created_at` desc) – user5565240 Jan 02 '18 at 08:06
1

@user5565240 you can't get this error with my code, you've modified it. Please double check it. – Alexey Mezenin Jan 02 '18 at 08:08
it gives me empty rows in my table in home page and if I add a category without associated post gives me error – user5565240 Jan 04 '18 at 13:35
1

This was so useful!! Thank you very much Alexey! It totally fixed my scope problem! I was trying to be cool and apply the scope thing I just learned haha. – Blagoh Jan 13 '18 at 19:44

score 0 · Answer 3 · answered Sep 11 '18 at 16:15

public function up()
{
    Schema::create('news', function (Blueprint $table) {
        $table->increments('id');
        $table->string('slug')->unique();
        $table->unsignedInteger('author_id');
        $table->unsignedInteger('category_id');
        $table->string('subject');
        $table->text('short');
        $table->text('content');
        $table->integer('view')->default(0);
        $table->integer('status')->default(0);
        $table->string('image');
        $table->timestamps();

        $table->foreign('author_id')
              ->references('id')->on('users')
              ->onDelete('cascade');
        // $table->foreign('category_id')
        //       ->references('id')->on('categories')
        //       ->onDelete('cascade');
    });
    // Schema::enableForeignKeyConstraints();
}
public function up()
{
    Schema::create('categories', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name')->unique();
        $table->timestamps();
    });
}

in contoller:

    $latestpostlist = News::whereIn('created_at',function($query){
            $query->select(DB::raw('max(created_at)'))
                      ->from('news')
                      ->groupBy('category_id');
    })->get();

in your case news will be post. this query worked for me

show last post from each category

3 Answers3

Linked