Skip to main content

AE 22: Natural language interfaces to data

Application exercise
Modified

November 19, 2026

01_querychat-app

library(querychat)

# connect to the LEGO database (read-only, so several apps can share it)
lego <- DBI::dbConnect(
  duckdb::duckdb(),
  dbdir = here::here("data/lego.duckdb"),
  read_only = TRUE
)

# create a QueryChat object for the `sets` table
qc <- QueryChat$new(
  data_source = lego,
  table_name = "sets",
  client = "anthropic/claude-sonnet-5",
  history = FALSE
)

# register the other tables so the LLM can join across them
qc$add_tables(lego, tables = setdiff(DBI::dbListTables(lego), "sets"))

# launch the built-in chat app
qc$app()

# Ask one question of each kind, then open the tool card under each answer:
#
# 1. Filter: "Show only Technic sets released since 2020"
# 2. Summarize: "Which year had the most sets released?"
# 3. Visualize: "Plot the number of sets released per year"
#
# For each one, note which querychat tool was called and what SQL it wrote.

02_extend-dashboard

# LEGO Explorer: a querychat dashboard
#
# The chat (left) writes SQL. Everything else on the page is ordinary Shiny
# code that reacts to querychat's reactive values for the `sets` table:
#
#   sets_tbl$df()     filtered sets   -> sets() -> value boxes, plots, table
#   sets_tbl$sql()    current SQL     -> "Current query" card
#   sets_tbl$title()  LLM's label     -> card headers
#   input$group       a traditional input -> theme plot only

library(tidyverse)
library(shiny)
library(bslib)
library(bsicons)
library(reactable)
library(querychat)

# data ---------------------------------------------------------------------

lego <- DBI::dbConnect(
  duckdb::duckdb(),
  dbdir = here::here("data/lego.duckdb"),
  read_only = TRUE
)
onStop(function() DBI::dbDisconnect(lego, shutdown = TRUE))

# themes nest up to three levels deep; find each theme's top-level ancestor
themes <- tbl(lego, "themes") |>
  collect()

theme_lookup <- themes |>
  select(theme_id = id, theme = name, parent_id) |>
  left_join(
    themes |> select(id, parent_name = name, grandparent_id = parent_id),
    by = join_by(parent_id == id)
  ) |>
  left_join(
    themes |> select(id, grandparent_name = name),
    by = join_by(grandparent_id == id)
  ) |>
  mutate(top_theme = coalesce(grandparent_name, parent_name, theme)) |>
  select(theme_id, theme, top_theme)

# querychat ----------------------------------------------------------------

qc <- QueryChat$new(
  data_source = lego,
  table_name = "sets",
  client = "anthropic/claude-sonnet-5",
  data_dict = here::here("data/data-dict.yaml"),
  greeting = here::here("data/greeting.md"),
  extra_instructions = paste(
    "The dashboard displays the `sets` table.",
    "When the user asks to show, filter, or sort sets (including by theme,",
    "minifigure, part, or color), call the dashboard update tool on the",
    "`sets` table, using subqueries on the other tables where needed."
  ),
  # start each class demo with a fresh conversation
  history = FALSE
)

# register the remaining tables so the LLM can join across them
qc$add_tables(
  lego,
  tables = c(
    "themes",
    "minifigs",
    "inventories",
    "inventory_sets",
    "inventory_minifigs",
    "inventory_parts",
    "parts",
    "part_categories",
    "colors"
  )
)

# ui -----------------------------------------------------------------------

ui <- page_sidebar(
  title = "LEGO Explorer",
  fillable = FALSE,
  sidebar = qc$sidebar(),
  layout_columns(
    fill = FALSE,
    value_box(
      title = "Sets",
      value = textOutput("n_sets"),
      showcase = bs_icon("box-seam"),
      theme = "primary"
    ),
    value_box(
      title = "Themes",
      value = textOutput("n_themes"),
      showcase = bs_icon("tags"),
      theme = "secondary"
    ),
    value_box(
      title = "Release years",
      value = textOutput("years"),
      showcase = bs_icon("calendar3"),
      theme = "info"
    ),
    value_box(
      title = "Median pieces per set",
      value = textOutput("median_parts"),
      showcase = bs_icon("bricks"),
      theme = "success"
    )
  ),
  layout_columns(
    col_widths = c(6, 6),
    card(
      full_screen = TRUE,
      card_header(textOutput("year_title")),
      plotOutput("year_plot")
    ),
    card(
      full_screen = TRUE,
      card_header(
        "Most common themes",
        # a traditional Shiny input living alongside the chat
        popover(
          bs_icon("gear", title = "Settings"),
          radioButtons(
            inputId = "group",
            label = "Group sets by",
            choices = c(
              "Top-level theme" = "top_theme",
              "Theme" = "theme"
            )
          )
        ),
        class = "d-flex justify-content-between align-items-center"
      ),
      plotOutput("theme_plot")
    )
  ),
  card(
    full_screen = TRUE,
    card_header(textOutput("table_title")),
    reactableOutput("sets_table")
  ),
  card(
    card_header("Current query"),
    verbatimTextOutput("sql")
  )
)

# server -------------------------------------------------------------------

server <- function(input, output, session) {
  qc_vals <- qc$server()

  # reactive sources provided by querychat for the `sets` table
  sets_tbl <- qc_vals$table("sets")

  # reactive conductor: the filtered sets, with theme names attached
  sets <- reactive({
    sets_tbl$df() |>
      as_tibble() |>
      left_join(theme_lookup, by = join_by(theme_id))
  })

  # the LLM's description of the current filter, if any
  filter_label <- reactive({
    sets_tbl$title() %||% "All sets"
  })

  # value boxes --------------------------------------------------------------

  output$n_sets <- renderText({
    nrow(sets()) |> scales::label_comma()()
  })

  output$n_themes <- renderText({
    n_distinct(sets()$theme_id) |> scales::label_comma()()
  })

  output$years <- renderText({
    req(nrow(sets()) > 0)
    yrs <- range(sets()$year)
    if (yrs[1] == yrs[2]) yrs[1] else str_c(yrs[1], "–", yrs[2])
  })

  output$median_parts <- renderText({
    # sets with 0 parts are bundles or have no inventory; leave them out
    parts <- sets()$num_parts[sets()$num_parts > 0]
    req(length(parts) > 0)
    median(parts) |> scales::label_comma()()
  })

  # plots --------------------------------------------------------------------

  output$year_title <- renderText({
    str_c("Sets released per year: ", filter_label())
  })

  output$year_plot <- renderPlot({
    req(nrow(sets()) > 0)

    sets() |>
      count(year) |>
      ggplot(mapping = aes(x = year, y = n)) +
      geom_col(fill = "#B31B1B") +
      labs(x = NULL, y = "Number of sets") +
      theme_minimal(base_size = 14)
  })

  output$theme_plot <- renderPlot({
    req(nrow(sets()) > 0)

    sets() |>
      count(group = .data[[input$group]], sort = TRUE) |>
      slice_head(n = 10) |>
      ggplot(mapping = aes(x = n, y = fct_reorder(group, n))) +
      geom_col(fill = "#B31B1B") +
      labs(x = "Number of sets", y = NULL) +
      theme_minimal(base_size = 14)
  })

  # table --------------------------------------------------------------------

  output$table_title <- renderText({
    filter_label()
  })

  output$sets_table <- renderReactable({
    sets() |>
      arrange(desc(year), set_num) |>
      select(img_url, set_num, name, year, theme, top_theme, num_parts) |>
      reactable(
        searchable = TRUE,
        defaultPageSize = 8,
        columns = list(
          img_url = colDef(
            name = "",
            width = 90,
            sortable = FALSE,
            cell = function(value) {
              img(src = value, height = "60px", alt = "")
            }
          ),
          set_num = colDef(name = "Set"),
          name = colDef(name = "Name", minWidth = 200),
          year = colDef(name = "Year"),
          theme = colDef(name = "Theme"),
          top_theme = colDef(name = "Top-level theme"),
          num_parts = colDef(name = "Pieces", format = colFormat(separators = TRUE))
        )
      )
  })

  # SQL ----------------------------------------------------------------------

  # the query the LLM wrote, shown for transparency
  # (ask the chat to "reset" to return to all sets)
  output$sql <- renderText({
    sets_tbl$sql() %||% "SELECT * FROM sets"
  })
}

shinyApp(ui = ui, server = server)

Acknowledgments