How can we help?

New post
0

Using a variable in MySQL query

Avatar
Kiersten Nelthorpe
None
Follow

How do I properly use custom variables in a MySQL query as a data source? I am trying the following:

WHERE T.created >= {props.startDate.format()}

I get a SQL syntax error, so I am guessing this is not the right way to access a variable?

Date Votes

5 comments

  • 0
    Avatar
    Kiersten Nelthorpe

    I sort of got it to work by setting a default value in the Variable list inside the Advanced menu of a Klip. But it is not updating when user input updates the start date variable on the dashboard.

  • 0
    Avatar
    Meggan King

    Hi Kiersten,

    Your format is not quite correct. If your variable is called startDate, then you reference it as {props.startDate} In the Klip editor variable value and the output of the variable must be in the expected date format for your query. Please refer to this document for details: https://support.klipfolio.com/hc/en-us/articles/216183237-BETA-Working-with-dynamic-data-sources

     

    It is advised to not use dates as properties in queries. If you want to use a date in your query - such as today, yesterday, or a rolling window (previously 6 months - today), then you should use our date query parameters and not variables. 

    https://support.klipfolio.com/hc/en-us/articles/216181877-Date-parameters-in-data-source-queries

     

    Using custom variables in a data source (first example above), will generate many data source instances behind the scenes and this can impact how your database responds and handles all the requests. 

     

    Hope this helps,

    Meggan

  • 0
    Avatar
    Matteo Guerrieri

    Hi,

    I'm trying to do something similar. I'm creating a data source using a SQL query that depends on the dates selected by the user in the dashboard:

    SELECT tb.users_used_free_session * 100 / COALESCE(NULLIF(tb.users, 0), 1) as "Rate(%)"
    FROM (
    SELECT (
    SELECTCOUNT(*)
    FROM users
    WHEREdate(created_at) >='{date_start}'ANDdate(created_at) <='{date_end}'
    ) as "users",
    (
    SELECTCOUNT(*)
    FROM user_sessions us
    INNER JOIN users u ONus.user_id=u.id
    WHERE is_free_session = true AND
    checked_in = true AND
    date(u.created_at) >='{date_start}'AND
    date(u.created_at) <='{date_end}'
    ) as "users_used_free_session"
    ) as "tb"
     
    If you see, in the WHERE clause, I want to interpolate the start and end date.
     
    How can I do that?
  • 0
    Avatar
    Parker Selman

    Hi Matteo,

    Thanks for your question! 

    If your variable is called date_start, then you reference it as {props.date_start}. Additionally, in the Klip editor variable value and the output of the variable must be in the expected date format for your query. Please refer to this document for details: https://support.klipfolio.com/hc/en-us/articles/216183237-BETA-Working-with-dynamic-data-sources.

    Once you've gotten your variable formatted properly for this query (both with the props. prefix) and the proper date formatting as expected by your db, you should be able to get this data source working! 

    Best,

    Parker

  • 1
    Avatar
    Matteo Guerrieri

    Hi Parker,

    Thanks for your answer, but I'm not working with any "Klip", I don't know where to find that section.

    I only see "Data sources" or "Metrics" in the menu.

    On the data source creation is where I need to use a variable, I need the dates to be taken from the dashboard dates:

Please sign in to leave a comment.