Shiny - postgres database query using dplyr and a reactive value

Question

I'm using a postgres db for a shiny app, and I'm having trouble getting a dplyr query to work.

I have the following reactive. si.division is a dataframe, and input$si_division is a select input:

si_division_selected <- reactive({
  si.division %>% 
    filter(division_name %in% input$si_division) %>% 
    select(division_code) %>% 
    unlist(use.names = FALSE)
})

I'm trying to pass this into a dplyr query using src_pool

industry_division_code <- src_pool(pool) %>% tbl("si_alldata") %>%
      translate_sql(division_code %in% si_division_selected()) %>% 
      select(industry_code)

I'm getting the following error:

Error in UseMethod: no applicable method for 'select_' applied to an object of class "c('sql', 'character')

I have also tried:

 industry_division_code <- src_pool(pool) %>% tbl("si_alldata") %>%
      filter(division_code %in% si_division_selected()) %>%
      select(industry_code)

Which returns:

Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "SI_DIVISION_SELECTED" LINE 5: WHERE ("division_code" IN SI_DIVISION_SELECTED()))

If I load the file into R instead of using the database I have no issues:

industry_division_code <- si_alldata %>%
    filter(division_code %in% si_division_selected()) %>%
    select(industry_code)

why do you use translate_sql (and why with two sets of parenthesis) instead of filter? — jess
– jess, Commented Mar 30, 2017 at 14:07
I updated my question to include the attempt using filter(). Also removed extra parenthesis, same result. — Michael
– Michael, Commented Mar 30, 2017 at 14:14
How does your query look like when you do %>% explain on it? Also, how many elements are in si_division_selected? %in% won't work with less than 2 elements. — jess
– jess, Commented Mar 30, 2017 at 14:34
only one element, but using == instead of %in% returned same result. explain() returns: <SQL> SELECT "industry_code" AS "industry_code" FROM (SELECT * FROM "si_alldata" WHERE ("division_code" = SI_DIVISION_SELECTED())) — Michael
– Michael, Commented Mar 30, 2017 at 14:46
Assigning the reactive and passing it to the query seems to work: Assign: si_division_selected <- si_division_selected() Then pass: industry_division_code <- collect(src_pool(pool) %>% tbl("si_alldata") %>% filter(division_code == si_division_selected) %>% select(industry_code)) — Michael
– Michael, Commented Mar 30, 2017 at 15:58

edgararuiz · Accepted Answer · 2017-10-04 02:25:11Z

0

I think if you want to keep using si_division_selected() as the value that is passed in the filter, then you should be able to use the rlang package to force the evaluation of the function, so the line would look like this: filter(division_code %in% !! si_division_selected()). Although, your current solution of just saving the results off to a variable would be my preferred avenue.

answered Oct 4, 2017 at 2:25

edgararuiz

6856 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Shiny - postgres database query using dplyr and a reactive value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related