Increment column on duplicate for SQLite & MySQL

Question

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 + 1 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?

Have you tried enclosing the field name by backticks? \DB::raw('`counter` + 1') Both kysql and sqlite use backticks for identifier names. — Shadow
– Shadow, Commented Oct 15, 2019 at 19:38
@Shadow thanks for the idea, I tried it but I still get the same error — Adam
– Adam, Commented Oct 15, 2019 at 21:53
I'm not familiar with laravel, but is this a bug in the sqlite implementation for laravel? Do you actually change libraries and/or set a flag indicating the type of backend when switching from mysql to sqlite? I ask, because something similar to your sqlite custom solution must certainly be generated for MySQL to properly handle new inserts vs. updates. Can you get the command/script generated for mysql to compare? For instance, mysql supports INSERT IGNORE whereas sqlite requires INSERT OR IGNORE and its strange that it doesn't properly choose between the two. — C Perkins
– C Perkins, Commented Oct 15, 2019 at 23:51
@CPerkins I don't think its a bug, I think its just not supported. I have a envirement file, and there I can change the database_driver. For testing I would like SQLite because its much quicker. The MySQL query is identical to SQLite query for insert, they both get nsert into "ice_cream" ("user_id", "counter") values (12,counter + 1) but it only works for MySQL. insertOrIgnore is only working since Laravle v5.8.33. — Adam
– Adam, Commented Oct 16, 2019 at 8:55

Adam · Accepted Answer · 2019-10-16 08:57:24Z

1

Found a way that works for both MySQL and SQLite:

$iceCream = static::firstOrNew([
                'user_id' => $user_id,
            ], 
            ['counter' => 0]);

$iceCream->counter++;

$iceCream->save();

answered Oct 16, 2019 at 8:57

Adam

29.6k30 gold badges187 silver badges281 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Increment column on duplicate for SQLite & MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related