Export a mask akin to rlang::.data for custom SQL functions

[MichaelChirico]

A useful feature of dbplyr is automatically passing through unknown calls straight into SQL, e.g. some flavors support ANY_VALUE() to take a value from a column arbitrarily during aggregation:

conn |>
  tbl("my_schema.table") |>
  group_by(grp) |>
  summarize(some_x = ANY_VALUE(x)) |>
  ungroup() |>
  show_query()

Will give something like

SELECT grp, ANY_VALUE(x) AS some_x
FROM my_schema.tbl
GROUP BY grp

However, within packages, this presents the classic issue with R CMD check notes that ANY_VALUE() is an undefined function, from the static R checker's point of view.

rlang offers .data and .env masks for this purpose:

https://rlang.r-lib.org/reference/dot-data.html

Could dbplyr offer something similar, e.g. an .sql mask we could use as:

conn |>
  tbl("my_schema.table") |>
  group_by(grp) |>
  summarize(some_x = .sql$ANY_VALUE(x)) |>
  ungroup() |>
  show_query()

[MichaelChirico]

Noting that this won't cover NSE-only operators like %like% that support custom translation, maybe a simple placeholder should be exported for those?

[mgirlich]

@hadley What do you think?

[hadley]

Hmmmm, exposing .sql is an interesting idea. We already have a way to force remote execution with sql() this would be more R CMD check friendly.

I quite like the idea of exporting a %like% place holder too — it could just work like dplyr::n() and throw an error when called directly.

[hadley]

Given that .env$mean(x) clearly implies that we should evaluate mean() locally, I wonder if it's not too much of an abuse for .data$mean(x) to mean evaluate mean(x) in the database? (i.e. translate it to SQL)