Convert dplyr pipeline into SQL string

Question

I would like to convert a (short) dplyr pipeline into a string representation of its equivalent SQL. For example:

library(dplyr)
dbplyr::lazy_frame() %>% filter(foo == 'bar')

will print out essentially what I'm looking for, namely:

<SQL>
SELECT *
FROM `df`
WHERE (`foo` = 'bar')

the problem is that this is merely printed out. In particular I don't see how to assign it to a string. I've tried appending %>% show_query() but I believe that has the same result (i.e., displaying the query rather conversion to a string). Appending %>% as.character() does produce something but it is not SQL (it's a character vector whose first element is "list(name = \"filter\", x = list(x = list(), vars = character(0)), dots = list(~foo == \"bar\"), args = list())").

Answer 1

You can capture the output:

library(dplyr)
x<-capture.output(dbplyr::lazy_frame() %>% filter(foo == 'bar'))
x
[1] "<SQL>"                 "SELECT *"              "FROM `df`"             "WHERE (`foo` = 'bar')"

or

dbplyr::lazy_frame() %>% filter(foo == 'bar')%>%capture.output

Answer 2

Use remote_query to get an c("sql", "character") and then convert that to character.

library(dbplyr)

lazy_frame() %>%
  filter(foo == 'bar') %>%
  remote_query %>%
  as.character
## [1] "SELECT *\nFROM `df`\nWHERE (`foo` = 'bar')"