CODEWITHSUNDEEP
rtidyverser-glue
user4687531
user4687531

Reputation: 1111

Create glue string with mixture of single and double quotes

I am trying to create a string using the glue package in R which is a mixture of 'single' and "double" quotes.

As a reprex, consider the following type of SQL query string I want to build:

CREATE TABLE fact_final_table AS 
(SELECT tab1.id,
    AVG(tab2."MV") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '7'
                                                      AND tab1.start_date::date - integer '1') AS "mv_avg_1w",
    AVG(tab2."MV") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '14'
                                                      AND tab1.start_date::date - integer '1') AS "mv_avg_2w"
FROM (SELECT id,
             start_date,
             point
      FROM base_tab
      WHERE mpfb.start_date::date >= '01-01-2000'::date) AS tab1
LEFT JOIN ghcnd_observations AS tab2
    ON (tab2.record_dt BETWEEN (tab1.start_date::date - integer '180')
                           AND (tab1.start_date::date - integer '1')
        AND ST_DWithin(tab1.point, tab2.location, 0.5))
GROUP BY tab1.id);

As you can see it is a combination of single and double quotes, which are important to preserve literally as written above. For example the tab2."MV" has double quotes, and tab1.start_date::date - integer '7' AND tab1.start_date::date - integer '1' has single quotes which need to be preserved literally.

This string also needs to be built using parameters. I tried the following in R using glue, but couldn't get it to work.

var1       <- "MV"
var1_lowcase <- "mv"
lag_days   <- 180
var_date   <- as.Date("2000-01-01")
var_dwithin <- 0.5

glue::glue(
"CREATE TABLE fact_final_table AS 
(SELECT tab1.id,
    AVG(tab2."{var1}") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '7'
                           AND tab1.start_date::date - integer '1') AS "{var1_lowcase}_avg_1w",
    AVG(tab2."{var1}") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '14'
                           AND tab1.start_date::date - integer '1') AS "{var1_lowcase}_avg_2w"
    FROM (SELECT id,
          start_date,
          point
          FROM base_tab
          WHERE mpfb.start_date::date >= '{format(var_date, "%d-%m-%Y")}'::date) AS tab1
    LEFT JOIN ghcnd_observations AS tab2
    ON (tab2.record_dt BETWEEN (tab1.start_date::date - integer '{lag_days}')
        AND (tab1.start_date::date - integer '1')
        AND ST_DWithin(tab1.point, tab2.location, {var_dwithin}))
    GROUP BY tab1.id);")

This does not work unfortunately due to single/double quotes not being escaped correctly in glue::glue(...).

Could anyone please assist here with minimal amount of breaking of the required output string? I'm not sure if this is easily feasible. I would appreciate any other tidy approaches e.g. using stringr as well for example, as I would like this to be %>% friendly. I've briefly looked at glue::glue_sql but didn't know how to directly apply it here. I'd be grateful to learn how to use it here as applicable.

Upvotes: 2

Views: 1941

Answers (2)

user4687531
user4687531

Reputation: 1111

So I looked into this in more detail since yesterday, and it does appear that glue does have functions to make single and double quotes explicit i.e. glue::single_quote() and glue::double_quote().

Similar to the (helpful) response by @ronakshah, I managed the following, which is more explicit (for code readability):

var1       <- "MV"
var1_lowcase <- "mv"
lag_days   <- 180
var_date   <- as.Date("2000-01-01")
var_dwithin <- 0.5

glue::glue(
    "CREATE TABLE fact_final_table AS 
   (SELECT tab1.id,
   AVG(tab2.{glue::double_quote(var1)}) FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '7'
   AND tab1.start_date::date - integer '1') AS {glue::double_quote(glue::glue({var1_lowcase},'_avg_1w'))},
   AVG(tab2.{glue::double_quote(var1)}) FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '14'
   AND tab1.start_date::date - integer '1') AS {glue::double_quote(glue::glue({var1_lowcase},'_avg_2w'))}
   FROM (SELECT id,
         start_date,
          point
   FROM base_tab
    WHERE mpfb.start_date::date >= {glue::single_quote(format(var_date, '%d-%m-%Y'))}::date) 
  AS tab1
   LEFT JOIN ghcnd_observations AS tab2
   ON (tab2.record_dt BETWEEN (tab1.start_date::date - integer '{lag_days}')
   AND (tab1.start_date::date - integer '1')
   AND ST_DWithin(tab1.point, tab2.location, {var_dwithin}))
   GROUP BY tab1.id);")

Which returns the main string I required. Hope this helps others who had similar glue requirements.

Upvotes: 6

Ronak Shah
Ronak Shah

Reputation: 388797

Try to escape the double quotes : 

glue::glue(
   "CREATE TABLE fact_final_table AS 
   (SELECT tab1.id,
   AVG(tab2.\"{var1}\") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '7'
   AND tab1.start_date::date - integer '1') AS \"{var1_lowcase}_avg_1w\",
   AVG(tab2.\"{var1}\") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date - integer '14'
   AND tab1.start_date::date - integer '1') AS \"{var1_lowcase}_avg_2w\"
   FROM (SELECT id,
         start_date,
          point
   FROM base_tab
    WHERE mpfb.start_date::date >= '{format(var_date, \"%d-%m-%Y\")}'::date) 
  AS tab1
   LEFT JOIN ghcnd_observations AS tab2
   ON (tab2.record_dt BETWEEN (tab1.start_date::date - integer '{lag_days}')
   AND (tab1.start_date::date - integer '1')
   AND ST_DWithin(tab1.point, tab2.location, {var_dwithin}))
   GROUP BY tab1.id);")

which returns : 

#CREATE TABLE fact_final_table AS 
#(SELECT tab1.id,
#AVG(tab2."MV") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date #- integer '7'
#AND tab1.start_date::date - integer '1') AS "mv_avg_1w",
#AVG(tab2."MV") FILTER (WHERE tab2.record_dt BETWEEN tab1.start_date::date #- integer '14'
#AND tab1.start_date::date - integer '1') AS "mv_avg_2w"
#FROM (SELECT id,
#start_date,
#point
#FROM base_tab
#WHERE mpfb.start_date::date >= '01-01-2000'::date) AS tab1
#LEFT JOIN ghcnd_observations AS tab2
#ON (tab2.record_dt BETWEEN (tab1.start_date::date - integer '180')
#AND (tab1.start_date::date - integer '1')
#AND ST_DWithin(tab1.point, tab2.location, 0.5))
#GROUP BY tab1.id);

Upvotes: 1

Related Questions