Use any() to fetch data from SQLite database using dbplyr-CodePudding

I want to fetch data from a local SQLite database in R by using the any() function in combination with group_by to filter groups where at least one row is equal to a certain condition. It would probably help to finally learn SQL, however, until now I managed to do all my queries using dbplyr and I hope there is a dplyr solution for this problem as well.

db <- dbConnect(RSQLite::SQLite(), "test_db.sqlite")

test_table <- tibble(id = c(rep(1:3, each = 3)),
                     cond = c(rep("A", 8), "B"))

dbWriteTable(db, "table", test_table)

table <- tbl(db, "table")

With the table already in memory I can accomplish what I want easily using

test_table %>% 
  group_by(id) %>% 
  filter(any(cond == "B"))

which gives me

     id cond 
  <int> <chr>
1     3 A    
2     3 A    
3     3 B

However this does not work:

table %>% 
  group_by(id) %>% 
  filter(any(cond == "B"))

It results in the following error:

error: no such function: any

Is there a dbplyr workaround?

CodePudding user response：

Here is a solution that works:

library(dplyr)
library(DBI)

db <- dbConnect(RSQLite::SQLite(), "test_db.sqlite")

test_table <- tibble(id = c(rep(1:3, each = 3)),
                     cond = c(rep("A", 8), "B"))
test_table
### A tibble: 9 × 2
##     id cond 
##  <int> <chr>
##1     1 A    
##2     1 A    
##3     1 A    
##4     2 A    
##5     2 A    
##6     2 A    
##7     3 A    
##8     3 A    
##9     3 B    


dbWriteTable(db, "table", test_table)

table <- tbl(db, "table")

table %>%
  group_by(id) %>%
  filter(sum(cond == "B") > 0)

## Source:   lazy query [?? x 2]
## Database: sqlite 3.38.0 [test_db.sqlite]
## Groups:   id
#     id cond 
#  <int> <chr>
#1     3 A    
#2     3 A    
#3     3 B