inst/views/create_age_query.R

In LearningToTalk/L2TDatabase: Helper Functions for the Learning-To-Talk 'MySQL' Database

# Generate the SQL query to show the ages when each task was completed in a
# long-format table

library(dplyr)
library(glue)

# We want to get the age and test completion dates from these tables
tasks_to_add <- tibble::tribble(
  ~task_name,       ~table,
  "Blending",       "backend.Blending_Admin",
  "BRIEF",          "backend.BRIEF",
  "CTOPP_Blending", "backend.CTOPP_Blending",
  "CTOPP_Elision",  "backend.CTOPP_Elision",
  "CTOPP_Memory",   "backend.CTOPP_Memory",
  "DELV_Risk",      "backend.DELV_Risk",
  "DELV_Variation", "backend.DELV_Variation",
  "EVT",            "backend.EVT",
  "FruitStroop",    "backend.FruitStroop",
  "GFTA",           "backend.GFTA",
  "KBIT",           "backend.KBIT",
  "MinPair",        "backend.MinPair_Admin",
  "PPVT",           "backend.PPVT",
  "RealWordRep",    "backend.RealWordRep_Admin",
  "Rhyming",        "backend.Rhyming_Admin",
  "SAILS",          "backend.SAILS_Admin",
  "VerbalFluency",  "backend.VerbalFluency"
)


# We consistently name columns TASK_Age and TASK_Completion in each task's table
# , so given a task name we can figure out the the name of the two columns.
create_task_row <- function(task_name, table_name){
  data_frame(
    task = task_name,
    completion = paste0(task_name, "_Completion"),
    age = paste0(task_name, "_Age"),
    table = table_name)
}

task_variables <- purrr::map2_df(
  tasks_to_add$task_name,
  tasks_to_add$table,
  create_task_row)
task_variables

# To generate the SQL query, we are going to convert each row of this table to
# SQL statement that gives a table with a task name, age and date of completion.

# First, we create an initial set of rows using the LENAs.
base_chunk <- "
-- Code generated by `create_age_query.R`
-- Edit that file instead

create or replace algorithm = undefined view backend.q_Task_Ages as
  select
    ChildStudyID,
    \"LENA\" as Task,
    LENA_Date as Task_Completion,
    LENA_Age as Task_Age
  from
    backend.LENA_Admin"

# For each of the tasks in the dataframe, we fill out this templated string to
# create a query for each task.
template <- "
-- rows for batch
  union all
    select
      ChildStudyID,
      \"{task}\" as Task,
      {completion} as Task_Completion,
      {age} as Task_Age
    from
      {table}
"

# Fill out the template for each table and combine into a single string
string <- glue_data(task_variables, template) %>%
  glue::collapse(sep = "\n") %>%
  stringr::str_replace_all("-- rows for batch\n", "")

# Add first chunk on top
query <- glue::collapse(c(base_chunk, string), sep = "\n")
cat(query)

writeLines(query, "./inst/views/ages.sql")