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.

Solution 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.

Solution 2

I've had a similar problem... here's what I suggest:

scope :price_min, lambda { |price_min| price_min.nil? ? {} : where("price.value" => { '$lte' => price_min.to_f }) }

Place this scope in the parent model. This will enable you to make queries like:

Stock.price_min(1000).count

Note that my scope only works when you actually insert some data there. This is very handy if you're building complex queries with Mongoid.

Good luck!

Very best, Ruy

Solution 3

MongoDB does allow querying of embedded documents, http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-ValueinanEmbeddedObject

What you're missing is a scope on the Price model, something like this:

 scope :greater_than, lambda {|value| { :where => {:value.gt => value} } }

This will let you pass in any value you want and return a Mongoid collection of prices with the value greater than what you passed in. It'll be an unsorted collection, so you'll have to sort it in Ruby.

 prices.sort {|a,b| a.value <=> b.value}.each {|price| puts price.value}

Mongoid does have a map_reduce method to which you pass two string variables containing the Javascript functions to execute map/reduce, and this would probably be the best way of doing what you need, but the code above will work for now.

Comments

[mathieurip]

Thanks for your answer . But i am not really sure how to use this scope functionality. I put the scope function in my Price model , and querying @Stocks = Stock.where(:prices.greater_than => 200) , it gives me an error : "undefined method `greater_than' for :prices:Symbol"

[Srdjan Pejic]

The scope should be invoked on a single Stock, not the collection. So, your code won't work, indeed. Because you're looking to filter like that, you're best off with a MapReduce call. I'll see if I can post an example somewhere.

[mathieurip]

Yes, this is what i just figured out. Nevertheless, i found a silly and slow solution that works to get a list of stocks whose prices are between 100 and 200 for example, but i am not happy with it : i = 100 while i<200 results = Stock.where("prices.value" => i) if(@stocks == nil) @stocks = results else @stocks += results end i += 1 end

[Peter Ehrlich]

Note that more complex queries can still be done, like this: all_of({:'books.name' => 'name'}, {:'books.author' => /joe/i})

[xentek]

@mathieurip the greater_than scope is just a class method on Stock, so call it like this: Stock.greather_than.

[Jenuel Ganawed]

using the string as the name of the parameter works fine with me... thanks for the info, this is really helpful