Hello I have a Table With millions of records and I'm Trying To Get Model Data By Relation but it's too slow using where has

My Code V1

$influencers = Brand::query()->with('campaignBrandInfluencers.influencer', 'campaignBrandInfluencers.campaign')->whereHas('campaignBrandInfluencers', function ($q) {
            $q->whereIn('status', [CampaignCases::VISIT, CampaignCases::CONFIRMATION, CampaignCases::COVERAGE]);
            $q->whereHas('influencer', function ($q) {
                $q->whereNull('deleted_at');
            });
            $q->whereHas('campaign', function ($q) {
                $q->where('status', 1);
            });
        })->coverageFilter($filter)->orderBy('id', 'desc')->paginate(10);

i have Tried To make it in 2 steps

 return $campaignInfluencers = CampaignInfluencer::query()->whereIn('status', [CampaignCases::VISIT, CampaignCases::CONFIRMATION, CampaignCases::COVERAGE])
            ->whereNotNull('brand_id')->where('brand_id', '>', 0)
            ->with('influencer', 'campaign')
             ->whereHas('influencer', function ($q) {
                $q->whereNull('deleted_at');
            })
            ->whereHas('campaign', function ($q) {
                $q->where('status', 1);
            })
            ->distinct() // Use distinct to select unique brand_id values
            ->pluck('brand_id') // Group by brand_id
            ->toArray();


 $influencers = Brand::query()->with('campaignBrandInfluencers.influencer', 'campaignBrandInfluencers.campaign')->whereIn('id',$campaignInfluencers)->coverageFilter($filter)->orderBy('id', 'desc')->paginate(10);

it takes about 40seconds to a minute so I can retrieve data

and yes I have added an index in columns I search by it

any idea How To Make it batter?

CodePudding user response：

You can use the query builder instead of the eloquent orm. See the laravel docs under database -> query builder