Check if record exists with dbplyr

Question

In order to check whether a record exists in an SQL table (say "Products"), the top voted answer in sql - Fastes way to determine if record exists suggests to use

IF EXISTS (SELECT * FROM Products WHERE id = '?') 
...

Given a connection con to an SQL database, is there a way to perform such an action on the table dplyr::tbl(con)? One could of course use %>% dplyr::filter(id %in% !!x) to select only the relevant rows and compare the result with the record (here x). This seems inefficient, though.

score 1 · Answer 1 · answered Nov 10 '20 at 20:05

The IF EXISTS ... line you quote is part of a transactional sql statement, rather than a pure query fetching data from a table. I have not found any way to have dbplyr translate transactional sql statements, only queries. In general, if using dbplyr I would use R for the control statements rather than transactional sql.

What I would recommend instead is consistent with the accepted answer in the question you linked:

id_to_find <- 1234
remote_table <- dplyr::tbl(db_connection, from = tbl_name)

# create query
fetched_id <- remote_table %>%
  filter(id == id_to_find) %>%
  select(id) %>%
  head(1)

# validate query
show_query(fetched_id)

# fetch result into R
fetched_id <- fetched_id %>%
  collect()

This will return to R a 1x1 dataframe if the id exists, or a 0x1 dataframe if it does not exist. This can then be checked with if(nrow(fetched_id) == 1){....

Alternatively, you might want to take a look at dbExecute in the DBI package. This lets you pass a text string from R to SQL. So you could create a transactional sql statement in R and pass this to the database without dbplyr.

One last note, dbplyr does have a translation to EXISTS for sql server. This occurs if you do a semi-join. So if your application can be written with a semi-join you may be able to approach the problem this way.

Check if record exists with dbplyr

1 Answers1