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

Question

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

Simon.S.A. · Accepted Answer · 2022-01-18 19:44:51Z

1

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.

answered Jan 18, 2022 at 19:44

Simon.S.A.

7,0127 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related