CODEWITHSUNDEEP
mysqlsqlrethinkdb
Borgy Manotoy
Borgy Manotoy

Reputation: 2038

RethinkDb query current date time between two date columns

Sorry if this a really a basic/noob question, but I am really new to rethinkDb coming from SQL.

(1)

Below is the query in SQL that I wanted to convert into ReThinkDb. This may be really simple, but I cannot make it right.

SQL equivalent:

select *  
from reservations
where
    room_id = 'b1a7ddd3-ddfd-4624-8e85-79b47fb19f99' and  
    now() between reservation_start and reservation_end

RethinkDb Query (with error):

r.db("myDb").table("reservations").filter(function(doc){ 
    return 
        doc("room_id").eq("b1a7ddd3-ddfd-4624-8e85-79b47fb19f99")
            .and( r.now().between(doc("reservation_start ").date(), doc("reservation_end").date()) )
    }
)

I just want to return the reservations scheduled today, or ongoing if it is already started but not yet finished (end date-time).

(2)

Reservations have a column or field for attendees which is a list/array of name/email:

attendees: [
  {"name": "Attendee 1", "email": "[email protected]"},
  {"name": "Attendee 2", "email": "[email protected]"},
  {"name": "Attendee 3", "email": "[email protected]"},
]

I would like to add a filter to check that an email exists in the list of attendees.

It is like querying for: The email [email protected] has a reservation today for room 101.

If querying with attende email is not possible or complicated... I don't mind as I can do the checking in my application. What matters is the rethinkdb query equivalent for now() between dateColumnStart and dateColumnEnd.

Update: Added sample data stored in DB (RethinkDB)

{
"attendees": [
    {
        "email": [email protected], »
        "name":  "Dummy User 101"
    } ,
    {
        "email": [email protected], »
        "name":  "Dummy User 102"
    }
] ,
"id":  "45qum29cel0cm4ejl2obi6pttj" ,
"room_id":  "7cc8e51d-e3fa-4d84-b7e6-9ebf8975754a" ,
"reservation_end":  "2018-11-23T02:00:00" , //10AM (GMT8)
"reservation_start":  "2018-11-19T00:00:00" , //8AM (GMT8)
"details":  "Week event 8AM-10AM Test"
}

{
"attendees": [
    {
        "email": [email protected], »
        "name":  "Dummy User 103"
    } ,
    {
        "email": [email protected], »
        "name":  "Dummy User 101"
    } ,
    {
        "email": [email protected], »
        "name":  "Dummy User 102"
    }
] ,
"id":  "6ejq8h6tvlpnjiskvt4kthfmss_20181123T060000Z" ,
"room_id":  "7cc8e51d-e3fa-4d84-b7e6-9ebf8975754a" ,
"reservation_end":  "2018-11-23T07:00:00" , //3PM (GMT8)
"reservation_start":  "2018-11-23T06:00:00" , //2PM (GMT8)
"details":  "Test Reservation"
}

Thanks!

Upvotes: 1

Views: 254

Answers (1)

taygetos
taygetos

Reputation: 3040

(1)

You cannot use between in the filter. Between gets you all documents between two keys. See docu here: https://www.rethinkdb.com/api/javascript/between/

What you need is the "during" keyword.

r.db("myDB").table("reservations").filter(function(doc){ 
    return doc("room_id").eq("b1a7ddd3-ddfd-4624-8e85-79b47fb19f99")
        .and( r.now().during(
           r.ISO8601(doc("reservation_start"),{defaultTimezone:"+08:00"}),
           r.ISO8601(doc("reservation_end"),{defaultTimezone:"+08:00"})))
    }
)

Upvotes: 1

Related Questions