[SOLVED] How to add the list in glue

How to add the list in glue_sql to Where filter

I’m writing a function and I wanted to paste a list typed by user to filters in WHERE. List typed to function by user:

filters = list( ‘fruits’ = c(‘apple’,’orange’),
‘vegetables’ = ‘carrot’)

How to paste this list to sql query in glue sql:

df = glue_sql(“select …
where filters)

Solution

parts <- paste(sapply(names(filters), function(nm) sprintf("%s in ({%s*})", nm, nm)), collapse = " and ")
parts
# [1] "fruits in ({fruits*}) and vegetables in ({vegetables*})"
glue::glue_data_sql(filters, paste("select * from table where", parts), .con = con)
# <SQL> select * from table where fruits in ('apple', 'orange') and vegetables in ('carrot')

More dynamic, allowing a singular "!" operator to negate equality/set-membership.

func <- function(nm, z) {
  not <- ("!" %in% z)
  z <- setdiff(z, "!")
  len1 <- (length(z) == 1L)
  parens <- if (len1) c("{", "}") else c("({", "*})")
  op <- if (len1) {
    if (not) "!=" else "="
  } else {
    if (not) "not in" else "in"
  }
  sprintf("%s %s %s setdiff(%s,'!') %s",
          nm, op, parens[1], nm, parens[2])
}

filters <- list( 'fruits' = c('!','apple','orange'), 'vegetables' = c('!', 'carrot') )
paste(mapply(func, names(filters), filters), collapse = " and ")
# [1] "fruits not in ({ setdiff(fruits,'!') *}) and vegetables != { setdiff(vegetables,'!') }"
glue::glue_data_sql(filters, paste(mapply(func, names(filters), filters), collapse = " and "), .con=con)
# <SQL> fruits not in ('apple', 'orange') and vegetables != 'carrot'

filters <- list( 'fruits' = c('apple','orange'), 'vegetables' = c('carrot') )
glue::glue_data_sql(filters, paste(mapply(func, names(filters), filters), collapse = " and "), .con=con)
# <SQL> fruits in ('apple', 'orange') and vegetables = 'carrot'

The negation is based solely on the presence of the "!" literal in a vector. (Anything more complex and you might as well adopt mongo's json-structured query language ... not something I'm eager to re-implement.)