Ndtyjky

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": "attendee1@test.com"},

  {"name": "Attendee 2", "email": "attendee2@test.com"},

  {"name": "Attendee 3", "email": "attendee3@test.com"},

]

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 attendee2@test.com 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": dummyUser101@gmail.com, »

        "name":  "Dummy User 101"

    } ,

    {

        "email": dummyUser102@gmail.com, »

        "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": dummyUser103@gmail.com, »

        "name":  "Dummy User 103"

    } ,

    {

        "email": dummyUser101@gmail.com, »

        "name":  "Dummy User 101"

    } ,

    {

        "email": dummyUser102@gmail.com, »

        "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!

(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"})))

    }

)