0

So, i created a program to optimize my marketing budget. As an input, the user need to set the total budget. I create this input variable as "cust_tot" in the Shiny UI code below:

ui <- fluidPage(
  titlePanel("Budget Optimizer"),
  sidebarLayout(
    sidebarPanel(
      numericInput("custo_tot", "Valor total do Investimento:", value = 3243452, min = 0),
      actionButton("submit", "Otimizar")
    ),
    mainPanel(
      plotOutput("grafico_investimento_atual")
    )
  )
)

In order to get the data, i need to query, but the variable "custo_tot" (inputed by the user) is also going to be part of the query:

in my original code, it was something like that:

sql <- " query here " 
sql = gsub("custo_rest_min_aux", custo_rest_min_aux, sql)
sql = gsub("custo_rest_max_aux", custo_rest_max_aux, sql)
sql = gsub("month_rest", sQuote(month_rest), sql)
sql = gsub("custo_tot", input$custo_tot, sql)
  
tb <- bq_project_query(projectid, sql)
df = bq_table_download(tb) %>% data.frame()

But i know that i cant simply use input$custo_tot because it shows the following error message:

Error in input$custo_tot : 
  Can't access reactive value 'custo_tot' outside of reactive consumer.
ℹ Do you need to wrap inside reactive() or observe()?

how should i deal with this?

Matheus Barreto
  • 23
  • 7
  • What does the server function look like? You posted the error but not the corresponding code where you call `input$custo_tot`. – LMc Aug 02 '23 at 20:50
  • Sorry, it was just an example. this code is inside the server function . The error i mentioned was when i tried to use sql = gsub("custo_tot", input$custo_tot, sql). I just wanna be able to use this input variable as a string to make a query. – Matheus Barreto Aug 02 '23 at 20:56

1 Answers1

1

As the error specifies, you cannot access the input list unless you are inside a reactive consumer:

server <- function(input, output, session) {
  sql <- reactive({
    sql <- " query here " 
    sql = gsub("custo_rest_min_aux", custo_rest_min_aux, sql)
    sql = gsub("custo_rest_max_aux", custo_rest_max_aux, sql)
    sql = gsub("month_rest", sQuote(month_rest), sql)
    sql = gsub("custo_tot", input$custo_tot, sql)
    sql
  })
  
}

This will make it a reactive so any time the inputs are updated this reactive will lazily update. Remember to use this reactive you need to call it like: sql().

LMc
  • 12,577
  • 3
  • 31
  • 43
  • I need to use the query to load dataset for further usage. Shoud i just do tb <- bq_project_query(projectid, sql()) ?? – Matheus Barreto Aug 02 '23 at 21:03
  • Using your code this is how you can construct the sql query string. – LMc Aug 02 '23 at 21:04
  • When i try to use the code below, i get the error: Erro: query is not a string (a length one character vector). – Matheus Barreto Aug 02 '23 at 21:09
  • In your question you have not provided information about many variables you mention: `custo_rest_min_aux`, `custo_rest_max_aux`, `month_rest`, and so forth. Until you provide a complete question it will be difficult to help you. – LMc Aug 02 '23 at 21:12
  • Please read about [how to make a great R reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and update your question accordingly. As it stands now your post is not reproducible. You will get better help if you can create a reproducible example. – LMc Aug 02 '23 at 21:12
  • My bad, they are not important because they are fixed numeric values that i also use on my query. The only problem that i'm having now is how to pass the sql() into the function bq_project_query(projectid, sql()). The goal here is to use sql() as an string to another function – Matheus Barreto Aug 02 '23 at 21:14
  • 1
    Your `bq_project_query` needs to be in a reactive consumer. Most things you want to do on the server side need to be in reactive consumers. You cannot, generally, just write code how you would normally in an R script. – LMc Aug 02 '23 at 21:15