How to get sum of a column in this relation? user > booking > booking_items

How about something like this:

$sum = BookingItem::whereHas('booking.user', function($query) use ($userId){
    $query->where('id', $userId);
})->whereHas('booking', function($query){
    $query->where('id', $this->id); // Or however you get the booking id
    $query->where('status', 'confirmed');
})->sum('item_num');

@grenadecx Yes brilliant.

I was trying it inside Booking.php but now I have moved your code inside a new method in BookingItem.php

public function getUserBookingCount() {
    return $this->whereHas('booking.user', function($query) use ($userId){
    $query->where('id', $userId);
})->whereHas('booking', function($query){
    $query->where('id', $this->id); // Or however you get the booking id
    $query->where('status', 'confirmed');
})->sum('item_num');
}

It seems to be working fine. Thank you very much.

I am just curious to know how to use a scope rather than $query->where('status', 'confirmed'); I have added a scopeConfirmed() in Booking.php so I thought I can add another conditions there as required rather than adding it on two places?

You can use the scope instead. Just replace the

$query->where('status', 'confirmed');

$query->confirmed();

Scopes are called on those models, so when we are in the whereHas booking closure, the scope used there will be on the relationship, in this case the booking model.

Edit: Actually I'm not sure what I was thinking. You don't need the whereHas for the booking id if you want for all the bookings. You can remove that completely.

// You can remove this row, unless you want for a specific booking
$query->where('id', $this->id); // Or however you get the booking id

@grenadecx Yes scope works well as you suggested. Thank you.

I have set a limit to users e.g. a user can have 10 items. So if user tries to book more items, then I want to check how many they are allowed e.g. $user->numAllowed - $bookingSum (calculated above). It's not for a single booking instead all bookings in a month. This is why I was trying to create this method inside Booking.php as it made more sense there.

@grenadecx how would you advise to build the query if I were to achieve the above in Booking.php

Ideally I would like to use it as below.

auth()->user()->countConfirmedBookingItems() Or perhaps as an admin (new Booking)->countConfirmedBookingItems($userId);

So, in Booking.php, if I create method

public function countConfirmedBookingItems($userId=null)
{
    $userId = $userId ?? auth()->id();
    --> If I use, 
    return $this->whereHas('user', function($query) use($userId) {
        $this->where('id', $userId);
    });
    
}

This would execute this SQL

select * from bookings where exists (select * from users where bookings.user_id = users.id and id = 1231)

I am a bit new so trying to understand how to select or sum column "item_num" from booking_items table when setting up inside Booking.php

E.g. if I create that above method inside BookingItem.php, then the query naturally select(*) from booking_items .... so I can do sum() ... however not so sure how to sum when inside Booking.php or even User.php ??

Any ideas?

Thanks for your help so far.

Alright, I understand. But if that's the case, wouldn't it make more sense to have it as a static function inside the bookings model and not the BookingItem?

public static function getUserBookingCount($userId = null)
{

    $userId = $userId ?? auth()->id();

    // If you use it as a static function you can't use $this variable, so use the model directly
    $sum = BookingItem::whereHas('booking.user', function($query) use ($userId){
            $query->where('id', $userId);
    })->whereHas('booking', function($query){
        $query->where('status', 'confirmed');
    })->sum('item_num');

}


// As per your example in a controller or elsewhere:
$bookingSum = Booking::getUserBookingCount();

Edit: Just saw your reply, let me revise my answer...

E.g. if I create that above method inside BookingItem.php, then the query naturally select(*) from booking_items .... so I can do sum() ... however not so sure how to sum when inside Booking.php or even User.php ??

That's because you use the relation $this->. You would need to do it independent and acessing the model directly, like BookingItem::whereHas....

@GRENADECX - Ok I can what you have done i.e. simply moved the code inside Booking.php and accessed BookingItem directly from there. That's Ok with me. Thanks for clarifying how when using static $this gets unavailable.

As I am new so I was trying to figure out using relationship method setup already by me.

Thanks again. :)

Well there are a lot of ways to do the same thing. I'm not sure what's best. You could move it inside the User model and not being static and then replace the $userId with $this->id, that way you could fetch it by the user model directly. You could also use static inside the booking model and call that static in a nonstatic on the user model. That way you could do both Booking::function and the $user->function

The only noteworthy thing to remember is when you work on the relations is that it will automatically apply the constrains of the relation on the model if you work directly with the relation. When you work with the model directly, you wont get those retraints.

If you want static functions or not that's up to you, both are possible. But if you want it inside the booking model, static is the only sensible because it's not a specific booking. If you want it inside user model, nonstatic works fine because it's on a specific user.

Hope I make any sense...

Edit: Don't forget to mark the answer if you are satisfied with it working.