how can I loop over subqueries using glue_sql?

[von_olaf]

I am using the amazing glue_sql to write complex queries and I love it.
However, I was not able to figure out how to loop over subqueries.

For instance something like:

table1 <- glue_sql("SELECT * FROM realtable_one", .con = oracon) 
table2 <- glue_sql("SELECT * FROM realtable_two", .con = oracon) 

mytables <- c('table1', 'table2')
purrr::map(mytables, glue_sql("SELECT * FROM ({.}), .con = oracon)

even a gool ol loop does not work, such as:

for(tab in mytables){dbGetQuery(oracon, 
                                glue_sql("SELECT * FROM ({tab}), .con = oracon))}

This syntax does not work but I cant understand why... Any ideas? Thanks!

[cderv]

@von_olaf

purrr::map(mytables, glue_sql("SELECT * FROM ({.}), .con = oracon)

This may be a typo but you missed a ~ for map function and a " to close glue string.

This seems to work fine

library(glue)
# dummy connexion for example
oracon <- DBI::ANSI()
mytables <- c('table1', 'table2')
purrr::map(mytables, ~ glue_sql("SELECT * FROM ({.})", .con = oracon))
#> [[1]]
#> <SQL> SELECT * FROM ('table1')
#> 
#> [[2]]
#> <SQL> SELECT * FROM ('table2')

for(tab in mytables){
  print(
    glue_sql("SELECT * FROM ({tab})", .con = oracon)
  )
}
#> <SQL> SELECT * FROM ('table1')
#> <SQL> SELECT * FROM ('table2')

^{Created on 2019-01-14 by the reprex package (v0.2.1)}

[von_olaf]

thank you @cderv for catching that typo. However, the point is that embedding the queries into a dbGetQuery does not seem to work (some extra ' is probably messing with the query)

 for(tab in mytables){
   myquery <- glue_sql("SELECT * FROM ({tab})", .con = oracon)
   dbGetQuery(oracon, myquery)
}

does that work for you? can you run multiple queries like that?

[jimhester]

Glue is vectorized over its arguments, so you would just pass the vector to it directly.

library(glue)

my_tables <- c('table1', 'table2')
my_queries <- glue_sql("SELECT * FROM ({my_tables})", .con = DBI::ANSI())
my_queries
#> <SQL> SELECT * FROM ('table1')
#> <SQL> SELECT * FROM ('table2')

[von_olaf]

thank you @jimhester but as I was saying to @cderv, looping to create a query is fine. The problem is using these queries in a dbGetQuery call in a loop (see above). Am I missing something? I am getting invalid table name ORA 00903 when I do so
Thanks!

[von_olaf]

I wonder if the quotation marks around table1 are not causing trouble here... that is ORACLE is looking for the table table1 which does not exist because table1 is actually a view (subquery)

[andresrcs]

You have to pass the textual subquery because table1 and table2 are not views in your database, try this approach.

library(glue)
library(rlang)


table1 <- glue_sql("SELECT * FROM realtable_one", .con = DBI::ANSI()) 
table2 <- glue_sql("SELECT * FROM realtable_two", .con = DBI::ANSI())

mytables <- c('table1', 'table2')
purrr::map(mytables, ~ glue_sql("SELECT * FROM ({eval_tidy(as.name(.))}) as tab", .con = DBI::ANSI()))
#> [[1]]
#> <SQL> SELECT * FROM (SELECT * FROM realtable_one) as tab
#> 
#> [[2]]
#> <SQL> SELECT * FROM (SELECT * FROM realtable_two) as tab

I have no problems doing this with that approach

for(tab in mytables){
    myquery <- glue_sql("SELECT * FROM ({eval_tidy(as.name(tab))}) as tab", .con = con)
    dbGetQuery(con, myquery) #results are not been saved this way
    # assign(paste0('result_', tab), dbGetQuery(con, myquery))
}

[von_olaf]

pretty sweet!!! is the eval_tidy something similar to the quo() unquo() functions in dplyr ? @andresrcs

[andresrcs]

quo() is from rlang not dplyr and there is no unquo() function, I think you mean !! instead for resuming evaluation and yes it is similar but works outside a function.

[von_olaf]

thanks! these tidy functions are getting so complicated... can you just explain what eval_tidy(as.name( is doing exactly? I see it works wonderfully, but why??

[andresrcs]

as.name() is converting a character string i.e. "table1" into a variable name and eval_tidy() is retrieving the content of the variable i.e. the subquery

[jimhester]

If you don't want to quote the table name use DBI::SQL() to pass literal SQL to the subquery.

library(glue)
table1 <- glue_sql("SELECT * FROM realtable_one", .con = DBI::ANSI()) 
table2 <- glue_sql("SELECT * FROM realtable_two", .con = DBI::ANSI())

my_tables <- c('table1', 'table2')
glue_sql("SELECT * FROM ({DBI::SQL(my_tables)}) as tab", .con = DBI::ANSI())
#> <SQL> SELECT * FROM (table1) as tab
#> <SQL> SELECT * FROM (table2) as tab

[von_olaf]

I like this! thanks buddy

[andresrcs]

@jimhester

my_tables <- c('table1', 'table2') glue_sql("SELECT * FROM ({DBI::SQL(my_tables)}) as tab", .con = DBI::ANSI()) #> <SQL> SELECT * FROM (table1) as tab #> <SQL> SELECT * FROM (table2) as tab

I'm a little confused, I think this would only work if table1 and table2 are views that already exist within the database, because you are not storing the subquery as a view in your databse before hand.