Introducing {qbr}

R javascript

An R interface to jquery Query-Builder.

Harry Fisher https://hfshr.xyz
2021-06-20

I’ve been spending quite a bit of time in the world of shiny recently, and a particular problem I was facing meant I decided to dip my toe into the seemingly magical world of htmlwidgets. In brief, I needed a way for users to interactively construct complex queries that would be used to interrogate a large database. While this is certainly possible to do using native shiny inputs, I was being lazy wanted to see if I could leverage something that had already been created. After a bit of googling I came across queryBuilder, a jquery plugin that provides UI component to create queries and filters. After playing around with the demos on the site, I was very impressed with the functionality and, and even better, someone had already made an R wrapper for the library! (See harveyl888/queryBuilder and Yannael/queryBuildR).

However this was where I started to run into some problems. The original queryBuilder has a vast array of settings that can be configured, including additional plugins and widgets that help add additional functionality to the builder. The existing R wrappers for queryBuilder only implemented a few of these settings, and I found myself wanting to access other settings which were not yet implemented. So I thought I’d have a go at creating my own version, taking inspiration from harveyl888/queryBuilder with the aim of including as much of the functionality as possible of the original queryBuilder.

Example

You can install qbr from github:

#install.packages("remotes")
remotes::install_github("hfshr/qbr")

and also check out the repository here: hfshr/qbr

Here is a quick example in shiny filtering the palmer penguins dataset with a few of the different settings configured. Code for the app is also below.

Show code
library(shiny)
library(qbr)

ui <- fluidPage(
  # Application title
  titlePanel("QueryBuilder demo"),
  mainPanel(
    fluidRow(
      column(
        8,
        queryBuilderOutput("querybuilder",
          width = 620,
          height = 300
        )
      ),
      column(
        4,
        uiOutput("txtValidation")
      )
    )
  ),
  fluidRow(
    column(
      width = 6,
      h3("dplyr filter"),
      verbatimTextOutput("txtFilterList"),
    ),
    column(
      width = 6,
      h3("SQL filter"),
      verbatimTextOutput("txtSQL")
    )
  ),
  fluidRow(
    column(
      width = 12,
      h3("dplyr filter applied to a table"),
      dataTableOutput("txtFilterResult")
    )
  )
)

# Define server logic required to draw a histogram
server <- function(input, output) {
  filters <- list(
    list(
      id = "species",
      label = "Species",
      type = "string",
      input = "select",
      description = "Shift-click to select multiple!",
      values = list("Adelie", "Gentoo", "Chinstrap"),
      multiple = TRUE,
      operators = c("equal", "not_equal", "in")
    ),
    list(
      id = "sex",
      label = "Sex",
      input = "checkbox",
      values = list(
        "male",
        "female"
      ),
      operators = c("equal", "not_equal", "in")
    ),
    list(
      id = "bill_length_mm",
      label = "Bill length",
      type = "integer",
      validation = list(
        min = 0,
        max = 100
      ),
      plugin = "slider",
      plugin_config = list(
        min = 0,
        max = 100,
        value = 0
      )
    )
  )

  output$txtValidation <- renderUI({
    if (isFALSE(input$querybuilder_validate) || is.null(input$querybuilder_validate)) {
      h3("INVALID QUERY", style = "color:red")
    } else {
      h3("VALID QUERY", style = "color:green")
    }
  })

  output$querybuilder <- renderQueryBuilder({
    queryBuilder(
      filters = filters,
      plugins = list(
        "sortable" = NA,
        "bt-tooltip-errors" = NA,
        "bt-checkbox" = list("color" = "primary"),
        "filter-description" = list("mode" = "bootbox"),
        "unique-filter" = NA
      ),
      display_errors = TRUE,
      allow_empty = FALSE,
      select_placeholder = "###"
    )
  })

  output$txtFilterList <- renderPrint({
    req(input$querybuilder_validate)
    filterTable(
      filters = input$querybuilder_out,
      data = palmerpenguins::penguins,
      output = "text"
    )
  })


  output$txtFilterResult <- renderDataTable(
    {
      req(input$querybuilder_validate)
      filterTable(
        filters = input$querybuilder_out,
        data = palmerpenguins::penguins,
        output = "table"
      )
    },
    options = list(
      pageLength = 5,
      scrollY = "200px",
      scrollX = TRUE
    )
  )

  output$txtSQL <- renderPrint({
    req(input$querybuilder_validate)
    input$querybuilder_sql
  })
}

# Run the application
shinyApp(ui = ui, server = server)

Summary

These settings barely scratch the surface of what is possible and the original queryBuilder site (https://querybuilder.js.org/) is well worth a visit to see the full potential of the widget. Also a special thanks to harveyl888/queryBuilder on which qbr is heavily based. If you find something that isn’t implemented feel free to open an issue hfshr/qbr.

Thanks for reading!

Reuse

Text and figures are licensed under Creative Commons Attribution CC BY 4.0. The figures that have been reused from other sources don't fall under this license and can be recognized by a note in their caption: "Figure from ...".

Citation

For attribution, please cite this work as

Fisher (2021, June 20). Data, Code & Coffee: Introducing {qbr}. Retrieved from https://hfshr.xyz/posts/2021-06-20-qbr/

BibTeX citation

@misc{fisher2021introducing,
  author = {Fisher, Harry},
  title = {Data, Code & Coffee: Introducing {qbr}},
  url = {https://hfshr.xyz/posts/2021-06-20-qbr/},
  year = {2021}
}