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

# Source:   SQL [?? x 45]
# Database: BigQueryConnection
   observation_id person_id observation_concept_id observation_date
          <int64>   <int64>                <int64> <date>          
 1          5.e16   9511495                4052785 2024-01-21      
 2          5.e16   9511495               40758030 2023-11-05      
 3          5.e16   2507782                4271662 2020-12-20      
 4          1 e15   1924698                1586193 2019-03-20      
 5          1 e15   2385328               40770194 2020-06-10      
 6          1 e15   8426511               40764345 2021-09-07      
 7          5.e16   3276670                4311995 2017-03-08      
 8          5.e16   9511495                3042950 2023-09-17      
 9          1 e15   1571432               37020658 2022-01-11      
10          1 e15   3068681               43530590 2019-06-17      
# ℹ more rows
# ℹ 41 more variables: observation_datetime <dttm>,
#   observation_type_concept_id <int64>, value_as_number <dbl>,
#   value_as_string <chr>, value_as_concept_id <int64>,
#   qualifier_concept_id <int64>, unit_concept_id <int64>,
#   visit_occurrence_id <int64>, visit_detail_id <int64>,
#   observation_source_value <chr>, observation_source_concept_id <int64>, …