Eloquent query question using same collection

Hi. Im trying to use the same collection to reduce the speed and optimise the site a bit and hitting an issue. Im not sure if theres a way to do this or not, I know the issue I'm facing with it but trying to figure it out slowly.

So I want to grab the Ledgers associated with that user, then I need to split out the transaction types into their own variable for the frontend. I think I know whats happening at the moment is that its running the transactionType() twice and applying it to the query. Is there a way to use something like the refresh() function on this. theres a load of different transactions I need to get from the database, and wanted to try and see if i can reduce the number of queries to the database.

Cheers

$ledgers = Ledger::where('user_id', $user_id)
			->between($start, $finish)
			->orderBy('created_at', 'desc');
	
$scans = $ledgers->transactionType([4])->get();
$sms_alerts = $ledgers->refresh()->transactionType([12])->get();

dd($sms_alerts);

skoobi

1 year ago

Level 13

Sorry, figured it out. Im modifying the query. If I call the ->where('transaction_id', 2) directly on the variable it works.

tykus

1 year ago

Best Answer

Level 104

You only get a Collection whenever you complete the Query, e.g. using get(). This might already be the quickest and most efficient way to fetch the data even if it means more queries. Try using clone rather than refresh to clone the $ledger Builder between Eloquent queries.

$ledgers = Ledger::where('user_id', $user_id)
			->between($start, $finish)
			->orderBy('created_at', 'desc'); // returns a Builder

$scans = $ledgers->clone()->transactionType([4])->get();
$sms_alerts = $ledgers->clone()->transactionType([12])->get();

Depending on the amount of data or time needed to query the database; you might find that working ou could fetch all of the Ledger instances for the User, between the dates, and then partition the Collection by transaction type:

$ledgers = Ledger::where('user_id', $user_id)
			->between($start, $finish)
            ->whereIn('transaction_type', [4, 12]) // scope to required types
			->orderBy('created_at', 'desc')
            ->get();

// Now you have a Collection which you can partition into sub-collections
[$scans, $sms_alerts] = $ledgers->partition(fn($ledger) => $ledger->transaction_type === 4);

// or, you can filter
$scans = $ledgers->filter(fn($ledger) => $ledger->transaction_type === 4);
$sms_alerts = $ledgers->filter(fn($ledger) => $ledger->transaction_type === 12);

skoobi

1 year ago

Level 13

@tykus Morning. I ended up using the ->filter() way as there was a lot of data. When I initially wrote the code 5-6 years ago I was querying the database for each transaction type variable, which was not the correct way. This way its brought the page load speed from 3sec to 0.5sec!

Cheers

Please or to participate in this conversation.