CODEWITHSUNDEEP
ruby-on-railsmongodbmongoid
JohnSmith1976
JohnSmith1976

Reputation: 666

Extract Mongoid documents based on the DateTime of their last has_many relations?

I have a bunch of orders, and some of them have order_confirmations.

1: I wish to extract a list of orders based on the DateTime of its last order_confirmation. This is my failed attempt (returns 0 records):

Order.where(:order_confirmations.exists => true).desc("order_confirmations.last.datetime")

2: I wish to extract a list of orders where the last order_confirmation is between 5 and 10 days old. This is my failed attempt (returns 0 results):

Order.lte("order_confirmations.last.datetime" => 5.days.ago).gte("order_confirmations.last.datetime" => 10.days.ago)

My relations:

class Order
  include Mongoid::Document
  has_many :order_confirmations
end

class OrderConfirmation
  include Mongoid::Document
  field :datetime, type: DateTime
  belongs_to :order
end

Upvotes: 0

Views: 340

Answers (2)

Gosha A
Gosha A

Reputation: 4570

With referenced relationships, you cannot directly query referenced documents.

That said, you would probably want to query order confirmations first, and then select the orders like this:

OrderConfirmation.between(datetime: 10.days.ago..5.days.ago)
  .distinct(:order_id).map { |id| Order.find(id) }

If you had confirmations embedded into the order, like this

class Order
  include Mongoid::Document
  embeds_many :order_confirmations
end

class OrderConfirmation
  include Mongoid::Document
  field :datetime, type: DateTime
  embedded_in :order
end

Then you could query order confirmation inside order query with $elemMatch:

Order.elem_match(order_confirmations:
  { :datetime.gte => 10.days.ago, :datetime.lte => 5.days.ago })

Regarding your first question, I don't think it's possible to do that with just MongoDB queries, so you could do something like

# if you go embedded rels
Order.all.map { |o| o.order_confirmations.desc(:datetime).first }
  .sort_by(&:datetime).map(&:order)

# if you stay on referenced rels
OrderConfirmation.desc(:datetime).group_by(&:order)
  .map { |k, v| v.first }.map(&:order)

Upvotes: 1

cpuguy83
cpuguy83

Reputation: 5993

Check out the elemMatch function.

where('$elemMatch' => [{...}]

I do believe there is a bug in mongoid though related to elemMatch and comparing dates, not sure if its been fixed.

Upvotes: 0

Related Questions