SQL query with "IN" constraint (application of map() or something similar)

[budugulo]

library(dplyr)
library(DBI)
library(RSQLite)

# Toy Database-------
toy_table <- tibble(id = seq(1:100),
             grade = rep(c("A", "B"), each = 50))

# DB connection: In-memory SQLite database and copy over toy_table-------
con_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con_db, toy_table)

# ID (data required for these ids only)-------
id_wanted <- c("'15', '17', '26', '50', '61', '97', '99', '100'") 

# Prepare query-------
query <- paste("select * from toy_table where id in (", id_wanted, ")")

# Get data-------
DBI::dbGetQuery(
  con_db,
  query)
#>    id grade
#> 1  15     A
#> 2  17     A
#> 3  26     A
#> 4  50     A
#> 5  61     B
#> 6  97     B
#> 7  99     B
#> 8 100     B

In the example above, I am retrieving data only for the IDs stored in the vector id_wanted.

id_wanted <- c("'15', '17', '26', '50', '61', '97', '99', '100'") 

Suppose I have the following constraint:

• id_wanted can contain maximum 3 elements.

How can I "break down" id_wanted and retrieve data for all the wanted ids? In other words, without manually creating SQL statements, how can I programmatically retrieve the desired data given the constraint?

[nirgrahamuk]

This code breaks id_wanted into sets of max 3, then repeatedly queries the db for them, and serves them up after they are recombined

library(dplyr)
library(DBI)
library(RSQLite)
library(purrr)
# Toy Database-------
toy_table <- tibble(id = seq(1:100),
                    grade = rep(c("A", "B"), each = 50))

# DB connection: In-memory SQLite database and copy over toy_table-------
con_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con_db, toy_table)

# ID (data required for these ids only)-------
id_wanted <- c("'15', '17', '26', '50', '61', '97', '99', '100'") 
id_wanted_pure <- str_split(id_wanted,",",simplify = TRUE)
(split_id <- split(id_wanted_pure,(seq_along(id_wanted_pure) - 1) %/% 3))

# Get data-------
map_dfr(split_id, ~{
DBI::dbGetQuery(
  con_db,
  paste("select * from toy_table where id in (", paste0(.x,collapse = ","), ")"))
})

[budugulo]

It is indeed what I was looking for. Many thanks! Could you please explain the following chunk of code?

@nirgrahamuk

id_wanted_pure <- str_split(id_wanted,",",simplify = TRUE)
(split_id <- split(id_wanted_pure,(seq_along(id_wanted_pure) - 1) %/% 3))