ruby - Rails sort from model association

Question

I have the following associations:

class Question < ApplicationRecord
  belongs_to :shopper
  has_many :question_messages, dependent: :destroy
end


class QuestionMessage < ApplicationRecord
  belongs_to :question
  belongs_to :shopper, optional: true
  belongs_to :store, optional: true
end

As you can see from the associations above I have two different user types(store, shopper) that can create question messages. I have an index page for the shopper user where I want to sort his questions according to the last question message he created. I'm trying this with the following code:

@questions = Question.where(shopper_id: current_shopper)
                     .group(:id).joins(:question_messages)
                     .order('max(question_messages.created_at) asc') 

The issue with this code is that it sorts the questions according to last questions messages created and not specifically the last question message created by the shopper. Any ideas on how to implement this so I can sort the questions according to the last question message that was created by the shopper?

Answer 1

Well if you want them to be sorted only by shopper's question messages then you need to apply the max only on question messages from that shopper:

Question.where(shopper_id: current_shopper)
        .group(:id)
        .joins(:question_messages)
        .where(question_messages: {shopper_id: current_shopper })
        .order('max(question_messages.created_at) asc')

The solution above will not return questions if the shopper doesn't have a QuestionMessage for it.

IF you need all questions regardless if the shopper has a question message pointing to the question, than you can use this:

Question.where(shopper_id: current_shopper)
        .group(:id)
        .joins("LEFT JOIN question_messages 
                ON question_messages.question_id = questions.id 
                AND question_messages.shopper_id = #{current_shopper.to_i}")
        .order('max(question_messages.created_at) asc')