Laravel with eager loading using condition

Question

I'm trying to optimize a relationship using eager loading and implement conditions in the with clause for eager loading, however when I do the below:

 $totalOpenQry =  Enquiry::whereIn('staff_id', $employeeIds)
            ->with(['enquiryStats' => function ($query) {
                $query->where('is_open','=',true)
                    ->where('is_dead','=',false)
                    ->orderBy('id','asc');
            }])
            ->where('ed_timestamp', '>=', $dateRange['start'])
            ->with('country');

        $totalOpen = $totalOpenQry->toSql();

$totalOpenQry->toSql()

produces the following:

'select * from `enquiries` where `staff_id` in (10, 15) and `ed_timestamp` >= '2017-09-12';

It seems to be ignoring the where conditionals in my with clause. Is there a way to fix this or implement it properly?

Thanks

Relationships aren't loaded in the same query. Eager loading still runs an additional query to load data, just not an additional query per row. — Devon Bessemer, Sep 12 '17 at 13:07
Using Laravel 5.3. @Devon, I thought I could eager load relationships using with? My results seem to be ignoring the is_open and is_closed entirely — liamjnorman, Sep 12 '17 at 13:08
I recommend using Laravel Debugbar. It will show you all the queries run. The eager loaded query will be directly after this one. — Devon Bessemer, Sep 12 '17 at 13:09

Mr. Pyramid · Accepted Answer · 2017-09-12T13:27:14.763

2

whereHas() works basically the same as has() but allows you to specify additional filters for the related model to check.

$totalOpenQry =  Enquiry::whereIn('staff_id', $employeeIds)
        ->with('enquiryStats')
        ->whereHas('enquiryStats', function ($query) {
            $query->where('is_open','=',true)
                ->where('is_dead','=',false)
                ->orderBy('id','asc');
        })
        ->where('ed_timestamp', '>=', $dateRange['start'])
        ->with('country');
// only  enquiryStats that have is_open = true and is_dead = false are returned

edited Sep 12 '17 at 13:27

answered Sep 12 '17 at 13:10

Mr. Pyramid

3,855
5
32
56

whereHas accepts parameter as per code base [ref link](https://stackoverflow.com/questions/43174190/laravel-where-selection-for-eloquent-eager-loading-relationship) – Mr. Pyramid Sep 12 '17 at 13:14
Add an explanation about what this does and I'll upvote it. – Devon Bessemer Sep 12 '17 at 13:19
1

This fixed my problem. Thanks @Mr.Pyramid – liamjnorman Sep 12 '17 at 13:21
yeah go for it I have added the explanation – Mr. Pyramid Sep 12 '17 at 13:27

score 1 · Answer 2 · answered Sep 12 '17 at 13:14

1

You'll get 2 query:

1: 'select * from `enquiries` where `staff_id` in (10, 15) and `ed_timestamp` >= '2017-09-12';

2 Something like: 'select * from `stats` where `staff_id` in (10, 15) AND `is_open`=true...;

But ->toSql() show first only.

answered Sep 12 '17 at 13:14

aleksejjj

1,715
10
21

Maybe I am misunderstanding then but I need both to be the same query so that it limits my result set? – liamjnorman Sep 12 '17 at 13:16
@liamjnorman Are you trying to constrain enquiries or the stats? – Devon Bessemer Sep 12 '17 at 13:17
I'm trying to load the enquiries with related enquiryStats and then constrain the results of how many enquiries I get based on the enquiryStats – liamjnorman Sep 12 '17 at 13:18
then you want whereHas in the other answer. The conditions on with() will just constrain the results of the related data. – Devon Bessemer Sep 12 '17 at 13:18

Laravel with eager loading using condition

2 Answers2