Rails/mongoid: Advanced querying of arrays

Question

Im stuck with an advanced query in rails. I need a solution that works in mongoid and if possible also active record (probably not possible). I've put together a simplified example below.

Consider the following model:

class Announcement
  include Mongoid::Document

  field :title, type: String
  field :user_group, type: Array
  field :year, type: Array
  field :tags, type: Array

  has_and_belongs_to_many :subjects

  before_save :generate_tags

  private
  def generate_tags
    tags = []
    if self.subjects.present?
      self.subjects.each { |x| tags << x.name.downcase.gsub(" ", "_") }
    end

    if self.year.present?
      self.year.each { |x| tags << "year_" + x.to_s }
    end

    self.tags = tags
  end
end

Given the tags array of document 1:

["hsc_mathematics", "hsc_chemistry", "year_9"]

And document 2:

["hsc_mathematics", "hsc_chemistry"]

And document 3:

["hsc_mathematics", "hsc_chemistry", "year_9", "year_10"]

And document 4:

["year_9", "year_10"]

Now consider the following model:

class Student < User
  include Mongoid::Document

  field :year, type: Integer
  has_many :subjects

  def announcements
      tags = []
      if self.subjects.present?
        self.subjects.each { |x| subjects << x.name.downcase.gsub(" ", "_") }
      end

      tags << "year_" + self.year.to_s

      Announcement.where("user_group" => { "$in" => ["Student", "all_groups"]}).any_of({"tags" => { "$in" => tags }}, {tags: []})
  end
end

For the purpose of our example our student has the following tags:

[ "hsc_mathematics", "hsc_physics", "year_10" ]

My query is incorrect as I want to return documents 2, 3 and 4 but not document 1.

I need the query to adhere to the following when returning announcements:

i. If the announcement has subject tags match on any subject

ii. If the announcement has year tags match on any year

iii. If announcement has year and subject tags match on any year and any subject

How would I go about writing this?

EDIT

Im happy to split year out of my tags but im still stuck

Announcement.where("user_group" => { "$in" => ["Student", "all_groups"]}).any_of({"tags" => { "$in" => ["hsc_mathematics", "hsc_physics"] }}, {tags: []}).any_of({"year_at_school" => { "$in" => 10 }}, {year_at_school: []})

You're not going to get one solution that will work with both AR and Mongoid, they're have entirely different interfaces for all but the most trivial of queries. Why are you mashing all your tags into one pile when they have internal structure? Why don't you organize the data better so that you can query it in some sort of sane way? — mu is too short
– mu is too short, Commented Sep 1, 2014 at 5:43
See my edit, I've split out my year tags. The first 2/3 of the query works just not year filtering. — Allan W Smith
– Allan W Smith, Commented Sep 1, 2014 at 6:27

Allan W Smith · Accepted Answer · 2014-09-02 02:47:08Z

2

So the solution was to adjust my models and use a more organised query rather then an entire tag bank.

Announcement model:

class Announcement
  include Mongoid::Document

  field :title, type: String
  field :user_group, type: Array, default: [""]
  field :year, type: Array, default: [""]
  field :tags, type: Array, default: [""]

  has_and_belongs_to_many :subjects

  before_save :generate_tags

  private
  def generate_tags
    tags = []
    if self.subjects.present?
      self.subjects.each { |x| tags << x.name.downcase.gsub(" ", "_") }
    end

    self.tags = tags
  end
end

User model:

class Student < User
  include Mongoid::Document

  field :year, type: Integer
  has_many :subjects

  def announcements
     year = "year_" + self.year.to_s
      tags = [""]
      if self.subjects.present?
        self.subjects.each { |x| tags << x.name.downcase.gsub(" ", "_") }
      end

      Announcement.where("user_group" => { "$in" => ["Student", ""] }).and("year" => { "$in" => [year, ""]}).any_in(tags: tags).all.entries
  end
end

EDIT: Heres a neater version of the query as suggested

This example also has an expiry field which assumes nil = never expires

Announcement.where(:user_group.in => ["Student", ""], :year.in => [year, ""], :tags.in => tags).any_of({:expires_at.gte => Time.zone.now}, {:expires_at => nil}).all.entries

edited Sep 2, 2014 at 2:47

answered Sep 2, 2014 at 0:58

Allan W Smith

7566 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mu is too short Over a year ago

You should be able to say things like where(:user_group.in => [...]) with Mongoid, I find that less noisy that the usual mess of hashes that MongoDB wants to work with.

Allan W Smith Over a year ago

Awesome I'll have a play with that. Cheers

Collectives™ on Stack Overflow

Rails/mongoid: Advanced querying of arrays

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related