How to preserve order in shiny app using datatables when sorting in the app?

Question

The first column of the datatable consits of names, while the second column uses characters which are a combination of numeric with comma delimiters, and characters for other values, for example, "1,000" , "2,000", "19,000", "Data missing", "Data suppressed". Using type = "num-fmt" I am able to get the datatable to display correctly when I run it as a function by itself, i.e. "1,000", "2,000", "19,000", and when using sort in the Rstudio terminal its ordering is correct, and as such when first displayed in the R shiny app it works. However, when using the sort options in the shiny interface, the ordering no longer works correctly, i.e. "1,000" , "19,000" , "2,000".

My understanding is that I must do the sorting in the server, or use java script, but I don't know how.

ui <- dashboardPage(

box(title = "Industries from selected region",
                    status = "danger",
                    solidHeader = TRUE,
                    DT::dataTableOutput("industry_tbl"),
                    width = 6)
)




server <- function(input, output, session) {
values <- reactiveValues(direction = "Exports",
                           year = "2020",
                           partner_country = "Spain",
                           industry = "Mining",
                           home_country = "UK")

output$industry_tbl<- DT::renderDT({
    industry_table_server(new_data,
                       values$year,
                       values$partner_country,
                       values$direction,
                       values$home_country)
  })

function:

industry_table_server <- function(dataset,
                               selected_year, 
                               selected_country,
                               selected_direction,
                               selected_region){

this_selection <- dplyr::filter(dataset,
                                  Year == selected_year,
                                  Country == selected_country,
                                  Direction == selected_direction,
                                  `Area name` == selected_region) %>%
    select(Industry, value)

DT::datatable(this_selection , 
                colnames = c("Industry",
                             paste0("£millions")),
                filter = "none",
                rownames = TRUE,
                extensions = c('Buttons'),
                options = list(
                  dom = 'Bftip',
                  buttons = c('copy', 'excel', 'print'),
                  searchHighlight = TRUE,
                  searchDelay = 0,
                  selection = "single",
                  pageLength = 10, # Shows 10 results
                  lengthMenu = c(5, 10),
                  columnDefs = list(list(className = 'dt-right', targets = c(0,2)), list(targets = c(2), type = "num-fmt"))
                )
  )
} ```

Stéphane Laurent · Answer

As said in the datatables website, regarding the type option:

Please note that if you are using server-side processing this option has no effect since the ordering and search actions are performed by a server-side script.

So you have to set server = FALSE in renderDT:

output$industry_tbl <- renderDT({
    industry_table_server(new_data,
                       values$year,
                       values$partner_country,
                       values$direction,
                       values$home_country)
}, server = FALSE)

How to preserve order in shiny app using datatables when sorting in the app?

1 Answers