Between with "min/max val"

[scottrobertson]

Hey

We have an index of account_created which is on the fields account_id and created_at. We need to be able to return all of those results ordered by created.

What I am trying to do, is replicate this part of the query with NoBrainer:

r.table("charges").between([44, r.minval], [44, r.maxval], {"index" => :account_created}).order_by({"index" => r.desc(:account_created)})

Normally i would just write this in straight reql, but we need to do quite a lot of eager_loading etc, and NoBrainer keeps that part much cleaner.

Do you have any idea on how to use between in NoBrainer? I have tried the following, but between is not a method on the symbol:

Charge.where(:account_created.between => [[44, RethinkDB::RQL.new.minval], [44, RethinkDB::RQL.new.maxval]])

Thanks

[jeroenvisser101]

There's Charge.where(:account_created.gte => 44, :account_created.lte => 44) which might match your use case?

[jeroenvisser101]

Or you could do where(lambda { |doc| rql_expression(doc) }) and still use eager_loading and all?

[scottrobertson]

@jeroenvisser101 sadly not as that tries to match the entire index against that range.

However, this could work. Thank you! Will give it a go now. If not, i will give the rql_expression a go. Didn't realise we had access to be able to do that.

Charge.where(:account_created.gte => [44, RethinkDB::RQL.new.minval], :account_created.lte => [44, RethinkDB::RQL.new.maxval]).

[jeroenvisser101]

Ah, to bad, let me know what you end up using 👍

[scottrobertson]

@jeroenvisser101 💥

Charge.where(:account_created.gte => [44, RethinkDB::RQL.new.minval], :account_created.lte => [44, RethinkDB::RQL.new.maxval]).order_by(account_created: :desc)

Thanks for the help! Sometime the best solutions are too simple to see ha..

[jeroenvisser101]

Looks great, just as a sidenote, I find gte more understandable, but it's actually an alias to ge. You're welcome!

[nviennot]

I think the where() query optimizer should recognize this case and should figure out to use the account_created index by using the minval..maxval range for the created_at values. You should be able to only specify where(:account_id => 44).

[scottrobertson]

@nviennot while that is normally true, in this case we have an index on account_id too, so it use that instead. We need to use the account_created one to optimise ordering.

But very interesting to know that it would do that 👍 thank you

[nviennot]

NoBrainer should be able to figure that out by itself when looking at the order() clause. Further we can specify which index we want by chaining with_index(:account_created) in the query to force NoBrainer to use it.