I have Items table that has relation to Histories table.

I want to get count of items that has only latest history.status

I still can't get the exact same count result because its always count all of the histories not the latest one

Here is my code:

create_items_table.php

Schema::create('items', function (Blueprint $table) {
        $table->id();
        $table->string('code');
        $table->string('name');
        $table->longText('description')->nullable();
        $table->longText('picture')->nullable();
        $table->timestamps();
    });

create_histories_table.php

$table->foreignId('item_id')->constrained();
$table->string('status')->nullable();
$table->longText('description')->nullable();
$table->dateTime('date')->nullable();

model of Item.php

public function histories(){
        return $this->hasMany(History::class);
    }
public function latestHistory(){
        return $this->hasOne(History::class)->latest();
    }

model of History.php

public function item()
{
    return $this->belongsTo(Item::class);
}

MyController.php

 $items_status['good'] = Item::with('latestHistory')->whereHas('latestHistory', function ($q) {
            $q->where('status', 'good');
        })->count();

 $items_status['broken'] = Item::with('latestHistory')->whereHas('latestHistory', function ($q) {
            $q->where('status', 'broken');
        })->count();

dd($items_status);

CodePudding user response：

i guess you mean latestOfMany() ?

//Item.php
public function latestHistory() {
        return $this->hasOne(History::class)->latestOfMany();
    }

Also do you have any solution for count the items that doesn't have history? Check docs for doesntHave

$items_status['no_history'] = Item::doesntHave('history')->count();