query_db {acdcquery}

Query Database

Description

This function performs targeted queries on an SQLite database using specified filtering arguments and returns the query results. It extracts information about which tables of the database are relevant for the query and then joins these relevant tables to the target table. The function constructs an SQL query which incorporates both the joining and filtering target variables. This SQL statement is then applied to the database and the resulting dataframe is returned to the user.

Usage

query_db(
  conn,
  arguments,
  target_vars = "default",
  target_table = "observation_table",
  argument_relation = "and"
)

Arguments

conn

The connection object to an SQLite database.

arguments

A list of filtering arguments for the query. The list must have only one filter argument per list-entry.

target_vars

A character vector specifying the variables to be included in the query results.

target_table

The target table in the database for querying.

argument_relation

A character string specifying the relation between filtering arguments ("and" or "or" or a numerical vector with the same length as the number of arguments). Arguments with equal numbers in their index are joined using the OR operator, others using AND. To represent (A OR B) AND C AND D use the vector c(1, 1, 2, 3).

Value

A data frame containing the query results.

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()
arguments = add_argument(
 list = arguments,
 conn = conn,
 variable = "cyl",
 operator = "equal",
 values = c(4, 6)
)

arguments = add_argument(
 list = arguments,
 conn = conn,
 variable = "example_value",
 operator = "greater",
 values = 0.4
)

# Return specified variables
target_vars = c("mtcars_id", "example_id", "cyl")

query_results = query_db(
 conn = conn,
 arguments = arguments,
 target_vars = target_vars,
 target_table = "example_table",
 argument_relation = "and"
)

# Return all variables in mtcars_table and example_value from example_table
query_results = query_db(
 conn = conn,
 arguments = arguments,
 target_vars = c("default", "example_value"),
 target_table = "mtcars_table",
 argument_relation = "and"
)