How to use index with order_by? #82

Closed
Soliah opened this Issue Aug 18, 2014 · 5 comments

Comments

Projects
None yet
2 participants
@Soliah

Soliah commented Aug 18, 2014

I have an index defined as below:

class BomSourceRecord
  include NoBrainer::Document

  field :data
  field :digest
  field :source_id, index: true

  index :aifstime_utc, -> document { document["data"]["aifstime_utc"] }
end

How I can tell nobrainer to use the aifstime_utc index in an order query?

BomSourceRecord.order_by(:aifstime_utc).with_index("aifstime_utc").where(source_id: 71).without_index.limit(10).to_a

doesn't seem to work. The above gives the following ReQL:

r.table("bom_source_records").filter {|var_3|
  var_3.get_field(:source_id).eq(71)
}.orderby(r(:aifstime_utc).asc).limit(10)

Essentially I'm trying to get the following ReQL:

r.table("bom_source_records").order_by(index: "aifstime_utc").filter { |doc|
  doc.get_field(:source_id).eq(71)
}.limit(10)
@nviennot

This comment has been minimized.

Show comment
Hide comment
@nviennot

nviennot Aug 18, 2014

Owner

This is what I'm getting with the class definition you gave:

BomSourceRecord.where(:source_id => 123).order_by(:aifstime_utc).first
=> r.table("bom_source_records").get_all(123, {"index" => :source_id}).orderby( r(:aifstime_utc).asc).limit(1)

So the source_id index is being used. Next on that, order_by cannot use an index, because an indexed order_by can only be performed on a r.table() construct. In other words, you cannot use both indexes at the same time.

For example, without the where clause, the index can be used for ordering:

BomSourceRecord.order_by(:aifstime_utc).first
=> r.table("bom_source_records").orderby({"index" => r(:aifstime_utc).asc}).limit( 1)

Avoid using with_index or without_index in general, unless you know what you are doing.

Here's a solution to your problem:

class BomSourceRecord
  # add this line
  scope :data_order_by, ->(field) { order_by { |doc| doc['data'][field] } }
end

BomSourceRecord.where(:source_id => 123).data_order_by(:aifstime_utc).first
=> r.table("bom_source_records").get_all(123, {"index" => :source_id}).orderby( r(lambda {|var_1| var_1.get_field("data").get_field(:aifstime_utc)}).asc).limit(1)

Adding a custom filter data_order_by (it's just a class method) allows you to access the data sub document efficiently.

Owner

nviennot commented Aug 18, 2014

This is what I'm getting with the class definition you gave:

BomSourceRecord.where(:source_id => 123).order_by(:aifstime_utc).first
=> r.table("bom_source_records").get_all(123, {"index" => :source_id}).orderby( r(:aifstime_utc).asc).limit(1)

So the source_id index is being used. Next on that, order_by cannot use an index, because an indexed order_by can only be performed on a r.table() construct. In other words, you cannot use both indexes at the same time.

For example, without the where clause, the index can be used for ordering:

BomSourceRecord.order_by(:aifstime_utc).first
=> r.table("bom_source_records").orderby({"index" => r(:aifstime_utc).asc}).limit( 1)

Avoid using with_index or without_index in general, unless you know what you are doing.

Here's a solution to your problem:

class BomSourceRecord
  # add this line
  scope :data_order_by, ->(field) { order_by { |doc| doc['data'][field] } }
end

BomSourceRecord.where(:source_id => 123).data_order_by(:aifstime_utc).first
=> r.table("bom_source_records").get_all(123, {"index" => :source_id}).orderby( r(lambda {|var_1| var_1.get_field("data").get_field(:aifstime_utc)}).asc).limit(1)

Adding a custom filter data_order_by (it's just a class method) allows you to access the data sub document efficiently.

@nviennot

This comment has been minimized.

Show comment
Hide comment
@nviennot

nviennot Aug 18, 2014

Owner

Sorry, I think I read too quickly what you were trying to achieve.
There is no way to force to use the order_by index, let me add some code.

Owner

nviennot commented Aug 18, 2014

Sorry, I think I read too quickly what you were trying to achieve.
There is no way to force to use the order_by index, let me add some code.

@Soliah

This comment has been minimized.

Show comment
Hide comment
@Soliah

Soliah Aug 18, 2014

@nviennot Yea I was a bit confused reading you reply 😕

Essentially, I want to avoid using the source_id index and force the use of the aifstime_utc index for ordering, whilst still being able to use a where call.

Soliah commented Aug 18, 2014

@nviennot Yea I was a bit confused reading you reply 😕

Essentially, I want to avoid using the source_id index and force the use of the aifstime_utc index for ordering, whilst still being able to use a where call.

@nviennot nviennot closed this in 2abc156 Aug 18, 2014

@nviennot

This comment has been minimized.

Show comment
Hide comment
@nviennot

nviennot Aug 18, 2014

Owner

I've pushed some code to do this. Example:

BomSourceRecord.where(:source_id => 123).order_by(:aifstime_utc).first
=> r.table("bom_source_records").get_all(123, {"index" => :source_id}).orderby( r(:aifstime_utc).asc).limit(1)

BomSourceRecord.where(:source_id => 123).order_by(:aifstime_utc).with_index(:aifstime_utc).first
=> r.table("bom_source_records").orderby({"index" => r(:aifstime_utc).asc}).filter {|var_1| var_1.get_field(:source_id).eq(123)}.limit(1)

FYI, The with_index or without_index clause position in the chain does not matter. Everything happens as if with_index was always added at the end of your query.

Owner

nviennot commented Aug 18, 2014

I've pushed some code to do this. Example:

BomSourceRecord.where(:source_id => 123).order_by(:aifstime_utc).first
=> r.table("bom_source_records").get_all(123, {"index" => :source_id}).orderby( r(:aifstime_utc).asc).limit(1)

BomSourceRecord.where(:source_id => 123).order_by(:aifstime_utc).with_index(:aifstime_utc).first
=> r.table("bom_source_records").orderby({"index" => r(:aifstime_utc).asc}).filter {|var_1| var_1.get_field(:source_id).eq(123)}.limit(1)

FYI, The with_index or without_index clause position in the chain does not matter. Everything happens as if with_index was always added at the end of your query.

@Soliah

This comment has been minimized.

Show comment
Hide comment
@Soliah

Soliah Aug 18, 2014

Working! Thanks a lot ❤️

Soliah commented Aug 18, 2014

Working! Thanks a lot ❤️

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment