Rethinkdb - filtering by value in another table

Question

In our RethinkDB database, we have a table for orders, and a separate table that stores all the order items. Each entry in the OrderItems table has the orderId of the corresponding order.

I want to write a query that gets all SHIPPED order items (just the items from the OrderItems table ... I don't want the whole order). But whether the order is "shipped" is stored in the Order table.

So, is it possible to write a query that filters the OrderItems table based on the "shipped" value for the corresponding order in the Orders table?

If you're wondering, we're using the JS version of Rethinkdb.

UPDATE:

OK, I figured it out on my own! Here is my solution. I'm not positive that it is the best way (and certainly isn't super efficient), so if anyone else has ideas I'd still love to hear them.

I did it by running a .merge() to create a new field based on the Order table, then did a filter based on that value.

A semi-generalized query with filter from another table for my problem looks like this:

r.table('orderItems')
  .merge(function(orderItem){
    return {
      orderShipped: r.table('orders').get(orderItem('orderId')).pluck('shipped') // I am plucking just the "shipped" value, since I don't want the entire order
    }
  })
  .filter(function(orderItem){
    return orderItem('orderShipped')('shipped').gt(0) // Filtering based on that new "shipped" value
  })

A few sample records to see your schema would be helpful... – InternalFX Jul 28 '14 at 17:50 — InternalFX, Jul 28 '14 at 17:50

score 1 · Accepted Answer · answered Sep 04 '14 at 06:32

1

it will be much easier.

r.table('orderItems').filter(function(orderItem){
    return r.table('orders').get(orderItem('orderId'))('shipped').default(0).gt(0)
})

And it should be better to avoid result NULL, add '.default(0)'

answered Sep 04 '14 at 06:32

Masatoshi

197
1
6

score 0 · Answer 2 · answered Aug 13 '15 at 21:54

It's probably better to create proper index before any finding. Without index, you cannot find document in a table with more than 100,000 element.

Also, filter is limit for only primary index.

A propery way is to using getAll and map

First, create index:

 r.table("orderItems").indexCreate("orderId")
 r.table("orders").indexCreate("shipStatus", r.row("shipped").default(0).gt(0))

With that index, we can find all of shipper order

r.table("orders").getAll(true, {index: "shipStatus"})

Now, we will use concatMap to transform the order into its equivalent orderItem

r.table("orders")
 .getAll(true, {index: "shipStatus"})
 .concatMap(function(order) {
      return r.table("orderItems").getAll(order("id"), {index: "orderId"}).coerceTo("array")
 })

Rethinkdb - filtering by value in another table

2 Answers2