Ad

Laravel DB Run Two Queries At Once (SELECT/UPDATE)

I have one table which stores some records as JSON. I'm trying to increment the numeric value of a key from JSON column. Below is the query I'm executing in Laravel using DB facade.

DB::connection()
  ->select("

    SELECT CAST(JSON_EXTRACT(consumed, '$.max_users') AS INT) INTO @tmp
    FROM subscriptions
    WHERE `id` = '2';

    UPDATE subscriptions  
    SET consumed = JSON_SET(consumed, '$.max_users', @tmp+1)
    WHERE `id` = '2'; 

");

It works perfectly ok in PhpMyAdmin however when attempting to execute it from Laravel it throws an exception, that SQL syntax is incorrect (where is it incorrect?).

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE subscriptions SET consumed = JSON_SET(consumed, '$.max_users', ' at line 5 (SQL: SELECT CAST(JSON_EXTRACT(consumed, '$.max_users') AS INT) INTO @tmp FROM subscriptions WHEREid= '2'; UPDATE subscriptions SET consumed = JSON_SET(consumed, '$.max_users', @tmp+1) WHEREid= '2'; )

My suspicion is select() does not allow two different queries to be ran at once. However in this particular case I need them so in order to make use of @tmp variable. I could retrieve the value of first query and pass it to the second one using PHP, but for the sake of argument, how to run two DB selects at once in Laravel? 🤔

Ad

Answer

You can use a single update query to increment this value:

UPDATE subscriptions  
    SET consumed = JSON_SET(consumed, '$.max_users', CAST(JSON_EXTRACT(consumed, '$.max_users') AS INT) + 1)
    WHERE `id` = '2'; 
Ad
source: stackoverflow.com
Ad