updateOrCreate() with a minimum of DB queries

Anyone?

It depends if the majority are creates or updates.

If for instance you have unique column then you could just try the insert and if you get an error back (because of a duplication) then catch it in try-catch block and issue an update instead.

But you would not want to do this if the majority are updates. In which case do it the other way around. Update as if it an existing record and catch the error and convert to a create.

Otherwise, you are probably stuck with the query to check for existence then the update or create.

And make sure the column you are using to find the record is indexed

Hi @Snapey

I do an insert at the beginning and indeed it will be more often updates then inserts.

You give a good idea but is there a way to compare arrays/collections and update or insert accordingly? I want to make as minimum DB calls as possible (for the sake of trying).

Will keep tinkering on it.

Thank you

Sure you could grab all associated rows in one go and then find in collection or array intersect

Its a bit hard to envisage without actual code but supposing you had to update a completely random set of rows, then you would probably need to pluck all the identifier keys and do a whereIn query on the database

Thank you for the input, I will "play" a bit so I can come back with some code...

If you have a unique index, that doesn't use the pKey, you could use mySQL's ON DUPLICATE KEY UPDATE (docs).

For example, I had the need to insert/update a user's entity tags, based on user input. Here is a sample table:

Sorry, cant get markdown table to display properly

| id | userId | entityType | tagName | createdDate | updatedDate | | ---- | --------- | ------------ | --------- | --------------------- | -------------------- | | 1 | 23 | company | west | 2017-01-01 00:01:23 | 2017-01-01 00:01:23 | | 2 | 23 | brand | west | 2017-02-01 13:25:03 | 2017-02-01 13:25:03 |

I built a unique index using userID_entityType_tagName and built a custom helper named UpdateOrCreate()that will return a DB::statement for mass updating.

Example:

$insertData = [
    [
        'userID'=>23, 
        'entityType'=>'brand', 
        'tagName'=>'west',
        // Need to manually create timestamps
        // Not automatic during mass inserts
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s")
    ],
    [
        'userID'=>23, 
        'entityType'=>'person', 
        'tagName'=>'automotive',
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s")
    ]
]
$userTag = new UserTag;
UpdateOrCreate::records($userTag, $insertData);

Returns DB::statement:

INSERT INTO entityTags (userID, entityType, tagName) 
VALUES 
    (23, 'brand', 'west'),
    (23, 'person', 'automotive')
ON DUPLICATE KEY UPDATE 
    `userID`=VALUES(`userID`), 
    `entityType`=VALUES(`entityType`), 
    `tagName`=VALUES(`tagName`);

If you are using mySQL and if you can set up a unique index, this may be a good solution. If you think this may work for you, I can post the helper class UpdateOrCreate that I built.