Strange sql behavior when using variables

Queries and Resources
1

I have a simple MySQL query. Something like this:

`SELECT COUNT(*) AS lead_count WHERE leads.campaign_id = '12345' and leads.deleted = 0 AND leads.date_entered BETWEEN '2024-08-01 05:00:00' AND '2024-08-28 04:59:59' ORDER BY lead_count DESC;`

This works fine if I leave it just as is. I then introduced some variables for the start time and end time (just because of how our DB is) and so I changed the variable in the mysql to this:

`SELECT COUNT(*) AS lead_count WHERE leads.campaign_id = '12345' and leads.deleted = 0 AND leads.date_entered BETWEEN {{ dateRange1.value.start + " " + varStartTime.value.trim() }} AND {{ dateRange1.value.end + " " + varEndTime.value.trim() }} ORDER BY lead_count DESC;`

And the above works just fine. Now, I have a scenario where IF the user doesn't want to look at a singular campaign but, they want to look at ALL campaigns, I need to remove the very first where clause which is removing the "leads.campaign_id = '12345 and" string.

So, at first, I created a simple variable so I could change it dynamically. That didn't work and brought back blank data.

`SELECT COUNT(*) AS lead_count WHERE {{ varCampaignIDs }} leads.deleted = 0 AND leads.date_entered BETWEEN '2024-08-01 05:00:00' AND '2024-08-28 04:59:59' ORDER BY lead_count DESC;`

Where, "varCampaignIDs" just = 'leads.campaign_id = '12345' AND'

And that didn't work either. Blank response again.

I then dynamically inserted an "ALL" value in a dropdown I was having the user to choose from. So, I ended up with this sql:

`SELECT COUNT(*) AS lead_count WHERE {{ select1.value = '5555' ? '': "leads.campaign = " + select1.value + " AND "}}  leads.deleted = 0 AND leads.date_entered BETWEEN '2024-08-01 05:00:00' AND '2024-08-28 04:59:59' ORDER BY lead_count DESC;`

The above is not working either and brings back errors. No matter what I try (and I have tried plenty of things) it just won't allow me to change the mysql with a variable for some reason but it is fine if I use other variables. Scratching my head on this one. Any thoughts or ideas? Thanks!

2

Hi @macphreak,

I think the issue might be with your ternary logic. You've got select1.value = '5555', but in JS = is for assignment, not comparison. Try changing it to select1.value === '5555', also double check the state of select1.value to confirm it's a string, since that's what you're comparing against ('5555' isn't the same as 5555)

3

My apologies. I was re-typing that example quickly. In my actual query it is the “==“ operator (usually it will be a mix of numbers and letters that it will comparing)

4

For anyone else who looks around for a solution like this - I ended up abandoning trying to manipulate the mysql. It just would not work (for me at least).

I ended up making another resource that just brought back ALL the data I needed and then I changed the data source of the table to be dynamic and added this code:

{{  select1.value === '12345' ? getALLLeadCounts.data : getLeadCounts.data }}
5

@macphreak Thank you for sharing you solution with the community!