aou_join {allofus}

Join current query to another table

Description

Joins two tables in the All of Us database. A less verbose wrapper for the dplyr::*_join() functions with some added safeguards.

Usage

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

Arguments

data

unexecuted SQL query from dbplyr/dplyr.

table

the omop table (or other remote table in your schema) you wish to join, as a character string, or a tbl object.

type

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

by

columns to join on

suffix

suffix preferences to add when joining data with the same column names not specified in the by argument.

x_as

optional; a string for the name of the left table

y_as

optional; a string for the name of the right table

...

Additional arguments passed on to the join function

con

Connection to the allofus SQL database. Defaults to getOption("aou.default.con"), which is created automatically with aou_connect().

Details

There are a few good reasons to use aou_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 dbplyr: 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.

Value

Reference to the remote table created by the join.

Examples

con <- aou_connect()
obs_tbl <- dplyr::tbl(con, "observation") %>%
  dplyr::select(-provider_id)
obs_tbl %>%
  aou_join("person", type = "left", by = "person_id")