Optimise 'firstOrCreate()' removing first 'select'

user_6368631 0
Viewed 97

I need to insert a lot of data and sometimes (1% or 2% of insert) the data is already present into DB. To manage this 1% or 2% of cases, I'm using the firstOrCreate().

The problem is that using firstOrCreate(), Eloquent performs always two queries:

  • select, to check if the data already exists
  • insert, If the data doesn't exists.

I'd like optimise the code removing first select (that returns the Model only on 1% or 2% of data) and the idea is create a code like:

try {
    $output = MyModel::create(['field1' => 'value1']);
} catch(\Illuminate\Database\QueryException $e) {
    if($e->getCode() == 1062) { // MySQL duplicate key code
        $output = MyModel::where('field1', 'value1')->first();
    }
}
$outputId = $output->id;

What is the best practice to do this? Is there an Eloquent method?

Thank you.

laravel-8 eloquent laravel database
1 Answers

Unfortunately, there are no methods like that.

NOTE

I've already developed the following packages:

I think these packages are sufficient for general purposes. Reducing SELECT queries might result in so little performance improvements.

However, if you are really willing to provide INSERT-before-SELECT optimization, please require mpyw/laravel-unique-violation-detector and implement a marco method on Eloquent Builder named like createOrFirst() to publish it as a package.

Example Implementation
<?php

namespace /* ... */;

use Mpyw\LaravelUniqueViolationDetector\Contracts\UniqueViolationDetector;
use /* ... */;

class EloquentCreateOrFirstServiceProvider extends ServiceProvider
{
    public function boot(UniqueViolationDetector $detector): void
    {
        Builder::macro('createOrFirst', function (array $attributes = [], array $values = []) use ($detector): Model {
            \assert($this instanceof Builder);
            return (new CreateOrFirstMacro($detector, $this))($attributes, $values);
        });
    }
}

<?php

namespace /* ... */;

use Mpyw\LaravelUniqueViolationDetector\Contracts\UniqueViolationDetector;
use /* ... */;

class CreateOrFirstMacro
{
    public function __construct(
        private UniqueViolationDetector $detector,
        private Builder $query,
    ) {
    }

    public function __invoke(array $attributes = [], array $values = []): Model
    {
        try {
            return (clone $this->query)->create($values + $attributes);
        } catch (PDOException $e) {
            if ($this->detector->forConnection($this->query->getConnection())->violated($e)) {
                return (clone $this->query)->where($attributes)->firstOrFail();                
            }
            throw $e;
        }
    }
}

<?php

// _ide_helper.php (barryvdh/laravel-ide-helper format)

if (false) {
    class Eloquent extends \Illuminate\Database\Eloquent\Model
    {
        /**
         * @return \Illuminate\Database\Eloquent\Model|static
         */
        public static function createOrFirst(array $attributes = [], array $values)
        {
        }
    }
}
Example Usage
$model = YourModel::createOrFirst(['field1' => 'value1']);
user_18465620
Related