Fuzzy Joins through sqlQuery() commands

[omario]

Using the R programming language, I have the following query I would like to run:

library(RODBC)
library(sqldf)

con = odbcConnect("some name", uid = "some id", pwd = "abc")

sample_query = sqlQuery(con, "select distinct * from table_a a 
inner join table_b b
on (a.date_1 between b.date_2 and b.date_3 and a.id1 = b.id1) or a.id2 = b.id2)

view(sample_query)

My Question: Is it possible to "pass" non-SQL statements through sqlQuery(), such as functions from the "dplyr" and "fuzzyjoin" libraries in R? For example:

library(fuzzyjoin)
library(dplyr)

sample_query = sqlQuery( stringdist_inner_join(table_1, table_2, by = "id2", max_dist = 2) %>%
  filter(date_1 >= date_2, date_1 <= date_3) )

view(sample_query)

Or will this just result in the above query being run "locally" (i.e. take much longer)? E.g.:

sample_query =  stringdist_inner_join(table_1, table_2, by = "id2", max_dist = 2) %>%
  filter(date_1 >= date_2, date_1 <= date_3) 

Thanks!

Note: Reproducible Example

table_1 = data.frame(id1 = c("123 A", "123BB", "12 5", "12--5"), id2 = c("11", "12", "14", "13"),
date_1 = c("2010-01-31","2010-01-31", "2015-01-31", "2018-01-31" ))

table_1$id1 = as.factor(table_1$id1)
table_1$id2 = as.factor(table_1$id2)
table_1$date_1 = as.factor(table_1$date_1)

table_2 = data.frame(id1 = c("0123", "1233", "125  .", "125_"), id2 = c("111", "112", "14", "113"),
date_2 = c("2009-01-31","2010-01-31", "2010-01-31", "2010-01-31" ),
date_3 = c("2011-01-31","2010-01-31", "2020-01-31", "2020-01-31" ))


table_2$id1 = as.factor(table_2$id1)
table_2$id2 = as.factor(table_2$id2)
table_2$date_2 = as.factor(table_2$date_2)
table_2$date_3 = as.factor(table_2$date_3)
  
    
    

[andresrcs]

@omario

Is it possible to "pass" non-SQL statements through sqlQuery(), such as functions from the "dplyr" and "fuzzyjoin" libraries in R?

Not exactly, everything executed on the sql server side has to be sql (unless you have R functions defined in the sql server itself), you can use the package dbplyr which translates several dplyr commands into sql under the hood but not all commands have a translation.