Search for a record that has a relationship to objects with specific value using Mongoid

Question

Consider the following model diagram representing two models of my Rails project.

The Implement model represents an "object" that a person has, for example a person can have a helmet, bag, shield, etc. As it can be seen in the image, each implement has a level, this means that a user can have a helmet level 1, bag level 2, shield level 5, etc. Also it's important to say that a person can't have two implements of the same type, so a person record can't have two helmets.

The problem I am currently trying to solve is to query my MongoDB database using Mongoid in such form that I can find a person that has an implement level greater or equal to a given one. For example, I want all Person records that have a shield level greater or equal to 2 and a helmet level greater or equal to 5.

Currently my solution involves a brute force approach of looping all Person records and looking if they have a relationship to the implement I want, the problem with this is that it's inefficient and does not scale when making this operations with thousands of records in the database.

required_bag_level = 2
required_helmet_level = 1
Person.select do |record|
  record.implements.where(name: 'bag').first.level >= required_bag_level && record.implements.where(name: 'helmet').first.level >= required_helmet_level
end

I have also tried adding a general level to the Person object consisting of the sum of every implement level he has, so for example if the person has a bag level 2 and a helmet level 1, I give him a general level of 3, then when making the query I would look for all records with general level equal to 3.

required_bag_level = 2
required_helmet_level = 1
total = required_bag_level + required_helmet_level
Person.where(general_level: total)

The problem with this last approach is that it does not work with multiple implements, take for example a person with bag level 2 and another person with bag level 1 and helmet level 1, both would have a general level 2. When looking for a person with bag level 2 I would search for all records with general level 2 giving me both records. This would be wrong because even though the last record has a general level 2, he only has a bag level 1.

I wanted to ask if someone can point me in the right direction and help me figure out a scalable solution. Thanks.

score 0 · Answer 1 · answered Aug 13 '19 at 00:17

When working with only one association, one way to attain the requested behavior is to query the "many" association and then go to the "one" association. For example, given the following setup:

class Band
  include Mongoid::Document
  field :name, type: String
  has_many :members
end

class Member
  include Mongoid::Document
  field :name, type: String
  field :age, type: Integer
  belongs_to :band
end

foo = Band.create!(name: 'foo')
Member.create!(band: foo, name: 'Foo Singer', age: 20)
Member.create!(band: foo, name: 'Foo Drummer', age: 25)
bar = Band.create!(name: 'bar')
Member.create!(band: bar, name: 'Bar Drummer', age: 23)
baz = Band.create!(name: 'baz')
Member.create!(band: bar, name: 'Baz Soloist', age: 23)

The easy solution is to do:

Band.find(
  Member.all.or(
    {age: {'$gte' => 25}},
    {name: /Drummer/},
  ).pluck(:band_id)
)

This is essentially equivalent to how ActiveRecord performs joins with eager loading. There is no unneeded data transfered over the wire, and a total of two queries are issued.

Note that until https://jira.mongodb.org/browse/MONGOID-4697 is resolved, AND/OR queries need to be carefully constructed in order for them to return correct results.

A more general purpose, and complex, approach is to use MongoDB aggregation pipeline (https://docs.mongodb.com/manual/core/aggregation-pipeline/). There is support for it in the Ruby driver (https://docs.mongodb.com/ruby-driver/master/tutorials/ruby-driver-aggregation/) but there is no handy sugar for it in Mongoid, thus it is a lower level solution.

band_ids = Band.collection.aggregate([
  {'$lookup' => {
    from: 'members',
    localField: '_id',
    foreignField: 'band_id',
    as: 'members',
  }},
  {'$match' => {
    '$or' => [
      {'members.age' => {'$gte' => 25}},
      {'members.name' => {'$regex' => /Drummer/}},
    ],
  }},
  {'$project' => {_id: 1}},
]).to_a
Band.find(band_ids)

Aggregation pipeline permits using data from multiple collections in the same query, via multiple $lookup stages.

One gotcha of the aggregation pipeline/$lookup is, if localField and foreignField are not correctly specified, the server appears to return a cross join rather than an empty result set as I expected.

Search for a record that has a relationship to objects with specific value using Mongoid

1 Answers1