Pfthb

I am really new to ReThinkDB coming from MySQL and I have some tasks that uses ReThinkDB.

Now, I would like to implement something like...

MySQL:

select * 
from reservations
where 
 room_id = '7cc8e51d-e3fa-4d84-b7e6-9ebf8975754a' and
 ((reservation_start between `11-01-2018 00:00:00` and `11-30-2018 23:59:59`) OR
 (reservation_end between `11-01-2018 00:00:00` and `11-30-2018 23:59:59`))

ReThinkDB:

This is the equivalent query using filter(), thanks to @Peter:

r.db("myDb").table("reservations").filter(function(doc) 
 return 
 doc("room_id").eq("7cc8e51d-e3fa-4d84-b7e6-9ebf8975754a")
 .and(doc("reservation_start").gt("2018-12-01T00:00:00").and(doc("reservation_start").lt("2018-12-10T23:59:59"))
 .or(doc("reservation_end").gt("2018-12-01T00:00:00").and(doc("reservation_end").lt("2018-12-10T23:59:59"))))
 
)

But I am not sure how to convert the two date range checks into multiple between.

Thanks!

Depending on what format you store your dates:

Option 1:

r.db("myDB").table("reservations").filter(function (doc) 
 return doc("reservation_date").during(
 r.time(2018, 11, 10, 'Z'), r.time(2018, 11, 20, 'Z'));
)

option 2:

 r.db("myDB").table("reservations").filter(function(doc) 
 return doc("reservation_date").gt("2018-11-10T18:15:31.000000Z")
 .and(doc("reservation_date").lt("2018-11-20T18:15:31.000000Z")))

Alternative way for more performance:

create index on reservation date:

 r.db("myDB").table("reservations").indexCreate("reservation_date")

then use the following query:

 r.db("myDB").table('reservations')
 .between("2018-11-10T18:15:31.000000Z",
 "2018-11-20T18:15:31.000000Z", index: "reservation_date")

Now I understand your problem a little better

First to convert your query to java:

r.db("myDb").table("reservations").filter(doc ->
 doc.g("room_id").eq("7cc8e51d-e3fa-4d84-b7e6-9ebf8975754a")
 .and(doc.g("reservation_start").gt("2018-12-01T00:00:00").and(doc.g("reservation_start").lt("2018-12-10T23:59:59"))
 .or(doc.g("reservation_end").gt("2018-12-01T00:00:00").and(doc.g("reservation_end").lt("2018-12-10T23:59:59")))))

Now optimally You may want to use the room_id as a secondary index:

r.db("myDB").table("reservations").indexCreate("room_id")

And then you can use in your query as:

 r.db("myDb").table("reservations")
 .getAll("7cc8e51d-e3fa-4d84-b7e6-9ebf8975754a")
 .optArg("index","room_id")
 .filter(...)

"But I am not sure how to convert the two date range checks into
multiple between."

1.) Create a compound array index

r.db("myDb").table("reservations").indexCreate(
 "reservation_date", [r.row("reservation_start"), r.row("reservation_end")]

2.) use the between query

r.db("myDb").table("reservations").between(
 ["2018-12-01T00:00:00", "2018-12-01T00:00:00"], ["2018-12-10T23:59:59", "2018-12-10T23:59:59"], index: "reservation_date"
) 

// note: now this gets tricky when you need the room id as well :)