Tuesday, April 26, 2016

SQL like IN clause in RethinkDB

Let’s consider a simple example of product. We need to get all product where product ids in a given list. A product document goes like this-
[
      {
         “ID”:0,
         “Name”:”Bread”,
         “Description”:”Whole grain bread”,
         “ReleaseDate”:”1992-01-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:4,
         “Price”:”2.5″
      },
      {
         “ID”:1,
         “Name”:”Milk”,
         “Description”:”Low fat milk”,
         “ReleaseDate”:”1995-10-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:3,
         “Price”:”3.5″
      },
      {
         “ID”:2,
         “Name”:”Vint soda”,
         “Description”:”Americana Variety – Mix of 6 flavors”,
         “ReleaseDate”:”2000-10-01T00:00:00″,
         “DiscontinuedDate”:null,
         “Rating”:3,
         “Price”:”20.9″
      },
   …
   ]
An equivalent SQL statement in this case will be –
SELECT ID, Name, Description from Products WHERE ID IN (1,2)
To implement this we can use filter with expr and map like below-
r.table('products').filter(function(product) {
        return r.expr([1,2]).contains(product('ID'))
    }).map(function (product) {
        return {
            id : product('ID'),
            name : product('Name'),
            description: product('Description')
        }
    });