How to get multiple whereBetween records in Laravel

Question

I'm building a small application on Laravel 5.4 where I'm having data sets to are being fetched from the relationship something like this:

$meetings = Company::where('name', 'Atlanta Ltd')
    ->withCount(['interactions as investors'=> function ($q) {
        $q
            ->whereBetween('schedule', [Carbon::now()->subMonths(6), Carbon::now()->subMonths(1)])
            ->whereHas('contactsAssociation', function ($q) {
                $q->whereHas('company', function ($q) {
                    $q->where('type', 'like', 'Investor');
                });
            });
    }])->get()
    ->transform(function ($company) {
        $company->investors_count = $company->interactions
            ->pluck('investors_count')
            ->sum();
        return $company;
    });

Currently I'm getting the number of interaction as investor_counts between that particular date, I want to fetch the counts between certain date fields, i.e. suppose I want to have the data to be fetched for consecutive 6 months, I mean data of 6 different months something like this:

->whereBetween('schedule', [Carbon::now()->subMonths(6), Carbon::now()->subMonths(5)])

and

->whereBetween('schedule', [Carbon::now()->subMonths(5), Carbon::now()->subMonths(4)])

and

->whereBetween('schedule', [Carbon::now()->subMonths(4), Carbon::now()->subMonths(3)])

And the list goes on similar to above differences. And I don't want to implement multiple foreach loops, is there any way out with Laravel Collection where I can manipulate such data the get the output. Please guide me. Thanks.

Edit: Let me explain in better way, currently I'm using this code to get my desired data:

public function investorGraphData(Request $request)
{
    $weekInvestorData = $weekResearchData = [];

    if($request->timeFormat == 'month')
    {
        for($i=0; $i<6; $i++)
        {
            $meetings = Company::where('name', $request->client)
                ->withCount(['interactions as investors'=> function ($q) use($i) {
                    $q
                        ->whereBetween('schedule', [Carbon::now()->subMonth($i+1), Carbon::now()->subMonth($i)])
                        ->whereHas('contactsAssociation', function ($q) {
                            $q->whereHas('company', function ($q) {
                                $q->where('type', 'like', 'Investor');
                            });
                        });
                }])
                ->withCount(['interactions as research' => function($q) use($i) {
                    $q
                        ->whereBetween('schedule', [Carbon::now()->subMonth($i+1), Carbon::now()->subMonth($i)])
                        ->whereHas('contactsAssociation', function ($q) {
                            $q->whereHas('company', function ($q) {
                                $q->where('type', 'like', 'Research');
                            });
                        });
                }])
                ->get();
            $weekInvestorData[] = $meetings[0]->investors_count;
            $weekResearchData[] = $meetings[0]->research_count;
        }

        return response()->json(['investor' => $weekInvestorData, 'research' => $weekResearchData], 200);
    }
    elseif ($request->timeFormat == 'week')
    {
        for($i=0; $i<6; $i++)
        {
            $meetings = Company::where('name', $request->client)
                ->withCount(['interactions as investors'=> function ($q) use($i) {
                    $q
                        ->whereBetween('schedule', [Carbon::now()->subWeek($i+1), Carbon::now()->subWeek($i)])
                        ->whereHas('contactsAssociation', function ($q) {
                            $q->whereHas('company', function ($q) {
                                $q->where('type', 'like', 'Investor');
                            });
                        });
                }])
                ->withCount(['interactions as research' => function($q) use($i) {
                    $q
                        ->whereBetween('schedule', [Carbon::now()->subWeek($i+1), Carbon::now()->subWeek($i)])
                        ->whereHas('contactsAssociation', function ($q) {
                            $q->whereHas('company', function ($q) {
                                $q->where('type', 'like', 'Research');
                            });
                        });
                }])
                ->get();
            $weekInvestorData[] = $meetings[0]->investors_count;
            $weekResearchData[] = $meetings[0]->research_count;
        }
        return response()->json(['investor' => $weekInvestorData, 'research' => $weekResearchData], 200);
    }
    else
        return response()->json(['message' => 'No input given'], 200);
}

Is there any way to shorten this code through collection?

Answer 1

you may try to give column as different name,

For example :

$meetings = Company::where('name', 'Atlanta Ltd')
    ->withCount(['interactions as investors'=> function ($q) {
        $q
            ->whereBetween('schedule as schedule6to5', [Carbon::now()->subMonths(6), Carbon::now()->subMonths(5)])
            ->whereBetween('schedule as schedule5to4', [Carbon::now()->subMonths(5), Carbon::now()->subMonths(4)])
            ->whereBetween('schedule as schedule4to3', [Carbon::now()->subMonths(4), Carbon::now()->subMonths(3)])
            ->whereHas('contactsAssociation', function ($q) {
                $q->whereHas('company', function ($q) {
                    $q->where('type', 'like', 'Investor');
                });
            });
    }])->get()
    ->transform(function ($company) {
        $company->investors_count = $company->interactions
            ->pluck('investors_count')
            ->sum();
        return $company;
    });

May be this will work.