2 years ago

Performant way to mass "update or create"

Posted 2 years ago by pilat

Hi, I'm looking for a way to do something like updateOrCreateMany (imaginary method).Currently, the code looks like this:

class ActivityRepositoryEloquent extends BaseRepository implements ActivityRepository
    // ... before this method, that was something like:
    //  $this->model
           ->whereIn('element_id', array_column($records, 'element_id')
           ->update([ 'delete' => true ]);

    public function insertMany($records)
        $searchFields = [

        foreach ($records as $record) {
            $record['deleted'] = false;

                array_only($record, $searchFields),
                array_except($record, $searchFields)

What it does, in fact, is the following: it makes SELECT * …nd then `UPDATE ?or each of the records!

Is there a way to minimize the number of SQL queries?

A comment about that 'deleted' field: I'm syncing data with a 3rd-part service. For the original data, let's call is "leads", there is id field defined by 3rd-party system. So, I simply store it in my database with that ID and have no issues. There is related data, however, like "custom fields", that I (re)generate in my site, basing on the leads data. Here I have auto-incremental id and I've noticed that my genius schema (deleting all related and re-generating it after leads sync) causes ids to grow higher and higher after each sync.

So, the revised schema is the following:

  1. Set deleted = false for all the custom fields, with element_id IN ([ array of lead ids ])

  2. updateOrCreate custom fields, basing on leads data + hardcoded deleted = 0

  3. Delete all the records that have deleted = 1 remaining;

Please sign in or create an account to participate in this conversation.