Querying embedded objects in Mongoid/rails 3 ("Lower than", Min operators and sorting)

Question

I am using rails 3 with mongoid. I have a collection of Stocks with an embedded collection of Prices :

class Stock
  include Mongoid::Document
  field :name, :type => String
  field :code, :type => Integer
  embeds_many :prices

class Price
  include Mongoid::Document
  field :date, :type => DateTime
  field :value, :type => Float
  embedded_in :stock, :inverse_of => :prices

I would like to get the stocks whose the minimum price since a given date is lower than a given price p, and then be able to sort the prices for each stock.

But it looks like Mongodb does not allow to do it. Because this will not work:

@stocks = Stock.Where(:prices.value.lt => p)

Also, it seems that mongoDB can not sort embedded objects.

So, is there an alternative in order to accomplish this task ?

Maybe i should put everything in one collection so that i could easily run the following query:

@stocks = Stock.Where(:prices.lt => p)

But i really want to get results grouped by stock names after my query (distinct stocks with an array of ordered prices for example). I have heard about map/reduce with the group function but i am not sure how to use it correctly with Mongoid.

http://www.mongodb.org/display/DOCS/Aggregation

The equivalent in SQL would be something like this:

SELECT name, code, min(price) from Stock WHERE price<p GROUP BY name, code

Thanks for your help.

Answer 1

MongoDB / Mongoid do allow you to do this. Your example will work, the syntax is just incorrect.

@stocks = Stock.Where(:prices.value.lt => p) #does not work

@stocks = Stock.where('prices.value' => {'$lt' => p}) #this should work

And, it's still chainable so you can order by name as well:

@stocks = Stock.where('prices.value' => {'$lt' => p}).asc(:name)

Hope this helps.