Laravel - Get count of parent model (meeting criteria based on children)-CodePudding

In my scenario I have events as a relationship of bookings, the event has a utc_start date on it. I want to query which bookings are upcoming, based on their related event's utc_start date being in the future.

This is my current solution, which does work, but I would prefer a solution that uses Laravel / Eloquent rather than just being hacked to work.

 /**
  * Upcoming bookings
  * @return total tally of events ahead of today's date
  */
function upcoming_bookings() {
    $event_count_per_booking = (array)Booking::withCount(['events' => function($q){
        $q->whereRaw("utc_start > STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')" , Carbon::now('Australia/Perth')->format('Y-m-d H:i'));
    }])->pluck('bookings.events_count')->toArray();
    # Response eg: [0,0,1,1,0]
    $future_events = array_filter($event_count_per_booking); # Remove zeros
    return count($future_events); # Response eg: 2
}

CodePudding user response：

You can try this, the whereHas method can bypass the additional steps:

function upcoming_bookings() {
    return Booking::whereHas('events', function (Builder $query){
       $query->whereRaw("utc_start > STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')" , Carbon::now('Australia/Perth')->format('Y-m-d H:i'));
    })->count();
}

make sure to import on top

use Illuminate\Database\Eloquent\Builder;

CodePudding user response：

I think you dont need to use typecasting and toArray().

$events = Booking::withCount(['events' => function($q){
        $q->whereRaw("utc_start > STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s')" , Carbon::now('Australia/Perth')->format('Y-m-d H:i'));
    }])
->select('bookings.events_count')
->map(function($item, $key) {
   return $item !== 0;
})->count();