Skip to contents

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("aou.default.con"),
  x_as = NULL,
  y_as = NULL,
  ...
)

Arguments

data

sql query from dbplyr/dplyr.

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()

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

Include the following line at the top of your script after setting the connection where con refers to the connection object in R. options(aou.default.con = con)

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.

Examples

allofus::aou_connect()
#> Error: Unable to connect
#> <BigQueryConnection>
#>   Dataset: NA.NA
#>   Billing: 
options(aou.default.con = con)
#> Error in options(aou.default.con = con): object 'con' not found
obs_tbl |>
  omop_join("person", type = "left", by = "person_id")
#> Error in tbl(con, table): could not find function "tbl"