Increment column on duplicate for SQLite & MySQL

I have a table ice_cream with two columns a primary_key user_id and and int counter with default value 0.

I want to insert a user_id with counter value 1, and increment counter on duplicate.

I am looking for an solution with the the Laravel Database Builder for version 5.6 that works for MySQL and SQLite.

For MySQL I have this solution:

 IceCream::updateOrCreate([
                    'user_id' => $user_id,
    ],
    [
       'counter' =>  \DB::raw('counter + 1'),
    ]);

However, this won't work with SQLite. I get the following error message:

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 no such column: counter (SQL: insert into "ice_cream" ("user_id", "counter") values (12,counter + 1))

For SQLite I have also a custom solution:

\DB::select(\DB::raw('INSERT OR IGNORE INTO ice_cream (user_id,counter) VALUES (?,0) '), [$user_id]);
\DB::select(\DB::raw("UPDATE ice_cream SET counter = counter WHERE user_id= ?"), [$user_id]);

However this wont work with MySql.

There is a general insertOrIgnore method since Laravel version v5.8.33, however I am currently on Laravel v5.6 and an update is currently not possible.

It it possible to create one solution that works for both?

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2OPet2N
via IFTTT