acdcquery (version 1.1.1)

add_argument: Add a filter argument to a list

Description

This function adds an argument to a list containing filter arguments later used to select data from the database. When supplying the variable used for filtering, the operator and the value, an SQL query will be constructed for the user and added as the next object to the list of arguments.#' When supplying only variable, operator and value, a SQL query will be constructed for the user and added as the next object to a list. Alternatively, the user may specify an SQL query manually.

Usage

add_argument(list, conn, variable, operator, values, statement = NULL)

Value

A list object with the new argument (SQL query) added.

Arguments

list

The list to which the argument will be added.

conn

The connection object or database connection string.

variable

The variable name to be used in the argument.

operator

The operator to be used in the argument (i.e., "greater", "between", "equal", "less").

values

The values to be used in the argument.

statement

The manual SQL query to be used.

Examples

conn <- connect_to_db(":memory:")

mtcars$mtcars_id = 1:nrow(mtcars)

example_data = data.frame(
  example_id = 1:150,
  mtcars_id = rep(1:30, each = 5),
  example_value = runif(150, 0, 1)
)

DBI::dbWriteTable(conn, "mtcars_table", mtcars)
DBI::dbWriteTable(conn, "example_table", example_data)

# Initializing argument list
arguments = list()

# Using "equal" operator
arguments = add_argument(
 list = arguments,
 conn = conn,
 variable = "cyl",
 operator = "equal",
 values = c(4, 6)
)

# Using "greater" operator
arguments = add_argument(
 list = arguments,
 conn = conn,
 variable = "cyl",
 operator = "greater",
 values = 2
)

# Using "between" operator
arguments = add_argument(
 list = arguments,
 conn = conn,
 variable = "cyl",
 operator = "between",
 values = c(2, 8)
)

# Manully constructing a filter statement
manual_arguments = add_argument(
 list = arguments,
 conn = conn,
 statement = "SELECT mtcars_id FROM mtcars WHERE cyl = 4 OR cyl = 6)"
)