can't display a lazy tbl

[ilyaminati]

When the data source is a tbl_lazy from dplyr, DT fails to render, returns error message.

Error in datatable: 'data' must be either a matrix or a data frame

There's no error with shiny::renderTable, because it calls collect(). But DT::renderDataTable should not call collect() on the whole data source, because its rendered data table can be paginated and filtered, i.e., the data can be collected lazily.

con <- DBI::dbConnect(...) # same with pool::dbPool(...)

ui <- fluidPage(
  DT::dataTableOutput("tbl")
)

server <- function(input, output, session) {
  dat <- reactive({
    con %>%
      tbl("mytable"))
  })

  output$tbl <- DT::renderDataTable({
    dat()
  })
}

shinyApp(ui, server)

[shrektan]

Sorry I'm confused of what you are expecting.

Why not call collect() and pass a data.frame to DT::renderDataTable()?

Also, call collect() automatically for tbl_lazy only saves one command but it may cause troubles if the table in the database is very large.

[ilyaminati]

There are two parts here.

1. Bug: the error message is unclear. It would be helpful to suggest that the user should call collect() when the input is a tbl_lazy.

2. Request for enhancement: when the input is a tbl_lazy, in interactive mode, in the R console, the input still displays ten lines of the tibble. Similarly, DT could display ten lines, and provide a button to display ten more lines, i.e., paginate the lazy tibble. DT already paginates a non-lazy tibble by default, so this would extend pagination to the lazy tibble.

[yihui]

We can certainly improve the error message.

For 2, I think it is more challenging but is what I was interested to do (#194). Currently DT does not take advantage of databases at all, and it definitely should. The implementation is probably not trivial, though.