Mongoid: Ordering by nested child value

Question

class Box
  embeds_many :things
  after_init :add_default_things

  def add_default_things
    self.things.build(title: "Socks", value: 2)
    self.things.build(title: "Ring", value: 1)
  end
end

class Thing
  field :title
  field :value, type: Integer
end

all boxes have got some default things: Socks and a Ring. Everybody can add this or another things into a box. So now I need to order all boxes by count of socks:

Box.order_by(:thing_with_title_socks.value.desc) # ???

Answer 1

I am not good at ruby so I am going to try to explain it in java terms. The problem is how you design your data. For example you have n Box classes, each having a list of items in it:

public class Box {
  public List<Item> items;
}

public class Item {
  public String name;
  public int value;
}

to sort all boxes in this classes for a specific item value you need to loop through all items in boxes which is not good at all. so I would change my code to do it more efficiently:

public class Box {
  public Map<Integer, Item> items;
}

public class Item {
  public int id;
  public String name;
  public int value;
}

this way I would check if the item exists in box or not; access the items value with O(1) complexity, and I wouldn't mistakenly put 2 different packets of socks in my box which would cause an error (which one would it sort for?).

your current data structure is like this :

{
  things : [ {"name" : "socks", "value" : 2} , {"name" : "ring", "value" : 5} ]
}

but if you are going to do sorting / direct access (using in query) on "known" object (like ring or socks) then your data should look like:

{
  things : { "socks": 2, "ring" : 5 }
}

if you have extra information attached to this items they can also look like :

{
  things : { "S01" : { "name" : "super socks", "value" : 1 }, "S02" : { "name" : "different socks", "value" : 2} }
}

this way you have direct access to the items in box.

hope this would help.

Edit : I thought it was obvious but just to make thing clear: you can't efficiently query "listed" child data unless you know the exact position. (You can always use map/reduce though)

Answer 2

This is hard (or impossible) to do with a single query. I played around with this for a while. First off, let me post my entire test script:

require 'mongoid'

Mongoid.configure do |config|
  config.master = Mongo::Connection.new.db("test")
end

class Box
  include Mongoid::Document
  embeds_many :things
  field :name
end

class Thing
  include Mongoid::Document
  field :title
  field :value, type: Integer
end

b = Box.new
b.name = "non sock box"
b.things.push Thing.create!(title: "Ring", value:1)
b.save!

b1 = Box.new
b1.name = "sock box"
b1.things.push Thing.create!(title:"Socks", value:50)
b1.save!

b2 = Box.new
b2.name = "huge sock box"
b2.things.push Thing.create!(title:"Socks", value:1000)
b2.things.push Thing.create!(title:"Ring", value:1100)
b2.save!

b3 = Box.new
b3.name = "huge ring box"
b3.things.push Thing.create!(title:"Socks", value:100)
b3.things.push Thing.create!(title:"Ring", value:2600)
b3.save!

b4 = Box.new
b4.name = "ring first sock box"
b4.things.push Thing.create!(title: "Ring", value: 1200)
b4.things.push Thing.create!(title: "Socks", value: 5)
b4.save!

So you do this a few ways. First, you could find all the Thing objects that match your criteria and then do something with the Things. For example, get the Box ID.

Thing.desc(:value).where(:title => "Socks").to_a

This is pushing the problem to the app layer, which is typical with some mongo queries. But that's not what you asked for. So I submit this nasty query. This works only if you always have Sock things first in the things embedded document.

Box.all(conditions: { "things.title" => "Socks" }, 
  sort:[["things.0.value", :desc]]).to_a

Which is really annoying, because you'll notice that in my example data, I have a "huge ring box" which has socks but also has the largest value attribute so actually this gets returned first. If you re-sorted the things, this query would actually work. I don't know how to say sort by a things.value equaling "foo". I would bet you can't.

>> Box.all(conditions: { "things.title" => "Socks" }, 
sort:[["things.0.value", :desc]]).collect(&:name)
=> ["ring first sock box", "huge sock box", "huge ring box", "sock box"]

See, this is wrong. Huge sock box should be first. But it's not because of the things.0.value is picking up the largest 2600 Ring value in (b4) the sample data.

You can get a one liner to work but it's actually doing (at least) two queries:

Thing.desc(:value).where(:title => "Socks").collect(&:id).each {|thing| 
  puts Box.where("things._id" => thing).first.name }

But again, this is really doing the lifting in ruby versus having mongo do it. There are many join problems like this. You have to do two queries.

Another option may be to de-normalize your data and just store the things as an embedded array of attributes or just simply attributes. If chests can contain anything, you don't even need to define the attributes ahead of time. This is kind of the cool thing about MongoDB.

class Chest
  include Mongoid::Document
  field :name
end

c = Chest.create!(:name => "sock chest", :socks => 50, :ring => 1)
c1 = Chest.create!(:name => "non sock chest", :ring => 10)
c2 = Chest.create!(:name => "huge sock chest", :ring => 5, :socks => 100)
c3 = Chest.create!(:name => "huge ring chest", :ring => 100, :socks => 25)

Chest.where(:socks.exists => true).desc(:socks).collect(&:name)
=> ["huge sock chest", "sock chest", "huge ring chest"]