join_for_descendants: Join For Descendants
In patelm9/chariot: chariot Package

Perform a compound join of join_for_ancestor_ids and join_on_concept_id for the ancestor concept ids to get all the ancestor attributes along with the ancestor ids.

join_for_descendants(
  kind = c("LEFT", "RIGHT", "INNER", "FULL"),
  data,
  ancestor_id_column = NULL,
  select_data_columns = "*",
  distinct = FALSE,
  write_schema = "patelm9",
  vocab_schema = "omop_vocabulary",
  where_in_concept_ancestor_field,
  where_in_concept_ancestor_field_value,
  where_not_in_concept_ancestor_field,
  where_not_in_concept_ancestor_field_value,
  where_is_null_concept_ancestor_field,
  where_is_not_null_concept_ancestor_field,
  case_insensitive = TRUE,
  conn,
  conn_fun = "connectAthena()",
  verbose = TRUE,
  render_sql = TRUE,
  render_only = FALSE
)

library(chariot)
library(tidyverse)

conn <- connectAthena()
test_data <-
        queryAthena(
                conn = conn,
                 sql_statement =     "
                                        SELECT
                                                concept_id AS test_concept_id,
                                                concept_name AS test_concept_name,
                                                concept_code AS test_concept_code
                                        FROM omop_vocabulary.concept
                                        ORDER BY RANDOM()
                                        LIMIT 20;")
test_data


# `Join On` functions point to the true column that is being joined on
join_on_concept_id(
        conn = conn,
        data = test_data,
        column = "test_concept_id"
        )

join_on_concept_code(
        conn = conn,
        data = test_data,
        column = "test_concept_code"
        )

join_on_concept_name(
        conn = conn,
        data = test_data,
        column = "test_concept_name"
)

# Join on the `concept_synonym_name` field in the Concept Synonym table
join_on_concept_synonym_name(
        conn = conn,
        data = test_data,
        column = "test_concept_name"
)

# Reconfigure the kind of join
join_on_concept_synonym_name(
        conn = conn,
        data = test_data,
        column = "test_concept_name",
        kind = "INNER"
)

# Return only distinct rows
join_on_concept_synonym_name(
        conn = conn,
        data = test_data,
        column = "test_concept_name",
        kind = "INNER",
        distinct = TRUE
)

# Return only the `test_concept_code` column
join_on_concept_synonym_name(
        conn = conn,
        data = test_data,
        column = "test_concept_name",
        kind = "INNER",
        distinct = TRUE,
        select_data_columns = "test_concept_name"
)


# Filter can be applied to the vocabulary tables
join_on_concept_synonym_name(
        conn = conn,
        data = test_data,
        column = "test_concept_name",
        where_not_in_concept_synonym_field = "language_concept_id",
        where_not_in_concept_synonym_field_value = 4180186
)

# `Join For` functions join on a predetermined vocabulary field for the addition
# of the specified column in the source data

# Get all the synonyms for a concept by concept_id
join_for_concept_synonym_name(
        conn = conn,
        data = test_data,
        concept_id_column = "test_concept_id"
        )


# Get the descendant concept ids
join_for_descendant_ids(
        conn = conn,
        data = test_data,
        ancestor_id_column = "test_concept_id"
        )

# Get the ancestor concept ids
join_for_ancestor_ids(
        conn = conn,
        data = test_data,
        descendant_id_column = "test_concept_id"
        )

# To retrieve all the attributes for either the ancestor or descendants, use
# the following functions
join_for_descendants(
        conn = conn,
        data = test_data,
        ancestor_id_column = "test_concept_id"
)

join_for_ancestors(
        conn = conn,
        data = test_data,
        descendant_id_column = "test_concept_id"
)


dcAthena(conn = conn)