Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Who to a specific row from relation

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]
Tags:

2

Answers


  1. 0

    To achieve your goal of retrieving orders for which user_id=123 is the last one who added a suborder, you can modify your query to first get the latest suborder added by user_id=123, and then retrieve the corresponding order. Here’s how you can do it:

    $myUserId = 123;

$latestSuborder = Suborder::where('user_id', $myUserId)
    ->latest()
    ->first();

if ($latestSuborder) {
    $order = $latestSuborder->order;
    // Now $order contains the order associated with the latest suborder added by user_id=123
    // You can use $order as needed
} else {
    // Handle the case where there are no suborders added by user_id=123
}

    This code first retrieves the latest suborder added by user_id=123. Then, it retrieves the corresponding order using the relationship between orders and suborders. If no suborder is found for user_id=123, you can handle that case accordingly.

    Login or Signup to reply.
  2. 0

    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')
        ->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())])
  ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search