Simple wrapper for join functions to join an existing query to another table in the omop cdm (or any other of the same source).
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"
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"