Who to a specific row from relation

Question

For this issue, I have reduced the complexity of the database.

I have a table with Orders id name

And a table with Suborders id order_id user_id

One order may contain numerous suborders. A suborder is only connected to one order. The suborders are stored in order of entry. Increasing id. I want to get the list of the orders for which user_id=123 is the last one who add a suborder.

    $myuserid=123;
    orders = Order::
        whereHas('suborders', function ($query) use ($myuserid) {
            $query->where('user_id', '=', $myuserid)
                ->take(1)
                ->latest()
                ->get();
            })->get();

I was hoping that the code above would only return a line when the last user_id = 123. But it gives the last line for which user_id=123 is the handler. So rows added after my line are ignored.

I think that I first have to get the last line and after that check if it is 123, but I'm stuck in the syntax. In unknown column user_id errors.

[chatGPT only comes with suggestions that also give the result above. Maybe I'm not asking it correcty]

Answer 1

first of all you need to define this last suborder relation. Each order has many sub orders (via order_id in sub_orders table), but each order also belongs to only one last sub order (via last_suborder_id in orders table ?!?!?).

To create this relation we need this field. How you calculate this last_suborder_id is up to you. You may want to "cache it" on the table each time a sub order is created or calculate in on the fly:

class Order extends Model
{
  public function scopeAppendLastSubOrderId(Builder $query)
  {
    $query->addSelect([
      'last_suborder_id' => SubOrders::query()
        ->whereColumn('sub_orders.order_id', 'orders.id')
        ->latest('id')
        ->limit(1)
        ->select('id')
    ]);
  }

  public function lastSubOrder()
  {
    return $this->belongsTo(SubOrder::class, 'last_suborder_id');
  }

With this in place we can:

Order::query()
  ->appendLastSubOrderId() // dynamically load relation
  ->with(['lastSubOrder' => fn ($with) => $with->where('user_id', auth()->id())])
  ->whereHas('lastSubOrder', function ($query) {
     $query->where('user_id', auth()->id());
  })
  ->get();