Replace operators from SQL query through dplyr/DBI/RJDBC

tidyverse
, ,
1

Hi,

I'm trying to use a dplyr pipeline for querying data inside a mapd database.
My current problem with it is that mapd SQL is SQL-92 conformant, and thus, doesn't accept R basic != operator, only SQL-92 <>.

I made a request to mapd to support the != operator, but in the meantime, I was wondering if you know any way to automatically convert my != characters, generated with my dplyr's query, to <>.

Considering my code is in a dplyr pipeline, agnostic regarding the db hosting the data, I can't write the request, do some string manipulation (like a basic str_replace) and then send the request : it has to work on local data.frames too.

I was wondering if DBI/dbplyr allows to restrict the operators set, and thius, fix my problem.

I'm querying the db through RJDBC package (using JDBC driver).

Thanks for any idea

2

(found a small trick right now, replacing filter(a !="b") by filter(!(a %in% "b"))
This is properly converted as WHERE (NOT(("a" IN ('b'))))), which is a nice temp fix for me :slight_smile:

3

Interesting question and follow-up. I would note that the SQL object is just an extension of character, so you should be able to use classic string manipulation as long as you turn it back into a SQL object afterwards. Curious to hear how this works out for you!

raw_sql <- dbplyr::sql("select 1 != 2")                             
print(raw_sql)                                                      
#> <SQL> select 1 != 2
class(raw_sql)                                                      
#> [1] "sql"       "character"
                                                                    
modified <- dbplyr::sql(stringr::str_replace_all(raw_sql,"!=","<>"))
print(modified)                                                     
#> <SQL> select 1 <> 2