Make a set indicator node.

Create a new column indicating the membership of another column in a given set.

set_indicator(
  source,
  rescol,
  testcol,
  testvalues,
  ...,
  translate_quotes = FALSE,
  env = parent.frame()
)

Arguments

source	source to select from.
rescol	name of column to land indicator in.
testcol	name of column to check.
testvalues	values to check for.
...	force later arguments to bind by name
translate_quotes	logical if TRUE translate quotes to SQL choice (simple replacement, no escaping).
env	environment to look to.

Value

set_indicator node.

Examples

if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
  my_db <- DBI::dbConnect(RSQLite::SQLite(),
                          ":memory:")

  d <- rq_copy_to(my_db, 'd',
                   data.frame(a = c("1", "2", "1", "3"),
                              b = c("1", "1", "3", "2"),
                              q = 1,
                              stringsAsFactors = FALSE),
                   temporary = TRUE,
                   overwrite = TRUE)
  # example
  set <- c("1", "2")
  op_tree <- d %.>%
    set_indicator(., "one_two", "a", set) %.>%
    set_indicator(., "z", "a", c())
  print(column_names(op_tree))
  print(columns_used(op_tree))
  cat(format(op_tree))
  sql <- to_sql(op_tree, my_db)
  cat(sql)
  print(DBI::dbGetQuery(my_db, sql))

  op_tree2 <- d %.>%
    set_indicator(., "one_two", "a", set) %.>%
    set_indicator(., "z", "b", c()) %.>%
    select_columns(., c("z", "one_two"))
  print(column_names(op_tree2))
  print(columns_used(op_tree2))

  # cleanup
  DBI::dbDisconnect(my_db)
}
#> [1] "a"       "b"       "q"       "one_two" "z"      
#> $d
#> [1] "a" "b" "q"
#> 
#> mk_td("d", c(
#>   "a",
#>   "b",
#>   "q")) %.>%
#>  set_indicator(., one_two = a IN set) %.>%
#>  set_indicator(., z = a IN c())
#> SELECT *, 0 AS `z` FROM (
#>  SELECT *, `a`  IN (  '1'  ,  '2'  )  AS `one_two` FROM (
#>   SELECT
#>    `a`,
#>    `b`,
#>    `q`
#>   FROM
#>    `d`
#>  ) tsql_04505208355990005100_0000000000
#> ) tsql_04505208355990005100_0000000001
#>   a b q one_two z
#> 1 1 1 1       1 0
#> 2 2 1 1       1 0
#> 3 1 3 1       1 0
#> 4 3 2 1       0 0
#> [1] "z"       "one_two"
#> $d
#> [1] "a" "b"
#>