I have been able to do this in the past using sprintf() to create an sql statement based on a list of user selected id's. But now I am wanting to make an sql statement based on a user selected list of multiple fields, from multiple tables, and I'm at a loss.
I have a database with multiple tables:
TBL1
Date Program Name Type height width
5/22 E7 Square angle 5 5
5/22 H9 Circle smooth 4 4
9/9 E7 Circle smooth 7 7
10/10 R8 Triangle angle 10 5
TBL2
Date Program Name Value1 Value2
5/22 E7 Square 5 2.4
5/22 H9 Circle 10 43
9/9 E7 Circle 3.2 9
10/10 R8 Triangle 999 1
TBL3
Type 1 2 3
angle a g h
smooth b c d
And I have a dataframe of rows I want to get from the database
df
Date Program Name
5/22 E7 Square
9/9 E7 Circle
10/10 R8 Triangle
I need to dynamically generate an SQL statement for gathering the data that I need. So I would need to get the Date, Program, Name, Type, Value1, and Value2 for every Date, Program, and Name in my df.
I was trying to figure out if sqlinterpolate could handle this, but it doesn't seem like it can?
I would join TBL1 and TBL3 WHERE Type=Type, and then join the VALUES from TBL2 all only for the rows where Date, Program, and Name matched my df.
Desired output from the sql return:
Date Program Name Type 1 2 3 Value1 Value2
5/22 E7 Square angle a g h 5 2.4
9/9 E7 Circle smooth b c d 3.2 9
10/10 R8 Triangle angle a g h 999 1
Thoughts?
You can split df into rows, create a query for each one, collapse the queries by 'UNION ALL\n', then fetch:
library(RSQLite)
library(DBI)
library(tidyverse)
library(glue)
#>
#> Attaching package: 'glue'
#> The following object is masked from 'package:dplyr':
#>
#> collapse
t1 <- tibble(date = c("5/22", "5/22", "9/9", "10/10"),
program = c("E7", "H9", "E7", "R8"),
name = c("Square", "Circle", "Circle", "Triangle"),
type = c("angle", "smooth", "smooth", "angle"),
height = c(5, 4, 7, 10),
width = c(5, 4, 5, 5))
t2 <- tibble(date = c("5/22", "5/22", "9/9", "10/10"),
program = c("E7", "H9", "E7", "R8"),
name = c("Square", "Circle", "Circle", "Triangle"),
val1 = c(5, 10, 3.2, 999),
val2 = c(2.4, 43, 9, 1))
t3 <- tibble(type = c("angle", "smooth"),
one = c("a", "b"),
two = c("g", "c"),
three = c("h", "d"))
df <- tibble(date = c("5/22", "9/9", "10/10"),
program = c("E7", "E7", "R8"),
name = c("Square", "Circle", "Triangle"))
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "tab1", t1)
dbWriteTable(con, "tab2", t2)
dbWriteTable(con, "tab3", t3)
queries <- df %>%
split(seq_len(nrow(.))) %>%
map(~{
d <- .x$date
p <- .x$program
n <- .x$name
glue('SELECT t1.date,
t1.program,
t1.name,
t1.type,
t2.val1,
t2.val2,
t3.one,
t3.two,
t3.three
FROM tab1 t1
LEFT JOIN tab2 t2 ON t1.date = t2.date AND t1.program = t2.program
LEFT JOIN tab3 t3 ON t1.type = t3.type
WHERE t1.date = "{d}"
AND t1.program = "{p}"
AND t1.name = "{n}"')
})
q <- paste(queries, collapse = "UNION ALL\n")
dbGetQuery(con, q)
#> date program name type val1 val2 one two three
#> 1 5/22 E7 Square angle 5.0 2.4 a g h
#> 2 9/9 E7 Circle smooth 3.2 9.0 b c d
#> 3 10/10 R8 Triangle angle 999.0 1.0 a g h
Created on 2020-04-03 by the reprex package (v0.3.0)