Write custom lazy evaluation function like dbplyr to get SQL

Question

How can I write the logic to this function which should be able to do 2 things.

get_data <- function(database, table=NULL, query=NULL){
  
  rlang::check_required(
    x = database
  )
  
  if(is.null(query) & !is.null(table)){
    
    # check whether code is piped %>% | |>
    query <- dbplyr::sql_render("piped code")
    
  }
  
  return(query)
  
}

Accept SQL Query

get_data(
  database = "uspto",
  query = "select * from applications where id = 1"
)

Recognize that Query is NULL and Table is not NULL so perform some check or pass through to piped code.

get_data(
  database = "uspto",
  table = "applications"
) %>%
  filter(id == 1) %>%
  collect()

For more context this function will be interacting with an API that accepts SQL as input. There will be a network / microservice layer between the client and the API. So I probably need to do something like below to create a dummy connection which I can then use dbplyr::sql_render to get a query string.

con <- memdb_frame(
  database,
  .name = table
)

Answer 1

I think this works well enough.

get_data <- function(database, table = NULL, query = NULL) {
  if (is.null(query)) {
    tbl(database, table) 
  } else {
    tbl(database sql(query))
  }

With this, you can do

con <- DBI::dbConnect(...)
get_data(con, table = "MyTable") |>
  head() |>
  collect()
get_data(con, query = "select * from MyTable limit 5") |>
  collect()

(If you're working with SQL Server, clearly that query should be something like "select top 5 * from MyTable".)

Code golf (shorter code):

get_data <- function(database, table = NULL, query = NULL) tbl(database, if (is.null(table)) sql(query) else table)

---

Edit. You named your function get_data (as did I), so the logical return value should be data. If instead you want to return a query to get data, perhaps you really mean:

get_query <- function(database, table = NULL, query = NULL) if (is.null(table)) query else sprintf("select * from [%s].[%s]", database, table)
get_query("quux", table = "sometable")
# [1] "select * from [quux].[sometable]"
get_query("quux", query = "select * from othertable")
# [1] "select * from othertable"