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

library("allofus")



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