Translate an expression to SQL

dbplyr translates commonly used base functions including logical (!, &, |), arithmetic (^), and comparison (!=) operators, as well as common summary (mean(), var()), and transformation (log()) functions. All other functions will be preserved as is. R's infix functions (e.g. %like%) will be converted to their SQL equivalents (e.g. LIKE).

Learn more in vignette("translation-function").

Usage

translate_sql(
  ...,
  con,
  vars_group = NULL,
  vars_order = NULL,
  vars_frame = NULL,
  window = TRUE
)

translate_sql_(
  dots,
  con,
  vars_group = NULL,
  vars_order = NULL,
  vars_frame = NULL,
  window = TRUE,
  context = list()
)

Arguments

..., dots

Expressions to translate. translate_sql() automatically quotes them for you. translate_sql_() expects a list of already quoted objects.

con

An optional database connection to control the details of the translation. The default, NULL, generates ANSI SQL.

vars_group, vars_order, vars_frame

Parameters used in the OVER expression of windowed functions.

window

Use FALSE to suppress generation of the OVER statement used for window functions. This is necessary when generating SQL for a grouped summary.

context

Use to carry information for special translation cases. For example, MS SQL needs a different conversion for is.na() in WHERE vs. SELECT clauses. Expects a list.

Examples

con <- simulate_dbi()

# Regular maths is translated in a very straightforward way
translate_sql(x + 1, con = con)
#> <SQL> `x` + 1.0
translate_sql(sin(x) + tan(y), con = con)
#> <SQL> SIN(`x`) + TAN(`y`)

# Note that all variable names are escaped
translate_sql(like == "x", con = con)
#> <SQL> `like` = 'x'
# In ANSI SQL: "" quotes variable _names_, '' quotes strings

# Logical operators are converted to their sql equivalents
translate_sql(x < 5 & !(y >= 5), con = con)
#> <SQL> `x` < 5.0 AND NOT((`y` >= 5.0))
# xor() doesn't have a direct SQL equivalent
translate_sql(xor(x, y), con = con)
#> <SQL> `x` OR `y` AND NOT (`x` AND `y`)

# If is translated into case when
translate_sql(if (x > 5) "big" else "small", con = con)
#> <SQL> CASE WHEN (`x` > 5.0) THEN 'big' WHEN NOT (`x` > 5.0) THEN 'small' END

# Infix functions are passed onto SQL with % removed
translate_sql(first %like% "Had%", con = con)
#> <SQL> `first` like 'Had%'
translate_sql(first %is% NA, con = con)
#> <SQL> `first` is NULL
translate_sql(first %in% c("John", "Roger", "Robert"), con = con)
#> <SQL> `first` IN ('John', 'Roger', 'Robert')

# And be careful if you really want integers
translate_sql(x == 1, con = con)
#> <SQL> `x` = 1.0
translate_sql(x == 1L, con = con)
#> <SQL> `x` = 1

# If you have an already quoted object, use translate_sql_:
x <- quote(y + 1 / sin(t))
translate_sql_(list(x), con = simulate_dbi())
#> <SQL> `y` + 1.0 / SIN(`t`)

# Windowed translation --------------------------------------------
# Known window functions automatically get OVER()
translate_sql(mpg > mean(mpg), con = con)
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> <SQL> `mpg` > AVG(`mpg`) OVER ()

# Suppress this with window = FALSE
translate_sql(mpg > mean(mpg), window = FALSE, con = con)
#> <SQL> `mpg` > AVG(`mpg`)

# vars_group controls partition:
translate_sql(mpg > mean(mpg), vars_group = "cyl", con = con)
#> <SQL> `mpg` > AVG(`mpg`) OVER (PARTITION BY `cyl`)

# and vars_order controls ordering for those functions that need it
translate_sql(cumsum(mpg), con = con)
#> Warning: Windowed expression `SUM(`mpg`)` does not have explicit order.
#> ℹ Please use `arrange()` or `window_order()` to make deterministic.
#> <SQL> SUM(`mpg`) OVER (ROWS UNBOUNDED PRECEDING)
translate_sql(cumsum(mpg), vars_order = "mpg", con = con)
#> <SQL> SUM(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING)