MongoDB conditional aggregate query on a HABTM relationship (Mongoid, RoR)?

Question

Rails 4.2.5, Mongoid 5.1.0

I have three models - Mailbox, Communication, and Message.

mailbox.rb

class Mailbox
    include Mongoid::Document
    belongs_to :user
    has_many :communications
end

communication.rb

class Communication
    include Mongoid::Document
    include Mongoid::Timestamps
    include AASM

    belongs_to :mailbox
    has_and_belongs_to_many :messages, autosave: true

    field :read_at,     type: DateTime
    field :box,         type: String
    field :touched_at,  type: DateTime
    field :import_thread_id, type: Integer
    scope :inbox, -> { where(:box => 'inbox') }
end

message.rb

class Message
    include Mongoid::Document
    include Mongoid::Timestamps

    attr_accessor :communication_id

    has_and_belongs_to_many :communications, autosave: true
    belongs_to :from_user, class_name: 'User'
    belongs_to :to_user, class_name: 'User'

    field :subject, type: String
    field :body,    type: String
    field :sent_at, type: DateTime
end

I'm using the authentication gem devise, which gives access to the current_user helper, which points at the current user logged in.

I have built a query for a controller that satisfied the following conditions: Get the current_user's mailbox, whose communication's are filtered by the box field, where box == 'inbox'. It was constructed like this (and is working):

current_user.mailbox.communications.where(:box => 'inbox')

My issue arrises when I try to build upon this query. I wish to chain queries so that I only obtain messages whose last message is not from the current_user. I am aware of the .last method, which returns the most recent record. I have come up with the following query but cannot understand what would need to be adjusted in order to make it work:

current_user.mailbox.communications.where(:box => 'inbox').where(:messages.last.from_user => {'$ne' => current_user})

This query produces the following result: undefined method 'from_user' for #<Origin::Key:0x007fd2295ff6d8>

I am currently able to accomplish this by doing the following, which I know is very inefficient and want to change immediately:

mb = current_user.mailbox.communications.inbox

comms = mb.reject {|c| c.messages.last.from_user == current_user}

I wish to move this logic from ruby to the actual database query. Thank you in advance to anyone who assists me with this, and please let me know if anymore information is helpful here.

Answer 1

Ok, so what's happening here is kind of messy, and has to do with how smart Mongoid is actually able to be when doing associations.

Specifically how queries are constructed when 'crossing' between two associations.

In the case of your first query:

current_user.mailbox.communications.where(:box => 'inbox')

That's cool with mongoid, because that actually just desugars into really 2 db calls:

1. Get the current mailbox for the user
2. Mongoid builds a criteria directly against the communication collection, with a where statement saying: use the mailbox id from item 1, and filter to box = inbox.

Now when we get to your next query,

current_user.mailbox.communications.where(:box => 'inbox').where(:messages.last.from_user => {'$ne' => current_user})

Is when Mongoid starts to be confused.

Here's the main issue: When you use 'where' you are querying the collection you are on. You won't cross associations.

What the where(:messages.last.from_user => {'$ne' => current_user}) is actually doing is not checking the messages association. What Mongoid is actually doing is searching the communication document for a property that would have a JSON path similar to: communication['messages']['last']['from_user'].

Now that you know why, you can get at what you want, but it's going to require a little more sweat than the equivalent ActiveRecord work.

Here's more of the way you can get at what you want:

user_id = current_user.id
communication_ids = current_user.mailbox.communications.where(:box => 'inbox').pluck(:_id)
# We're going to need to work around the fact there is no 'group by' in
# Mongoid, so there's really no way to get the 'last' entry in a set
messages_for_communications = Messages.where(:communications_ids => {"$in" => communications_ids}).pluck(
  [:_id, :communications_ids, :from_user_id, :sent_at]
)
# Now that we've got a hash, we need to expand it per-communication,
# And we will throw out communications that don't involve the user
messages_with_communication_ids = messages_for_communications.flat_map do |mesg|
  message_set = []
  mesg["communications_ids"].each do |c_id|
    if communication_ids.include?(c_id)
      message_set << ({:id => mesg["_id"],
       :communication_id => c_id,
       :from_user => mesg["from_user_id"],
       :sent_at => mesg["sent_at"]})
    end
  message_set
end
# Group by communication_id
grouped_messages = messages_with_communication_ids.group_by { |msg| mesg[:communication_id] }
communications_and_message_ids = {}
grouped_messages.each_pair do |k,v|
  sorted_messages = v.sort_by { |msg| msg[:sent_at] }
  if sorted_messages.last[:from_user] != user_id
    communications_and_message_ids[k] = sorted_messages.last[:id]
  end
end
# This is now a hash of {:communication_id => :last_message_id}
communications_and_message_ids

I'm not sure my code is 100% (you probably need to check the field names in the documents to make sure I'm searching through the right ones), but I think you get the general pattern.