shiny app data sources reactive graph refactor

[ergz]

the app uses a number of different approaches for fetching, and the updating is inconsistent/unclear, a refactor is needed to keep this reactive graph consistent and clear.

[ergz]

the strategy should be to make the big data fetches the least amount of times possible, we can do big data fetches in global.R or at the top of server.R, then any subsequent filter on this data can be made via reactives within the server.R, here is an example of I refactored the sample_status:

Make a function that runs the big data fetch

DB_get_sample_status <- function() {
  DBI::dbGetQuery(
    con,
    "SELECT t1.sample_id, sc.status_code_name as status, t1.updated_at, t1.comment as plate_comment FROM sample_status AS t1
      INNER JOIN (
        SELECT sample_id, MAX(id) AS max_id
        FROM sample_status
      GROUP BY sample_Id
    ) AS t2 ON t1.id = t2.max_id join public.status_code sc on sc.id = t1.status_code_id;"
  )
}

Make the fetch happen when a button is pressed (additional logic here is so that the fetch happens once when the app starts and therefore there is data in the tab when user first visits):

latest_sample_status <- eventReactive(list(input$sample_status_refresh, initial_load()), {
    logger::log_info("Fetching latest results using sample status query")
    DB_get_sample_status()
  })

then subsequent smaller filters operate on this reactive and not the full database fetch:

selected_all_sample_status <- reactive({

    re <- ifelse(input$sample_status_season == 2023, "\\b\\w{3}23", "\\b\\w{3}24")
    data <- latest_sample_status() |> filter(str_detect(sample_id, re))

    if(input$sample_status_filter != "All") {
      data <- data |>
        dplyr::filter(status == input$sample_status_filter)
    }
    if(input$location_filter != "All") {
      data <- data |>
        dplyr::filter(stringr::str_detect(sample_id, input$location_filter))
    }

    data

  })