Complicated eloquent queries

Here is a sample of a complicated query that includes where and with and inner queries. This snuppet is the one used to get the legislative event stream on the dashboard in the GovIQ project.

$memberId = Auth::user()->id ;

$allResultsArray = [];

$months = 36 ; //Please set this to 2 months when the feeds works perfectly, Now three years.

$dateLimit = time() - $months*31*24*60*60 ; //Two Months Earlier

$dateLimit = date("Y-m-d 00:00:00",$dateLimit) ;



$qqAllNewProvincial = ProvincialBillModel::with([

    'events'=>function($eventQ2) use ($dateLimit){

        ###$eventQ2->where('date', '>=', "$dateLimit")->groupby('bill_id')->orderBy('date','desc');

        //$eventQ2->where('date', '>=', "$dateLimit")->orderBy('date','desc')->first();

        $eventQ2->where('date', '>=', "$dateLimit")->orderBy('date','desc');

        }

])->whereHas(

            'events',function($eventQ) use ($dateLimit){

                $eventQ->where('date', '>=', "$dateLimit");

    })->whereHas(

            'favorites',function($favoriteQ) use ($memberId){

                $favoriteQ->where('user_id',$memberId);

            }

)->get();







    $qqAllNewFederal = FederalBillModel::with([

    'events'=>function($eventQ2) use ($dateLimit){

        ###$eventQ2->where('date', '>=', "$dateLimit")->groupby('bill_id')->orderBy('date','desc');

        //$eventQ2->where('date', '>=', "$dateLimit")->groupby('bill_id')->orderBy('date','desc');

        $eventQ2->where('date', '>=', "$dateLimit")->orderBy('date','desc');



        //$eventQ2->where('date', $eventQ2->max('date'))->where('date', '>=', "$dateLimit")->groupby('bill_id');

        ###$eventQ2->where('date', '>=', "$dateLimit")->orderBy('date','desc')->first();

        }

    ])->whereHas(

            'events',function($eventQ) use ($dateLimit){

                $eventQ->where('date', '>=', "$dateLimit");

    })->whereHas(

            'favorites',function($favoriteQ) use ($memberId){

                $favoriteQ->where('user_id',$memberId);

            }

    )->get(); 



$finalMergedResults = $qqAllNewFederal->merge($qqAllNewProvincial);

Another Sample is as follows :

$records = BillEventModel::where('date', '<', "$date100BeforeStartdate")->with(['bill', 'provincialBill'])

->whereHas(

    'bill',function($billQ) use ($date100BeforeStartdate){

        $billQ->whereHas(

            'lastEvent',function($QQe) use ($date100BeforeStartdate) {

                $QQe->where('date', '<', $date100BeforeStartdate); 

            }

        );

})->orWhereHas(

    'provincialBill',function($billQ) use ($date100BeforeStartdate){

        $billQ->whereHas(

            'lastEvent',function($QQe) use ($date100BeforeStartdate) {

                $QQe->where('date', '<', $date100BeforeStartdate); 

            }

        );

})

->orderBy('updated_at','desc')

->get()

->unique('bill_id', 'eventType');

->toArray();

And here is a new one as well:

$finalMergedResults = collect();



//FEDERAL BILLS

$records = new \App\Bill();

$records = $records->newQuery();

//$records->with(['bill', 'bill.sponsor', 'bill.sponsorSenator', 'provincialBill']);

$records->with('lastEvent');



$records->whereHas(

    'lastEvent',function($QQe) use ($date100BeforeStartdate) {

        $QQe->where('date', '<', $date100BeforeStartdate); 

    }

);

//$records->with('events');

$records = $records->get()->toArray();

$finalMergedResults = $finalMergedResults->merge($records);





//PROVINCIAL BILLS

$recordsProvincial = new \App\ProvincialBill();

$recordsProvincial = $recordsProvincial->newQuery();

//$recordsProvincial->with(['bill', 'bill.sponsor', 'bill.sponsorSenator', 'provincialBill']);

$recordsProvincial->with('lastEvent');



$recordsProvincial->whereHas(

    'lastEvent',function($QQe) use ($date100BeforeStartdate) {

        $QQe->where('date', '<', $date100BeforeStartdate); 

    }

);

//$recordsProvincial->with('events');

$recordsProvincial = $recordsProvincial->get()->toArray();

$finalMergedResults = $finalMergedResults->merge($recordsProvincial);



//$finalMergedResults = $finalMergedResults->sortByDesc('updated_at');

Mohammed Anwar

Experienced technical lead PHP, MySQL and Laravel Developer for 15+ years, with proven ability to develop and create high-quality and optimized web applications. Great ability to build and optimize database design, schema and queries. Versed programing trainer and instructor delivering web courses and helping others to get into the field in a timely manner. Fast and eager learner for new technologies .