Eloquent, query condition based on relation

I have got a little problem.

Take a look on it: I have first relation:

    public function statuses()
    {
        return $this->morphMany('App\Models\Status', 'statusable')->orderBy('id', 'DESC');
    }

and:

    public function currentStatus()
    {
        return $this->morphOne('App\Models\Status', 'statusable')->orderBy('id', 'DESC');
    }

Second relation works perfectly fine, it gives my the newest status, so if eg. order has 3 statuses, pending, processing, completed it returns me completed.

ok and here comes the main problem:

    Order::whereHas('currentStatus', function($builder) {
       $builder->where('status', StatusCode::PENDING);
    })->get();

This "where" is searching in every status of each Order. How can I make this to query only 'currentStatus', not all statuses?

It is returning Orders that had Pending status in the past and their currentStatus is not Pending anymore. Orders that doesnt have Pending as currentStatus shouldn't be considered here.

tykus

7 years ago

Level 104

Using whereHas with polymorphic relations has never been straight-forward.

The solution below will get the desired results, but I would probably look to optimise further if it was my own (I would worry about performance whenever the count of statuses gets large):

Order::with('status')->whereHas('statuses', function ($query) {
    $query->addSubSelect(
        'current_status',
        Status::select('status')
            ->whereRaw('statusable_id = orders.id')
            ->latest()
    )->where('current_status', StatusCode::PENDING);
})->get();

Briefly what it does is creates a second sub-select (the first comes from whereHas) which gets the current_status, and then filters the first sub-select by the desired status - from there the whereHas part works as normal.

I would put logic that inside a method on the Order model for convenience.

Please or to participate in this conversation.