rethinkdb: How to orderby two attributes and use between on one of those

Question

we have a rethinkdb with tickets in it. They have a createdAt with a timestamp in milliseconds and a priority attribute.

e.g.

{
  createdAt: 12345,
  priority: 4,
  owner: "Bob",
  description: "test",
  status: "new"
}

rethinkdb.db('dev').table(tableId)
        .orderBy({index: 'createdAt'})
        .between(timeFrom,timeTo)
        .filter(filter)
        .skip(paginator).limit(20).run(this.connection);

We now have the following problem. We want a query that does two orderBy ... the first would be orderBy "priority" and also by "createdAt". So given the filter and the timespan it should return the tickets with the highest priority and inside the priority the oldest should be on top.

We tried to build a compound index with priority and createdAt. That did work, but the .between didn't work as intended on this index.

rethinkdb.db('dev').table('tickets').indexCreate('prioAndCreatedAt' [rethinkdb.row('priority'), rethinkdb.row('createdAt')]).run(this.connection)

with the query:

rethinkdb.db('dev').table(tableId)
        .orderBy({index: 'prioAndCreatedAt'})
        .between([rethinkdb.minval, timeFrom],[rethinkdb.maxval , timeTo])
        .filter(filter)
        .skip(paginator).limit(20).run(this.connection);

In our minds that should order by priority first and then by createdAt and with the .between we would ignore the priority (because of the .minval and .maxval) and the just get all the tickets between timeFrom and timeTo.

Buuuut also tickets where createdAt was smaller than timeFrom were returned. So this doesn't work like we planned. Its like this "problem": RethinkDB Compound Index Weirdness Using Between

But we cant figure out another way for this.

Answer 1

Since

it should return the tickets with the highest priority and inside the priority the oldest should be on top

Is there a reason not to simply use 2 orderBy?

r.db('dev').table('tickets')
  .between(timeFrom, timeTo, {index: 'createdAt'})
  .orderBy('createdAt')
  .orderBy(r.desc('priority'))

Then you can pipe your filter/paginator on this selection. It will provide tickets within the correct range, ordered by descending priority then by ascending creation date (the way SQL considers with ORDER BY priority, createdAt). And it avoids the (documented) behavior of between with compound indexes.

Answer 2

I think your query only supposed to work when the createdAt is also the primary key. Is it? Otherwise you can create an additional index on the createdAt field and use it in your between statement:

r.db('dev').table('tickets').indexCreate('createdAt', r.row('createdAt'))

r.db...
.between([rethinkdb.minval, timeFrom],[rethinkdb.maxval , timeTo], {index:"createdAt"})

you can also use multiple orderby as described by @Stock Overflaw, but it only works correctly if you put both conditions into one orderBy statement:

r.db('dev').table('tickets')
  .between(timeFrom, timeTo, {index: 'createdAt'})
  .orderBy(r.asc('createdAt'), r.asc('priority'))

keep in mind that this is less performant, because it doesn't use the indexes.