How do I delete all columns that contain NA only with dbplyr?

I want to delete all columns of a dataframe which contain NA values only, within the dbplyr syntax.
With the normal R syntax this is no problem, see here: https://stackoverflow.com/questions/2643939/remove-columns-from-dataframe-where-all-values-are-na
Here is a possibility in R:

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

df <- data.frame(a = NA,
                 b = seq(1:5), 
                 c = c(rep(1, 4), NA))

df %>% purrr::discard(~all(is.na(.)))
# desired output:
# A tibble: 5 × 2
      b     c
  <int> <dbl>
1     1     1
2     2     1
3     3     1
4     4     1
5     5    NA

But now I want to do this in the dbplyr syntax. For this the following example:

# Creating test database
con <- dbConnect(RSQLite::SQLite(), "")
# Inserting test table
dbWriteTable(con, "df", df)

con %>% 
  tbl("df") %>% 
  purrr::discard(~all(is.na(.))) %>% 
  collect()

#Error in UseMethod("collect") : 
# no applicable method for 'collect' applied to an object of class "list"

Can anyone help me find a solution within dbplyr?

Revised answer - remove columns where every value is NA

The idea here is check each column for the number of NAs and compare against the number of rows:

df = con %>% tbl("df")

num_rows = df %>%
  ungroup() %>%
  summarise(num = n()) %>%
  collect() %>%
  pull(num)

cols_to_remove = c()

for(col in colnames(df)){
  num_na = df %>%
    ungroup() %>%
    mutate(is_na = ifelse(is.na(!!sym(col)), 1, 0)) %>%
    summarise(num_na = sum(is_na)) %>%
    collect() %>%
    pull(num_na)

  if(num_na == num_rows){
    cols_to_remove = c(cols_to_remove, col)
  }
}

local_df = df %>%
  select(-all_of(cols_to_remove)) %>%
  collect()

Original answer - removes rows where every value is NA

Here is one approach:

df = con %>% tbl("df")

df = df %>% mutate(all_na = TRUE)

for(col in colnames(df)){
  df = df %>%
    mutate(all_na = ifelse(!is.na(!!sym(col)), FALSE, all_na))
}

df %>%
  filter(all_na != TRUE) %>%
  select(-all_na)

The idea is to create a new column to store whether NAs are found in the row. By iterating through all the rows and updating this column, we create an indicator column for the presence of all NA values.

Syntax explanation

!!sym(.) is an rlang feature. It converts from the variable col containing the string "my_column" to the column name my_column.

Instead of using !!sym(.) inside select we use all_of for a similar purpose.