NoBrainerORM / nobrainer

Ruby ORM for RethinkDB
http://nobrainer.io/
Other
387 stars 49 forks source link

Support for range query using compound index? #272

Closed CodeMonkeySteve closed 2 years ago

CodeMonkeySteve commented 3 years ago

I have a user_items table with a compound index on [:user_id, :item_id]. I'm trying to do a range query, but I can't get NoBrainer to use the index:

UserItem.where(:user_id => '9O6ZCaYTjj8Mgt', :item_id.gt => '9UX88eKUTrlbRH').to_rql

r.table("user_items").order_by({"index" => r.asc(:id)}).filter {|var_2|
  var_2[:user_id].eq("9O6ZCaYTjj8Mgt").and(
    var_2[:item_id].gt("9UX88eKUTrlbRH")
  )
}

... when what I want is ...

r.table("user_items").between(
  [ "9O6ZCaYTjj8Mgt", "9UX88eKUTrlbRH" ],
  [ "9O6ZCaYTjj8Mgt", r.maxval ], 
  { "leftBound" => "open", "index" => "user_id_item_id" }
).order_by({ "index" => r.asc(:id) })

It seems like https://github.com/nviennot/nobrainer/blob/master/lib/no_brainer/criteria/where.rb#L393 prevents compound indexes from being used unless the query has equality arguments for all fields in the index. I can see why you might want to avoid supporting partial indexes in general, as the logic to select the best of several different indexes could get insanely complex, but it doesn't seem like much of a stretch to use compound indexes when one of the fields uses an inequality.

In any event, can you suggest a workaround to support this case? Thanks ...