RethinkDB index for filter + orderby

Question

Lets say a comments table has the following structure:

id | author | timestamp | body

I want to use index for efficiently execute the following query:

r.table('comments').getAll("me", {index: "author"}).orderBy('timestamp').run(conn, callback)

Is there other efficient method I can use?

It looks that currently index is not supported for a filtered result of a table. When creating an index for timestamp and adding it as a hint in orderBy('timestamp', {index: timestamp}) I'm getting the following error:

RqlRuntimeError: Indexed order_by can only be performed on a TABLE. in:

Answer 1

This can be accomplished with a compound index on the "author" and "timestamp" fields. You can create such an index like so:

r.table("comments").index_create("author_timestamp", lambda x: [x["author"], x["timestamp"]])

Then you can use it to perform the query like so:

r.table("comments")
 .between(["me", r.minval], ["me", r.maxval]
 .order_by(index="author_timestamp)

The between works like the get_all did in your original query because it gets only documents that have the author "me" and any timestamp. Then we do an order_by on the same index which orders by the timestamp(since all of the keys have the same author.) the key here is that you can only use one index per table access so we need to cram all this information in to the same index.

Answer 2

It's currently not possible chain a getAll with a orderBy using indexes twice. Ordering with an index can be done only on a table right now.

NB: The command to orderBy with an index is orderBy({index: 'timestamp'}) (no need to repeat the key)

Answer 3

The answer by Joe Doliner was selected but it seems wrong to me.

First, in the between command, no indexer was specified. Therefore between will use primary index.

Second, the between return a selection

table.between(lowerKey, upperKey[, {index: 'id', leftBound: 'closed', rightBound: 'open'}]) → selection

and orderBy cannot run on selection with an index, only table can use index.

table.orderBy([key1...], {index: index_name}) → selection<stream>
selection.orderBy(key1, [key2...]) → selection<array>
sequence.orderBy(key1, [key2...]) → array

Answer 4

You want to create what's called a "compound index." After that, you can query it efficiently.

//create compound index
r.table('comments')
.indexCreate(
  'author__timestamp', [r.row("author"), r.row("timestamp")]
)

//the query
r.table('comments')
.between(
  ['me', r.minval],
  ['me', r.maxval],
  {index: 'author__timestamp'}
)
.orderBy({index: r.desc('author__timestamp')})  //or "r.asc"
.skip(0)     //pagi
.limit(10)   //nation!

I like using two underscores for compound indexes. It's just stylistic. Doesn't matter how you choose to name your compound index.

Reference: How to use getall with orderby in RethinkDB