Dynamically change dates using a variable

Queries and Resources
1

Hello everyone,

I'm trying to make the date range in my query dynamic based on the user's selection using the date range component ('dateRangePicker2'). This approach as worked for me in another query, but I'm encountering an issue here.

Here's the query:

SELECT
  query,
  SUM(impressions) AS Impressions,
  SUM(clicks) AS Clicks,
  'Branded' AS BvNB
FROM
  SearchConsole sc
WHERE
  dId = 105
  AND startDate >= {{ dateRangeStart2.value }}
  AND startDate < {{ dateRangeEnd2.value }}
  AND query REGEXP '(h|H)ello(?!?)(world|planet)?'
GROUP BY
  query
UNION ALL
SELECT
  startDate,
  query,
  SUM(impressions) AS Impressions,
  SUM(clicks) AS Clicks,
  'Non Branded' AS BvNB
FROM
  SearchConsole sc
WHERE
  dId = 105
  AND startDate >= {{dateRangeStart2.value}}
  AND startDate < {{dateRangeEnd2.value}}
  AND query NOT REGEXP '(h|H)ello(?!?)(world|planet)?'
GROUP BY
  query

I've defined "dateRangeStart2" and "dateRangeEnd2" as variables:

"dateRangeStart2":

{{ dateRangePicker2.value.start ? dateRangePicker2.value.start : moment().subtract(2, 'months').startOf('month').format('YYYY-MM-DD') }}

"dateRangeEnd2":

{{ dateRangePicker2.value.end ? dateRangePicker2.value.end : moment().subtract(2, 'months').format('YYYY-MM-DD') }}

However, when I run the query, I get this error:

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 '2024-06-01'r( |e)'2024-06-21'front' GROUP BY query, startDate UNION ALL S...' at line 12

I've replaced AND startDate >= {{dateRangeStart2.value}} AND startDate < {{dateRangeEnd2.value}} with static dates(I.E. "2024-06-01" AND "2024-06-31" and the query ran. I'm not sure what I'm doing wrong here. Thanks in advance!

1 Like
2

Hi @chris.mendez - welcome to the forums. I'm not real familiar with MariaDB, but have you tried taking the variables out of the equation and just using your definition for them in the query itself?

SELECT
  query,
  SUM(impressions) AS Impressions,
  SUM(clicks) AS Clicks,
  'Branded' AS BvNB
FROM
  SearchConsole sc
WHERE
  dId = 105
  AND startDate >= {{ dateRangePicker2.value.start ? dateRangePicker2.value.start : moment().subtract(2, 'months').startOf('month').format('YYYY-MM-DD') }}
  AND startDate < {{ dateRangePicker2.value.end ? dateRangePicker2.value.end : moment().subtract(2, 'months').format('YYYY-MM-DD') }}
  AND query REGEXP '(h|H)ello(?!?)(world|planet)?'
GROUP BY
  query
UNION ALL
SELECT
  startDate,
  query,
  SUM(impressions) AS Impressions,
  SUM(clicks) AS Clicks,
  'Non Branded' AS BvNB
FROM
  SearchConsole sc
WHERE
  dId = 105
  AND startDate >= {{ dateRangePicker2.value.start ? dateRangePicker2.value.start : moment().subtract(2, 'months').startOf('month').format('YYYY-MM-DD') }}
  AND startDate < {{ dateRangePicker2.value.end ? dateRangePicker2.value.end : moment().subtract(2, 'months').format('YYYY-MM-DD') }}
  AND query NOT REGEXP '(h|H)ello(?!?)(world|planet)?'
GROUP BY
  query

Also, did you confirm the JS code result is a string in the expected format (by just pasting the JS code into the console, for example)? No reason to believe it wouldn't be

1 Like
3

Hi @jg80 - Thanks for your help! I really appreciate it!

I get the same error when I use the definition instead of the cariable. The only time it runs without an error is when I make the startDate in the second WHERE clause static. For example:

SELECT
  query,
  SUM(impressions) AS Impressions,
  SUM(clicks) AS Clicks,
  'Branded' AS BvNB
FROM
  SearchConsole sc
WHERE
  dId = 105
  AND startDate >= {{ dateRangePicker2.value.start ? dateRangePicker2.value.start : moment().subtract(2, 'months').startOf('month').format('YYYY-MM-DD') }}
  AND startDate < {{ dateRangePicker2.value.end ? dateRangePicker2.value.end : moment().subtract(2, 'months').format('YYYY-MM-DD') }}
  AND query REGEXP '(h|H)ello(?!?)(world|planet)?'
GROUP BY
  query
UNION ALL
SELECT
  startDate,
  query,
  SUM(impressions) AS Impressions,
  SUM(clicks) AS Clicks,
  'Non Branded' AS BvNB
FROM
  SearchConsole sc
WHERE
  dId = 105
  AND startDate >= "2024-06-01"
  And startDate < "2024-06-31"
  AND query NOT REGEXP '(h|H)ello(?!?)(world|planet)?'
GROUP BY
  query

I don't know why that's the case.

1 Like
4

Hi @chris.mendez - welcome to the community!

If I understand correctly, the first set of data conditionals throws an error initially but there are no errors if you hard-code the data conditionals in the second? That is very odd. I don't have any knowledge of MariaDB nuances, but my first thought is to try wrapping the interpolated date values in double quotes, as that is the way you've hard-coded it so that it runs successfully.

Digging into this a bit deeper, do you know the format of your startDate column? Is it a DATE or DATETIME?

2 Likes
5

Hi @Darren -

Thanks for your response! That's correct - if I hard-code the dates in the second select statement then the query will run.

I actually already tried wrapping the date values in double quotes and that didn't help.

The format of the startDate column is a Date.

I'm not sure what to do here. It seems to me like this is a bug in Retool.

2 Likes
6

Yeah - it's starting to seem that way. Have you configured this as a MySQL resource in Retool? My understanding is that MariaDB is very similar. One other thing you can try is disabling prepared statements via the resource settings. Their primary purpose is to prevent SQL injections but I've seen it unpredictably mess with the format of interpolated values, as well.

image

If that doesn't work, I think the next step would be to pare down the actual content of your query one line at a time in order to determine more precisely which part is causing issues. Can you also share the configuration of your resource and let me know if you're on a cloud or self-hosted deployment? If the latter, which version?

1 Like