CODEWITHSUNDEEP
mysqldplyrtidyversedbplyr
Yomi.blaze93
Yomi.blaze93

Reputation: 425

How to query list of Id's in Database using dplyr in r

I'm new to using r to manipulate data from the database

I want to know how to query a list of Id's in a database table

I want a situation whereby the query returns all records of Id's if found

Before I used to query just one id with the code below

start_1<-tbl(connect, "accountbits")%>%
    filter(Tranx_id == "2022011813250866101336997")%>%
                        collect()

So it shows the query with details attached with the id.

I want to have many id's like the example below

start_2<-tbl(connect, "accountbits")%>%
        filter(Tranx_id = c("2022011813250866101336997","20220115675250866101336997"
         "202201181325086610143246997","2022015433250866101336997")%>%
                            collect()

I want it to bring all records attached to this id in the database

Thank you

Upvotes: 1

Views: 447

Answers (1)

Simon.S.A.
Simon.S.A.

Reputation: 6931

The R operator you are looking for is %in%. This checks set intersection:

c(1,3,5) %in% c(1,2,3,4)
# = (TRUE, TRUE, FALSE)

because 1 and 3 are in c(1,2,3,4).

You can type ?`%in%` at the console for help info about this operator (` is the backtick, located next to the number 1 in the top left corner of more keyboards).

There are dbplyr translations defined for %in% so a command like:

start_2 <- tbl(connect, "accountbits")%>%
  filter(Tranx_id %in% c("1234","2345","3456"))

will translate into SQL like:

SELECT *
FROM accountbits
WHERE Tranx_id IN (1234, 2345, 3456)

and collect() will pull those values into local R memory as expected.

Upvotes: 1

Related Questions