I am using MySQL 5.7 and trying to emulate rank(which is not available in 5.7) using JSON variables.
The problem is that in Laravel DB or PDO query below doesn't work correctly but works fine in TablePlus.
The idea of a query to count rows by groups and filter rows which have counter less than 250. Every group rows have it own counters in json variable.
The idea of this query:
select result.*
from (
select
@extracted_counter:=JSON_EXTRACT(@row_number, CONCAT('game', t.game_id)) as extracted_counter,
JSON_SET(@row_number, CONCAT('key', t.game_id), @extracted_counter + 1) as counters,
t.*
from (@row_number := '{"game1": 0, "game27": 0}') as x,
table_name as t
where t.app_id = 16325
) as result
where result.extracted_counter < 250;
@json_variable build from another table. I made it as a string to simplify.
Again, the original query is working ok in TablePlus program but when it comes to Laravel 5.4 DB, @row_number can't perform addition more than 3, for instance: "{"game27718": 0,"game27398": 3,"game26592": 0,"game24042": 0,"game23957": 3}"
I thought that Laravel uses specific PDO settings but couldn't find anything. Also, I've tried to use CAST but it didn't help really since addition is working for first three records.
Not really sure how is different settings for TablePlus and PDO Laravel. I've tried to google settings of TablePlus but I can't imagine how can I google it correctly to not bump into interface docs.
I would appreciate any help.
The original query I am trying to use looks like this:
SELECT temp.*
FROM (
SELECT
@extracted_counter:=CAST(JSON_EXTRACT(@row_number, CONCAT('$.game', sao.game_id)) AS UNSIGNED) AS extracted_rank,
if(
@extracted_counter < @games_length,
@row_number:=JSON_SET(
@row_number,
CONCAT('$.game', sao.game_id),
@extracted_counter + 1
),
@const_length
) AS rnk,
sao.email, sao.name,
sao.game_id, sao.order_total_price,
sao.device_type, sao.coupon_id,
sao.coupon_code, sao.browser,
sao.city, sao.country,
sao.order_id, sao.created_at
FROM (SELECT @row_number := (
SELECT concat('{', group_concat(concat('"game', s.game_id, '"'), ': 0'), '}') AS str_json
FROM (
SELECT g.id AS game_id
FROM users AS u
LEFT JOIN games AS g ON u.id = g.user_id
WHERE u.app_id = 16325
AND g.deleted_at IS NULL
) AS s
)) AS x,
(SELECT @games_length := 250) AS games_length,
(SELECT @const_length := @games_length + 100) AS const_length,
shopify_app_orders as sao
WHERE sao.app_id = 16325
AND sao.coupon_id > 0
ORDER BY sao.id DESC
) AS temp
LEFT JOIN coupons AS c ON c.id = temp.coupon_id
WHERE temp.extracted_rank < @games_length
via
Chebli Mohamed
