Query based on calculated fields with Mongoid

Question

I want to implement a scope overdue in a model Invoice to return all invoices that exceeded the date, until they had to be paid. I have the fields invoice_date, :type => Date and days_for_payment, :type => Integer.

In my previous version, which was built on ActiveRecord, I could use the query

Invoice.where("invoice_date + days_for_payment < ?", Date.today)

This query made the calculation on the DB side.

Is there a way to get the same thing done with Mongoid? Or does anyone know a good workaround (proc, lambda, etc.)?

I use mongoid '2.4.12'

Answer 1

Found the answer myself. With the prefix this.* I can reference to the fields. And I can use JavaScript functions. MongoDB gets cooler and cooler!

So here is my solution:

class Invoice
  include Mongoid::Document
  field :invoice_date, :type => Date
  field :days_for_payment, :type => Integer
  ...

  scope :overdue, where("(Math.round(this.invoice_date.getTime() / 1000) + (this.days_for_payment * 24 * 3600)) < #{Time.now.to_i}")

  ...
end

Timestamp creation in js works different. So I had to get rid of the last three numbers and round them. If anybody knows a more elegant way, please let me know.

My only problem left is, that I can't store a Date object to MongoDB. It always tells me I have to use Time. I think I better upgrade mongoid to 3.0.1.

Answer 2

I am not sure about mongoid, if you are querying mongodb directly you can use the $where operator. It is not recommended as it doesn't use indexes. If you have another condition that filters the records to a small set then you can use the $where to further filter it.