Use any() with dbplyr for grouped database query in R

user_1904298 0
Viewed 192

I would like to filter only samples that contain one or more of two unique strings. Basically, I only want to retain samples that have at least one al and one ph code in the same sample.

I am trying to replicate this dplyr grouped filter into dbplyr without success.

Here is the successful dplyr code I'm trying to replicate:

library(DBI)
library(odbc)
library(dbplyr)
library(tidyverse)


al_codes <- c('al1', 'al2', 'al3', 'al4')
ph_codes <- c('ph1', 'ph2', 'ph3', 'ph4')


df <- 
tibble(
  sample = c(rep('water',4), rep('surfacewater',4), rep('groundwater',4)),
  variable = c('al1', 'al2', 'al3', 'ph1', 'al1', 'al2', 'al3', 'al4', 'ph1', 'ph2', 'ph3', 'ph4')
)


# want to pull only records that have at least one al and one ph test code in same sample
df %>% 
  group_by(sample) %>%
  filter(any(al_codes %in% variable) & any(ph_codes %in% variable))

# A tibble: 4 x 2
# Groups:   sample [1]
# sample variable
# <chr>  <chr>   
# 1 water  al1     
# 2 water  al2     
# 3 water  al3     
# 4 water  ph1

This correctly filters only the 'water' sample because it has >=1 of al_codes and ph_codes.

However, when I try to replicate this in a dbplyr query to an oracle database I get the following error:

tbl(con, from =in_schema('DWH_DBA','DWH_ALL_SAMPLE_RESULTS')) %>% 
  group_by(SAMPLE_ID) %>%
  filter(any(al_test_codes %in% TEST_CODE) & any(ph_test_codes %in% TEST_CODE))

Error: nanodbc/nanodbc.cpp:1617: HY000: [Oracle][ODBC][Ora]ORA-00936: missing expression
...

Any suggestions on how to accomplish this in dbplyr would be very appreciated.

Session Info:

R version 3.6.3 (2020-02-29)

DBI_1.1.0; odbc_1.2.2; dbplyr_1.4.2; tidyverse_1.3.0

dbplyr dplyr filter r
1 Answers

Here is one option that works

library(dbplyr)
tb1 %>%
  group_by(sample) %>%
  filter(sum(variable %in% al_codes, na.rm = TRUE) > 0 & 
         sum(variable %in% ph_codes, na.rm = TRUE) > 0)

-output

# Source:   lazy query [?? x 2]
# Database: sqlite 3.30.1 [:memory:]
# Groups:   sample
#  sample variable
#  <chr>  <chr>   
#1 water  al1     
#2 water  al2     
#3 water  al3     
#4 water  ph1

data

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, df)
tb1 <- tbl(con, "df")
user_37322710
Related