How to solve error "no applicable method for 'show_query' applied to an object of class "data.frame""

Solution 1:

show_query() translates the dplyr syntax into query code for the backend you are using.

A database backend using dbplyr will result in an SQL query (as a data.table backend using dtplyr will result in a DT[i,j,by] query).

show_query doesn't need to have a method to translate dplyr syntax applied to a data.frame backend to itself, hence the error message you're getting.

An easy way to get an SQL query result is to transform the data.frame into an in-memory database with memdb_frame:

memdb_frame(iris) %>% 
  filter(Species == "setosa")  %>% 
  summarise(mean.Sepal.Length = mean(Sepal.Length),
            mean.Petal.Length = mean(Petal.Length))  %>% show_query()

<SQL>
SELECT AVG(`Sepal.Length`) AS `mean.Sepal.Length`, AVG(`Petal.Length`) AS `mean.Petal.Length`
FROM `dbplyr_002`
WHERE (`Species` = 'setosa')

Solution 2:

dbplyr translation of R commands to SQL only works for remote tables. show_query() reveals the translated query that would be used to fetch data from the database. If the table is in local R memory then there is no need for SQL translation.

Part of the reason for this is that dbplyr has different translations defined for different databases. So without knowing what flavor of SQL/database you are using it can not determine the correct translation.

If you want to produce translations without connecting to a database, you can use simulated connections:

library(dbplyr)
library(dplyr)

data(iris)

# SQL server translation
remote_df = tbl_lazy(iris, con = simulate_mssql())
remote_df %>%
  filter(Species == 'setosa') %>%
  head() %>%
  show_query()

# MySQL translation
remote_df = tbl_lazy(iris, con = simulate_mysql())
remote_df %>%
  filter(Species == 'setosa') %>%
  head() %>%
  show_query()

These will produce slightly different SQL translations TOP vs LIMIT keywords.