Should glue_sql_collapse() exist?

[DavisVaughan]

I'm working on a template for a SQL Server MERGE function and want to make the ON statement as dynamic as possible. I do that by building it up one piece at a time. This process involves a glue_collapse() followed by a DBI::SQL() because glue_collapse() coerces to a character(). Should there be a glue_sql_collapse() to support this?

library(glue)
library(odbc)
#> Warning: package 'odbc' was built under R version 3.4.4
library(dbplyr)
#> Warning: package 'dbplyr' was built under R version 3.4.4

x <- c("thing1", "thing2")
y <- c("other1", "other2")

# Mock my S4 sql server connection
setClass("Microsoft SQL Server", representation(quote = "character"), contains = "OdbcConnection")
conn <- new("Microsoft SQL Server", quote = "\"")

sql_conditions <- glue_sql(' "target".{`x`} = "source".{`y`} ', .con = conn)
sql_conditions
#> <SQL>  "target"."thing1" = "source"."other1" 
#> <SQL>  "target"."thing2" = "source"."other2"

# Can't just use glue_collapse(). Get back a character.
sql_combined_conditions <- DBI::SQL(glue::glue_collapse(sql_conditions, sep = " AND "))
sql_combined_conditions
#> <SQL>  "target"."thing1" = "source"."other1"  AND  "target"."thing2" = "source"."other2"

sql_ON <- glue::glue_sql("ON {sql_combined_conditions}", .con = conn)
sql_ON
#> <SQL> ON  "target"."thing1" = "source"."other1"  AND  "target"."thing2" = "source"."other2"

Created on 2018-07-31 by the reprex package (v0.2.0).

[jimhester]

maybe glue_collapse should just handle this case specifically and return a SQL if given an SQL input...

[DavisVaughan]

That would work too!

[tbradley1013]

Has there been any progress on this? I wrote a potential implementation as I was about to open a similar issue when I found this. I am sure that it will need to be refined, but if you like the implementation than I am happy to create a PR:

library(glue)
library(DBI)

tmp_con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")
DBI::dbWriteTable(tmp_con, "mtcars", mtcars)

# Create two separate queries
query_one_opts <- c(4, 6)
query_one <- glue::glue_sql("cyl in ({query_one_opts*})", .con = tmp_con)

query_two_opts <- c(1, 2)
query_two <- glue::glue_sql("carb in ({query_two_opts*})")

query <- list("cyl in ({query_one_opts*})", "carb in ({query_two_opts*})")

# Queries are two SQL statements prior to combining
query_sql <- list(query_one, query_two)



# ROUGH ATTEMPT AT A FUNCTION FOR THIS
# Helper function to run glue_sql on anything that already hasn't
check_sql <- function(x, ...){
  if (!"SQL" %in% class(x)){
    return(glue::glue_sql(x, ...))
  } else return(x)
}

# collapse the sql statements
glue_sql_collapse <- function(x, .sep = " AND ", .con){
  # error checking from glue_collapse
  if (length(x) == 0) {
    return(as_glue(character()))
  }
  if (any(is.na(x))) {
    return(as_glue(NA_character_))
  }
  
  
  x <- lapply(x, check_sql, .con = .con)
  
  out <- glue::glue_collapse(x, sep = .sep)
  
  out <- DBI::SQL(conn = .con, out)
  
  return(out)
}

glue_sql_collapse(query, .sep = " AND ", .con = tmp_con)
#> <SQL> cyl in (4, 6) AND carb in (1, 2)
glue_sql_collapse(query_sql, .sep = " AND ", .con = tmp_con)
#> <SQL> cyl in (4, 6) AND carb in (1, 2)

^{Created on 2019-11-22 by the reprex package (v0.3.0)}