fabricecw
4 months ago

Complex query performance

Posted 4 months ago by fabricecw

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!

Please sign in or create an account to participate in this conversation.