Complex query performance

Hey!

I need to track statuses on an order model. It's a many to many relationship between order and status, since an order has multiple statuses and a status is used in many orders.

Now there is always the "current" and the "next" status of the order. The status sequence is controlled by a "step" integer in the pivot.

| order_id | status_id | step | completed_at | | ------------- | --------------| ------| ----------------| | 1 | 1 | 10 | 2019-0706 16:00 | | 1 | 2 | 20 | null | | 1 | 3 | 30 | null |

Status relationship:

/**
 * Get the order's statuses.
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function statuses()
{
    return $this->belongsToMany('App\Status')->using('App\OrderStatus')->withPivot('completed_at', 'user_id', 'step')->orderBy('pivot_step');
}

Next status:

/**
 * The status of the order which is in progress.
 *
 * @return Status
 */
public function statusInProgress()
{
    return $this->statuses()->wherePivot('completed_at', null)->first();
}

Current status:

/**
 * The last completed status.
 *
 * @return Status
 */
public function lastCompletedStatus()
{
    return $this->statuses()->wherePivot('completed_at', '!=', null)->get()->last();
}

And obviously when it comes to filtering, users want filter by all orders which are in a specific status. How can I query the orders in a performant way?

Currently I have to loop over all the orders and check if the methods return is equal to the filtered status...

I have considered about the following approaches:

Store the current and next status id directly on the order -> redudant data
MySQL views

Is there a better approach to do that? Thanks for your help!

DavidPetrov

6 years ago

Level 2

Firstly, your lastCompletedStatus() is not conventional and does not return a relationship, but a retrieved model instead. In order to increase efficiency, you need the conventional approach:

public function lastCompletedStatus()
{
    return $this->statuses()->wherePivot('completed_at', '!=', null)->latest()->take(1);
}

Then, you might simply query your orders by last completed status using Eloquent's whereHas() method:

$orders = $user->orders()->whereHas('lastCompletedStatus', function($query){
    $query->where('name', 'given_status_name'); //or however you define a certain status
})->get();

This will retrieve all of the user's orders having their last completed status's 'name' column match the given value (in this case 'given_status_name'). You can change that accordingly.

Hope that answers your question!

1 like

fabricecw

6 years ago

Level 7

Hm, I think this won't work since you can't use last() method in a query builder instance.

DavidPetrov

6 years ago

Level 2

Yeah, my mistake, I got mislead by what was provided. In order to obtain the last completed status you can instead use ->latest()->take(1) for the query. Haven't tested, but should fit into the concept.

Please or to participate in this conversation.