# 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)