How to filter the related model on Laravel Eloquent

1
votes

I got an Affiliate model who has a hasMany relationship with Booking.

Now I want to bring all the affiliates with their bookings where the booking date is between 2 given dates and only bring the bookings that comply with that constraint.

now I got this

$affiliate = Affiliate::whereHas('bookings', static function (Builder $builder) use ($params) {
                $builder->whereOrAny('date','>=', $params['dateFrom']);
                $builder->whereOrAny('date','<=', $params['dateTo']);
            })->with('bookings');

and this does a good job filtering the affiliates that contains a booking that has the date between the ones in the params, BUT I want to also filter the bookings so the resulting $affiliates->bookings only contains the bookings that have a date between those given dates

by example, I have something like this

$affiliates = [
        {
            name : 'Example 1',
            bookings : [
                {
                    'date' : 2020-06-01
                },
                {
                    'date' : 2019-05-01
                },
                {
                    'date' : 2019-04-01
                }
            ]   
        },
        {
            name : 'Example 2',
            bookings : [
                {
                    'date' : 2020-06-01
                },
                {
                    'date' : 2020-05-01
                },
                {
                    'date' : 2020-04-01
                }
            ]   
        },
        {
            name : 'Example 3',
            bookings : [
                {
                    'date' : 2019-06-01
                },
                {
                    'date' : 2020-05-01
                },
                {
                    'date' : 2020-04-01
                }
            ]   
        }
    ]

and If I filter with fromDate = 2019-01-01 and toDate = 2019-31-12 (any date in 2019) return something like this

    $affiliatesFiltered = [
        {
            name : 'Example 1',
            bookings : [
                {
                    'date' : 2019-05-01
                },
                {
                    'date' : 2019-04-01
                }
            ]   
        },
        {
            name : 'Example 3',
            bookings : [
                {
                    'date' : 2019-06-01
                },
            ]   
        }
    ]

as you can see not only I need to filter the affiliates to only bring the ones who have bookings between those dates but also bring only the bookings between those dates I hope you guys can help me

2
phplaraveleloquentorm
Date beetwen $builder->where([['date','>=', $params['dateFrom']], ['date','<=', $params['dateTo']]]); - Alex Black
Apply whereOrAny inside with same as whereHas - Aslam
Or you can take a look at this stackoverflow.com/a/29594039/8012110 - Aslam

2 Answers

1
votes

Ok I got a solution thanks to some of the comments, in case someone is wondering how to do this It would be something like this

$affiliate->with(['bookings' => function($query) use($params) {
    $query->where('date','>=', $params['dateFrom'])
        ->where('date','<=', $params['dateTo'])    
        ;
}])

and it works like a charm :) thanks a lot!

0
votes

Assuming your bookings model also has the dateFrom and dateTo properties, this should work for you in order to filter your relationship as well:

...
->with(['bookings' => function ($q) use ($params){
$q->where(
[
    ['bookings.dateFrom','>=',$params['dateFrom']],
    ['bookings.dateTo','<=',$params['dateTo']],
]
);

}]);