SQLite和Shiny/flexdashboard:无法在SQL查询中嵌入reactiveValues()对象

library(shiny)
library(flexdashboard)
library(RSQLite)

# 创建一个SQLite数据库连接
db <- dbConnect(RSQLite::SQLite(), "example.db")

# 创建一个reactiveValues对象
values <- reactiveValues(filter = "all")

# 定义一个函数来执行SQL查询
run_query <- function(filter_value) {
  query <- paste("SELECT * FROM your_table WHERE category =", filter_value)
  result <- dbGetQuery(db, query)
  return(result)
}

# Shiny服务器逻辑
server <- function(input, output) {
  # 当filter值改变时，重新运行查询
  filtered_data <- reactive({
    run_query(values$filter)
  })

  # 输出结果
  output$data_table <- renderTable({
    filtered_data()
  })

  # 更新filter值的示例
  observeEvent(input$update_filter, {
    values$filter <- input$filter_value
  })
}

# Shiny UI
ui <- fluidPage(
  selectInput("filter_value", "Filter by Category:", choices = c("all", "A", "B", "C")),
  actionButton("update_filter", "Update Filter"),
  tableOutput("data_table")
)

# 创建flexdashboard
flex_dashboard(
  title = "SQLite and Shiny Example",
  server = server,
  ui = ui
)