Shiny pass input into dbplyr filter pipe

Question

I'm trying to use a textual input to filter a postgre table in a shiny dashboard. I need to filter a certain column that is selected from a selectinput. Problem is that column names of the table start with "_" i.e. "_7_track", so I have a lot of trouble with the correct expression.

that's my server code

column_to_look<-paste0("_",input$column_to_look,"_track")
dbDataCon<-DBI::dbConnect(RPostgreSQL::PostgreSQL(), user='xxx', password='xxx', dbname='xxx', host='nnn.nnn.nn.nnn')
    db_tracing<-dplyr::tbl(dbDataCon,'r_tracing_mfg') %>% 
      filter(column_to_look == "P321LM000011")%>%
      collect()

where input$column_to_look is a number (7) and the string column_to_look become "_7_track".

I get a 0*0 table, if I ask for query the translation is:

db_tracing %>% show_query()

<SQL>
SELECT *
FROM "r_tracing_mfg"
WHERE ('_7_track' = 'P321LM000011')

DBeaver (that I use to work on db) generate this query: SELECT * FROM r_tracing_mfg
WHERE "_7_track" = 'P321LM000011'

that works.

If I type directly in dbplyr's pipe this code:

db_tracing<-dplyr::tbl(dbDataCon,'r_tracing_mfg') %>% 
      filter(`_7_track` == "P321LM000011") %>%
      collect()

The request works and I obtain a table with n*n matched observations. In this case show_query() gives me:

<SQL>
SELECT *
FROM "r_tracing_mfg"
WHERE ("_7_track" = 'P321LM000011')

So how can I reproduce this second behaviuor? I've tryed, of course in vain, with

paste0("`_",input$column_to_look,"_track`")

score 1 · Accepted Answer · answered Apr 02 '21 at 14:27

I think that wrapping your paste0(...) in !!sym() will work. I'd try:

library(glue)
column_to_look<-glue_sql("_{input$column_to_look}_track")

dbDataCon<-DBI::dbConnect(RPostgreSQL::PostgreSQL(), user='xxx', password='xxx', dbname='xxx', host='nnn.nnn.nn.nnn')

db_tracing<-dplyr::tbl(dbDataCon,'r_tracing_mfg') %>% 
      filter(!!sym(column_to_look) == "P321LM000011")%>%
      collect()

using glue_sql instead of paste0

Shiny pass input into dbplyr filter pipe

1 Answers1