How to avoid N + 1 queries in PHP

#laravel #php #webdev #beginners

I was reviewing a Laravel codebase a few days ago, and I saw a common N+1 query mistake. N+1 query slows down our applications unknowingly. Because things that are supposed to run at once will run 4 to 5 times.

I will be emphasizing on two N+1 query examples and their solution(s).

SCENARIO ONE:
We are trying to loop over an array to create transactions in the database.

The mistake


foreach ($request->transactions as $transaction) {
   Transaction::create([
     "transactionId" => $transaction->id,
     "amount" => $transaction->amount
     // and the rest of the code
   ]);
}

This is a classic case of N+1 query because you are creating a new record for everytime you go through a loop, and this will slow down your application or any route calling this function.

The simple solution to this will be to.

Declare an empty array before the foreach loop line.
Loop through the array and prepare to insert the record into the database.
Insert it all at once into the database.

Implementation of the explanation.

Declare an empty array before the foreach loop line

$storeTransaction = [];

Loop through the code and prepare how you will insert it

$now = now();

foreach ($request->transactions as $transaction) {
  $storeTransaction[] = [
     "transactionId" => $transaction->id,
     "amount" => $transaction->amount,
     "created_at" => $now(),
     "updated_at" => $now(),
  ];
}

Insert it all at once into the database

Transaction::insert($storeTransaction);

This approach will do one insert into the database. So instead of inserting 10 - 15 times, you inserted all the data into the database at once.

SCENARIO TWO:
You have a function to get a record and update the record, if it exists.

The mistake

public function updateTransactionStatus($id)
{
   $transaction = Transactions::where('id', $id)->get();

   if (!$transaction) {
         return $this->errorResponseHelper('fail', 'Transaction not found', 404);
   }

   Transactions::where('id', $id)->update(['status' => 1]);

   return $this->successResponseHelper('success', 'Transaction updated successfully', $transaction);
}

While this particular function or code block will do its work effectively, it is not efficient, because we are fetching records twice, then making another call to update the row/record.

The simple solution to this will be to.

Fetch the record by using the GET keyword.
If the record does not exist, return an error message
If it exists, just use the model or record that you have called already to update the record.

public function updateTransactionStatus($id)
{
   $transaction = Transactions::where('id', $id)->get();

   if (!$transaction) {
      return $this->errorResponseHelper('fail', 'Transaction not found', 404);
   }

   $transaction->update(['status' => 1]);

   return $this->successResponseHelper('success', 'Transaction updated successfully', $transaction);
}