Minato Posts - Answer Overflow

Minato

•Created by Minato on 6/7/2024 in #help-and-questions

using variable in queryRaw not working

Hi I have this code :

const date = "2024-06-07"
const regionCode = "US"
const dateParam = `[{"date": "${date}"}]`;

SELECT * FROM "Video" WHERE
    "trendingRanks" @> ${dateParam}::jsonb
    AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements("Video"."trendingRanks") elem
      WHERE elem -> 'date' = '"2024-06-07"' AND (elem -> 'rank') ? ${regionCode}
    )
    ORDER BY
  (
    SELECT (elem -> 'rank' ->> '${regionCode}')::int
    FROM jsonb_array_elements("Video"."trendingRanks") elem
    WHERE elem -> 'date' = '"${date}"' AND (elem -> 'rank') ? '${regionCode}'
    LIMIT 1
  );

const date = "2024-06-07"
const regionCode = "US"
const dateParam = `[{"date": "${date}"}]`;

SELECT * FROM "Video" WHERE
    "trendingRanks" @> ${dateParam}::jsonb
    AND EXISTS (
      SELECT 1
      FROM jsonb_array_elements("Video"."trendingRanks") elem
      WHERE elem -> 'date' = '"2024-06-07"' AND (elem -> 'rank') ? ${regionCode}
    )
    ORDER BY
  (
    SELECT (elem -> 'rank' ->> '${regionCode}')::int
    FROM jsonb_array_elements("Video"."trendingRanks") elem
    WHERE elem -> 'date' = '"${date}"' AND (elem -> 'rank') ? '${regionCode}'
    LIMIT 1
  );

it work fine when i use it in $queryRaw the problem is if I change the 2024-06-07 that exist in the first WHERE to the variable ${date} it stop working

WHERE elem -> 'date' = '"${date}"'

WHERE elem -> 'date' = '"${date}"'

PS: the second condition(in ORDER BY) I'm using the same variable and it work fine but in the first one it only work when I'm using a static value any ideas ?

1 replies

Gaming

Programming