Consider this code

$amUser = User::where('status',1)->get();
$data=[];
foreach($amUser as $a){
    $salescalls = $a->salesCall();
    if (isset($request->corporate_name))
    {
        $salescalls=$salescalls->where('corporate_name','like','%'.$request->corporate_name.'%');
    }
    if (isset($request->start_date))
    {
        $salescalls=$salescalls->whereDate('sales_calls.created_at','>=',$request->start_date);
    }
    if (isset($request->end_date))
    {
        $salescalls=$salescalls->whereDate('sales_calls.created_at','<=',$request->end_date);
    }
    $data[$a->id]=$salescalls->get();
    $grandTotal['sum'] =$salescalls->count();
}

As you see it will execute salesCall() query on each loop and producing n 1 problem, but if I eager loading $amUser->load('salesCall') I can't do whereDatequery so my strategy is build all where condition and then populate all user id then combining them all together in one execution, but I don't know how to populate all these where condition and call them later. How to do that or any advice?

CodePudding user response：

When building a query, you may specify which relationships should be eager loaded using the with method :

$amUser = User::with('salescalls')
    ->where('status', 1)
    ->get();

Sometimes you may wish to eager load a relationship but also specify additional query conditions for the eager loading query. You can accomplish this by passing an array of relationships to the with method where the array key is a relationship name and the array value is a closure that adds additional constraints to the eager loading query :

$amUser = User::with(['salescalls' => function($query) use ($request){
    if($request->corporate_name){
        $query->where('corporate_name', 'like', '%' . $request->corporate_name . '%');
    }

    if($request->start_date){
        $query->whereDate('created_at', '>=', $request->start_date);
    }

    if($request->end_date){
        $query->whereDate('created_at', '<=', $request->end_date);
    }
}])->where('status', 1)->get();

So you don't need to use foreach. Eager loading alleviates the "N 1" query problem.

And oh, you can also use when method btw.

But I want to only retrieve salescalls data

Yes you can :

$data = $amUser->pluck('salescalls');

Then, how to count data? (without empty collection)

$grandTotal = $amUser->pluck('salescalls')->flatten()->count();