CODEWITHSUNDEEP
mysqlrshinyshinydashboard
cartita
cartita

Reputation: 31

Shiny dashboard data refresh trigger

I am importing some data from a MySQL table, but so far for checking the data updates I was just reloading the app. I would like for this to happen dynamically but I am not sure how to implement the invalidateLater function on my code or if I should even go for it, because I am seeing people recommending ReactivePoll. Basically I am receiving data into my database from 5 to 5 minutes and I want to check the database in a certain interval too and update the number showed in my Value Box.

Thanks! 

     shinyServer(function(input, output, session) {

      conn <- dbConnect(RMySQL::MySQL(), 
                        dbname="db", 
                        host="localhost", 
                        user="root", 
                        password="password")

      table<- dbReadTable(conn = conn, name ='table1', value= as.data.frame(table))

      prd_number <- dbFetch(dbSendQuery(conn, "SELECT obj FROM table1 ORDER BY id DESC LIMIT 1"))


      output$prdnumber <- shinydashboard::renderValueBox({
        shinydashboard::valueBox(prd_number, "Number of products", icon=icon("tags"), color="purple"
        )
      })
   })

UPDATE

I accomplished what I wanted but now I have:

server.R

  prd_number <- reactivePoll(20000, session, checkFunc = function(){  

  conn <- dbConnect(RMySQL::MySQL(), 
                    dbname="db", 
                    host="localhost", 
                    user="root", 
                    password="password")

  val1 <- dbGetQuery(conn, "SELECT obj FROM table ORDER BY id DESC LIMIT 1")

  val2 <- dbGetQuery(conn, "SELECT date FROM table ORDER BY id DESC LIMIT 1")

  output$prdnumber <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(val1, "Number of items", icon=icon("tags"), color="purple"
    )
  })

  output$update <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(val2, "Last update", icon=icon("tags"), color="lime"
    )
      })
 dbDisconnect(conn)
  })

Upvotes: 2

Views: 1348

Answers (1)

JohnCoene
JohnCoene

Reputation: 2261

This is not how you'd use reactivePoll I believe, reactivePoll is, well a reactive.

reactivePoll runs checkFunc at every intervalMillis, if checkFunc returns a different value than previously then valueFunc is ran.

Also never update your output inside your reactives, move them outside of the reactive.

server <- function(input, output, session){
  prd_number <- reactivePoll(
    20000, # checkFunc is ran very 2 min
    session,
    checkFunc = function(){
      Sys.time() ## CHANGE THIS (see below)
    },
    valueFunc = function(){
      conn <- dbConnect(
        RMySQL::MySQL(), 
        dbname="db", 
        host="localhost", 
        user="root", 
        password="password"
      )

      val1 <- dbGetQuery(conn, "SELECT obj FROM table ORDER BY id DESC LIMIT 1")

      val2 <- dbGetQuery(conn, "SELECT date FROM table ORDER BY id DESC LIMIT 1")

      dbDisconnect(conn)
      return(list(val1 = val1, val2 = val2)) # return both values
    }
  )

  output$prdnumber <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(prd_number()$val1, "Number of items", icon=icon("tags"), color="purple")
  })

  output$update <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(prd_number()$val2, "Last update", icon=icon("tags"), color="lime")
  }) 
}

In the above, change the checkFunc. It currently returns the current time so every 2 min valueFunc is ran. You do not want that.

In valueFunc, you should ping the database with a cheap SQL statement to check if the table(s) of interest have been updated: i.e.: return last row => if the last row has changed, valueFunc is ran and your outputs (that use prd_number) will be updated.

Upvotes: 1

Related Questions