Join current query to another omop table

Simple wrapper for join functions to join an existing query to another table in the omop cdm (or any other of the same source).

Usage

omop_join(
  data,
  table,
  type,
  by,
  suffix = c("_x", "_y"),
  con = getOption("con.default.value"),
  schema = NULL,
  x_as = NULL,
  y_as = NULL,
  ...
)

Arguments

data

sql query from dbplyr/dplyr. this function works in pipes!

table

the omop table (or other table in your schema) you wish to join

type

the type of join. use types available in dplyr: left, right, inner, anti, full etc.

con

defaults to the connection you set with options()

schema

defaults to the schema you set with options()

x_as

optional; a string for the name of the left table

y_as

optional; a string for the name of the right table

...

arguments passed on to the join function. e.g., by = "person_id"

Value

Continued dplyr query

Details

If you are using omop_join with ohdsi_lab, you should include the following two lines at the top of your script after setting the schema and connection, where con refers to a connection generated by DatabaseConnector::connect() and cdm_schema points to the cdm_schema for pharmetrics. To provide provide an alternative schema, see below.

options(con.default.value = con)

options(schema.default.value = cdm_schema)

There are a few good reasons to use omop_join() when possible over the x_join functions from dplyr. First, it reduces the code necessary to join an existing table to another table. Second, it includes checks/workarounds for two sources of common errors using dplyr with DatabaseConnector: it automatically appends the x_as and y_as arguments to the join call if they are not provided and it changes the default suffix from .x/.y to _x/_y for cases with shared column names not specified by the by argument which will result in a sql error.

omop_join() can also be used to join tables in other schema - simply set the schema argument to the preferred schema (whereas otherwise it just looks for the default).

Examples

options(con.default.value = con)
#> Error: object 'con' not found
options(schema.default.value = cdm_schema)
#> Error: object 'cdm_schema' not found
obs_tbl |>
  omop_join("person", type = "left", by = "person_id")
#> Error in omop_join(obs_tbl, "person", type = "left", by = "person_id"): Provide `con` as an argument or default with `options(con.default.value = ...)`