How to use getall with orderby in RethinkDB

rethinkdb

Solution

RethinkDB doesn't support efficient index intersection (the Github issue to add this is #809), but you could implement this query efficiently by adding a compound index for the 'id' and 'timestamp' indexes.

If your result set is small enough, though, the `orderBy` could just be done completely in-memory by dropping the 'index' optarg:

r.table("test").getAll(1, {index: "id"}).orderBy("timestamp")

To do this efficiently for large result sets, you would need an index. Assuming your 'id' and 'timestamp' indexes correspond directly to fields in your rows, adding the index would look like:

r.table("test").indexCreate("id_time",
                            function(row) {
                                return [row("id"), row("timestamp")];
                            })

To get all the rows with `id=1` and sort by the timestamp, you would then run:

r.table("test").between([1], [2], {"index": "id_time"})
               .orderBy({"index": "id_time"})

In addition, going back to the original query you posted, you could query between two timestamps for `id=1` by running:

r.table("test").between([1, <time 1>], [1, <time 2>], {"index": "id_time"})
               .orderBy({"index": "id_time"})

Problem

i want to list records where id=1 between two timestamps and finally order them according to timestamp. Mysql query something: ``` Select * from test where (timestamp between 100099323 AND 1423699323) AND id=1 order by timestamp ``` there are more than 5 million documents in rethink database. I tried using index for the simple mysql query: ``` Select * from test where id=1 order by timestamp ``` and Rethinkdb query is: ``` r.table('test').getAll(1, {index: 'id'}).orderBy({index: 'timestamp'}) ``` but i am getting error: ``` RqlRuntimeError: Indexed order_by can only be performed on a TABLE or TABLE_SLICE in: r.table("test").getAll(1, {index: "id"}).orderBy({index: "timestamp"}) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ``` Any suggestion?

Original source