jeudi 28 septembre 2017

Laravel only return records where the last item in a relationship equals requirements

I am working on some reporting for an app that has users and bookings.

The aim is to return all users that haven't made a booking within the last 2 months.

My query currently returns all users who has ever made a booking at the given team and order the bookings by newest to oldest.

My question is how can I only return users where the last booking made has an estimated booking time more than 2 months ago?

My current query is:

$collection = User::whereHas('customerBookings', function($q) use ($teamId) {
    $q->where('team_id', $teamId);
})->with(['customerBookings' => function ($q) use ($teamId) {
    $q->where('team_id', $teamId);
    $q->orderBy('estimated_booking_time', 'desc');
}])
->get();

This correctly returns all users that have bookings (relationship to a bookings table) that have the requested team_id and then only return the bookings that match the same requirements.

I was going to use ->reject on the collection but I would rather just filter out in the query itself.

Ideally I want to add something to the with claus:

$q->where( FIRST RELATIONSHIP ITEM ->estimated_booking_time, '<=', Carbon::now()->subMonths(2) );

The issue for me is how I can get the first item from the relationship and use it to only return the users depending on this last item.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire