sql_build: Build and render SQL from a sequence of lazy operations

In dbplyr: A 'dplyr' Back End for Databases

Build and render SQL from a sequence of lazy operations

Description

sql_build() creates a select_query S3 object, that is rendered to a SQL string by sql_render(). The output from sql_build() is designed to be easy to test, as it's database agnostic, and has a hierarchical structure. Outside of testing, however, you should always call sql_render().

Usage

lazy_multi_join_query(
  x,
  joins,
  table_names,
  vars,
  group_vars = op_grps(x),
  order_vars = op_sort(x),
  frame = op_frame(x),
  call = caller_env()
)

lazy_rf_join_query(
  x,
  y,
  type,
  by,
  table_names,
  vars,
  group_vars = op_grps(x),
  order_vars = op_sort(x),
  frame = op_frame(x),
  call = caller_env()
)

lazy_semi_join_query(
  x,
  y,
  vars,
  anti,
  by,
  where,
  group_vars = op_grps(x),
  order_vars = op_sort(x),
  frame = op_frame(x),
  call = caller_env()
)

lazy_query(
  query_type,
  x,
  ...,
  group_vars = op_grps(x),
  order_vars = op_sort(x),
  frame = op_frame(x)
)

lazy_select_query(
  x,
  select = NULL,
  where = NULL,
  group_by = NULL,
  having = NULL,
  order_by = NULL,
  limit = NULL,
  distinct = FALSE,
  group_vars = NULL,
  order_vars = NULL,
  frame = NULL,
  select_operation = c("select", "mutate", "summarise"),
  message_summarise = NULL
)

lazy_set_op_query(x, y, type, all, call = caller_env())

lazy_union_query(x, unions, call = caller_env())

sql_build(op, con = NULL, ..., sql_options = NULL)

sql_render(
  query,
  con = NULL,
  ...,
  sql_options = NULL,
  subquery = FALSE,
  lvl = 0
)

sql_optimise(x, con = NULL, ..., subquery = FALSE)

join_query(
  x,
  y,
  select,
  ...,
  type = "inner",
  by = NULL,
  suffix = c(".x", ".y"),
  na_matches = FALSE
)

select_query(
  from,
  select = sql("*"),
  where = character(),
  group_by = character(),
  having = character(),
  window = character(),
  order_by = character(),
  limit = NULL,
  distinct = FALSE,
  from_alias = NULL
)

semi_join_query(
  x,
  y,
  vars,
  anti = FALSE,
  by = NULL,
  where = NULL,
  na_matches = FALSE
)

set_op_query(x, y, type, all = FALSE)

union_query(x, unions)

Arguments

...	Other arguments passed on to the methods. Not currently used.
op	A sequence of lazy operations
con	A database connection. The default NULL uses a set of rules that should be very similar to ANSI 92, and allows for testing without an active database connection.
sql_options	SQL rendering options generated by sql_options().
subquery	Is this SQL going to be used in a subquery? This is important because you can place a bare table name in a subquery and ORDER BY does not work in subqueries.

Details

sql_build() is generic over the lazy operations, lazy_ops, and generates an S3 object that represents the query. sql_render() takes a query object and then calls a function that is generic over the database. For example, sql_build.op_mutate() generates a select_query, and sql_render.select_query() calls sql_select(), which has different methods for different databases. The default methods should generate ANSI 92 SQL where possible, so you backends only need to override the methods if the backend is not ANSI compliant.

dbplyr documentation built on May 29, 2024, 6:19 a.m.