Laravel - efficient way to check database parameters before insert

Question

I have populate a form of which every text field generated is based on the database result. I simply name every text field using the id. Now when the form is filled, I use controller to save it. But prior to insert the database, I loop the Request::input() to check every item whether such entry is exist or not. I just wonder if there is efficient way to check every item in the loop to insert it into db. Here is my code

public function store(Request $request, $id, $inid)
{       
    $startOfDay = Carbon::now()->startOfDay();
    $endOfDay = Carbon::now()->endOfDay();

    $instruments = InstrumentReading::whereBetween('created_at', [$startOfDay, $endOfDay])
                                    ->where('iv_inid', '=', $inid)
                                    ->get();
    foreach ($request->input() as $k => $v) {
        $read = new InstrumentReading;
        $read->iv_inid = $inid;
        $read->iv_ipid = $k;
        $read->iv_usid = Auth::user()->id;
        $read->iv_reading = $v;
        $read->save();
    }
    if ($instruments->count() > 0) {            
        //to filter the iv_ipid...
        foreach($instruments as $instrument)
        {
            $instrument->iv_status = "VOID";
            $instrument->save();
        }
    }

}

Answer 1

In words of efficent approach what you can do is to simple check / fetch ONLY all posible rows from the database, and the check in the loop if the row was already inserted. Also fetch only iv_ipid column, as we do not need all columns from the table to do our check. It will be faster to select only the column we need. You can use directly Fluent (Query Builder) over Eloquent to pull the data from database as it greatly increase the performance for a simple query like this.

public function store(Request $request, $id, $inid)
{
    // Search only records with submitted iv_ipid, iv_inid and created today
    $alreadyInserted = DB::table('instrument_readings')
                   ->whereBetween('created_at', [
                       Carbon::now()->startOfDay(), 
                       Carbon::now()->endOfDay()
                   ])
                   // Get only records with submitted iv_ipid
                   ->whereIn('iv_ipid', array_keys($request->input()))
                   // Get records with given iv_inid only
                   ->where('iv_inid', $inid)
                   // For our check we need only one column, 
                   // no need to select all of them, it will be fast
                   ->select('iv_ipid')
                   // Get the records from DB
                   ->lists('iv_ipid');

    foreach ($request->input() as $k => $v) {
        // Very simple check if iv_ipid is not in the array
        // it does not exists in the database
        if (!in_array($k, $alreadyInserted)) {
            $read = new InstrumentReading;
            $read->iv_inid = $inid;
            $read->iv_ipid = $k;
            $read->iv_usid = Auth::user()->id;
            $read->iv_reading = $v;
            $read->save();
        } else {
            //todo
        }
}

This is the most efficent way suggested until now, because you fetch at once only the records you are interested in, not all records from today. Also you fetch only one column, the one that we need for out check. Eloquent ususlally give a lot of overheat on the perfomance, so in the suggested code I use directly Fluent, which will boost the speed this part of code is executed by ~ 20%.

Your mistake in the original code is that you are doing database call each time in a loop. When you need such a simple task as a check, never put database calls, queries etc. in a loop. It is an overkill. Instead select all needed data before the loop and then do your checks.

Now this is in case you only need to save new records to database. In case you want to manipulate each record in the loop, let's say you need to loop through each submited entry, get get the model or create it if it does not exists and then do something else with this model, the most efficent way then will be this one:

public function store(Request $request, $id, $inid)
{
    foreach ($request->input() as $k => $v) {
       // Here you search for match with given attributes
       // If object in DB with this attributes exists
       // It will be returned, otherwise new one will be constructed
       // But yet not saved in DB
       $model = InstrumentReading::firstOrNew([
           'iv_inid' => $inid,
           'iv_ipid' => $k,
           'iv_usid' => Auth::user()->id
       ]);

       // Check if it is existing DB row or a new instance
       if (!$model->exists()) {
           // If it is a new one set $v and save
           $model->iv_reading = $v;
           $model->save();
       }

    // Do something with the model here
    .....   
}

This way Laravel will check if model with the passed parameters already exist in database, and if so it will return it for you. If it does not exist, it will create new instance of it, so you can then set the $v and save to db. So you are good to go to do anything else with this model and you can be sure it exists in database after this point.

Answer 2

First approach (efficiency first)

Consider using a simple SQL INSERT IGNORE Query and use Fluent, i.e.:

• Make a composite unique key containing:

  1. iv_inid
  2. iv_ipid
  3. created_time, up to an hour granularity, this is important, because created_at might have a far greater granularity than your intended purpose, and might slow things down a bit.

• Use DB, i.e.:

DB::query( "INSERT IGNORE INTO $yourTable VALUES ( ... )" );

Pros:
- Extremely fast, all the necessary checking is done on the DB Server

Cons:
- You cannot know which values triggered a duplicate value / unique key violation, as related errors are treated as warnings.

Second approach (convenience first)

Use firstOrFail, i.e.:

$startOfDay = Carbon::now()->startOfDay();
$endOfDay = Carbon::now()->endOfDay();

// ... for

try {
    InstrumentReading::where('iv_inid', $inid)
        ->where('iv_ipid', $k)
        ->whereBetween('created_at', [$startOfDay, $endOfDay])
        ->firstOrFail();

    continue;
} catch (ModelNotFoundException $e) {
    $instrumentReading = InstrumentReading::create([
        // your values
    ]);
}

// ... endfor

Pros:
- Easy to implement

Cons:
- Somewhat slower than simple queries