How to use glue inside of lapply

I'm trying to use the function glue_sql inside of lapply, but I'm running into a problem.

I have a long list of IDs to use in a WHERE clause. That list is too long for SQL Server and results in an error, so I am trying to cut the list into pieces to run smaller queries and loop through the pieces. My code works outside of lapply.

Because of the way that variables are called in glue, I'm seeing an error. I think glue is expecting a global variable rather than the list passed to it by the anonymous function as "x".

Here's a sample.

library(glue)
mylist <- list( `1` = c("1111","1112","1113","1114","1115"),
                `2` = c("2221","2222","2223","2224","2225"))

sqlcode <- lapply(mylist,
    function(x) glue_sql("
        SELECT *
        FROM [db].[dbo].[tbl]
        WHERE ID IN ({x*})
    "), .con = conn)

Solution

This works for me when I put the .con=conn inside the anon-func:

sqlcode <- lapply(mylist,
#     function(x) glue::glue_sql("
#         SELECT *
#         FROM [db].[dbo].[tbl]
#         WHERE ID IN ({x*})
#     ", .con = conn))

sqlcode
# $`1`
# <SQL>     SELECT *
#     FROM [db].[dbo].[tbl]
#     WHERE ID IN ('1111', '1112', '1113', '1114', '1115')
# $`2`
# <SQL>     SELECT *
#     FROM [db].[dbo].[tbl]
#     WHERE ID IN ('2221', '2222', '2223', '2224', '2225')

An alternative to your effort, though, that requires only one query:

DBI::dbWriteTable(conn, data.frame(id = unlist(mylist), "#sometemp")

### one of:
dat <- DBI::dbGetQuery(conn,
  "select *
   from [db].[dbo].[tbl]
   where ID in (select * from #sometemp)")
### or
dat <- DBI::dbGetQuery(conn,
  "select v.*
   from #sometemp t
     inner join [db].[dbo].[tbl] v on t.id=v.Id")

# not strictly necessary since it's a temporary table, but cleanup anyway
DBI::dbExecute(conn, "drop table #sometemp")

Note: the use of "#" to denote a temporary table is specific to SQL Server. Other DBMSes would instead use

dbWriteTable(conn, data.frame(..), "sometemp", temporary=TRUE)

for the same effect.