I'm trying to implement the following workflow:
- the user select the categorical and numerical filters it wants.
- once a button is pressed, based on these filters, a complex query is run against a Presto database and it is stored in memory as a dataframe.
- successive changes to the filters should not be calling the database, but filtering the in-memory dataframe.
How can this be implemented ? I unsuccessfully tried the following:
tableMod <- function(input, output, session, dims, thresholds, cats, search) {
observeEvent(search(), {
output$table <- renderDataTable({
if (search() == TRUE) {
baseTable <- genBaseTable(dims, thresholds, cats)
table <- reactifyTable(baseTable, dims, thresholds, cats)
}
else {
table <- reactive(empty_table)
}
datatable(table(), rownames = FALSE, selection = "none", options = list(pageLength = 100, lengthChange = FALSE, searching = FALSE))
})
})
}
genBaseTable <- function(dims, thresholds, cats) {
isolate({
print("querying database")
pool %>% tbl(TableSitesDPerfo) %>%
filter(
imps >= thresholds$imps(),
clicks >= thresholds$clicks(),
ctr >= thresholds$ctr()
) %>%
group_by_at(dims()) %>%
summarise(
imps = sum(imps),
clicks = sum(clicks),
jcost = sum(jcost)
) %>%
mutate(
ctr = clicks / imps
) %>%
ungroup() %>%
collect()
})
}
filterPerformance <- function(table, operator, imps, clicks, ctr, jcost) {
reactive({
if (operator() == "AND") {
filter(table, (imps >= imps()) & (clicks >= clicks()) &
(ctr >= ctr() / 100) & (jcost >= jcost()))
}
else if (operator() == "OR") {
filter(table, (imps >= imps()) | (clicks >= clicks()) |
(ctr >= ctr() / 100) | (jcost >= jcost()))
}
})
}
reactifyTable <- function(table, dims, thresholds, cats) {
table_perfo <- filterPerformance(
table, thresholds$operator, thresholds$imps, thresholds$clicks, thresholds$ctr, thresholds$jcost
)
}
I expected genBaseTable not to be called after the first time since it is isolated and filtering work at the dataframe level on baseTable. However, genBaseTable keeps getting called when I make changes to the filters.
- also asked in stackoverflow: https://stackoverflow.com/questions/55699546/isolating-database-queries-in-shiny