CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecordassociationsunique
Saqib Shahzad
Saqib Shahzad

Reputation: 1002

getting unique records on the basis of specific associated column - rails active record associations

I have three models. User, Question and Attempt.

Each User has_many :attempts,

similarly, Question has_many :attempts

and Attempt belongs_to User and Question

Required:

What I want is a method to return a user's last attempt for every question that user has an associated attempt.

I mean if a user has attempted q1, q2 and has 3 attempts for each question, then my method should return last attempt of q1 and last attempt of q2.

Right now: I have created a method in User model.

  def last_attempts
    return self.attempts.joins(:question).order("questions.id ASC ,attempts.updated_at DESC").select("DISTINCT ON (question_id) *")
  end

Error: This method returns duplicate attempts. (all attempts for every question of that user). all 3 attempts of q1 and all three attempts of q2

Upvotes: 0

Views: 593

Answers (2)

Scott Bartell
Scott Bartell

Reputation: 2840

It looks like you have an unnecessary join in your query.

You should be able to do the following:

def questions_last_attempts
  attempts
    .select("DISTINCT ON (question_id) *")
    .order(:question_id, created_at: :desc)
end

Which should generate the following SQL:

SELECT DISTINCT ON (question_id) * FROM "attempts" WHERE "attempts"."user_id" = 1 ORDER BY "attempts"."question_id" ASC, "attempts"."created_at" DESC

Upvotes: 2

Sovalina
Sovalina

Reputation: 5609

One way is to add a relationship between User and Question through Attempt.
Then map the user.questions ids to select each time the first ordered attempt:

class User < ApplicationRecord
  has_many :attempts
  has_many :questions, through: :attempts

  def last_attempts
    questions.order(:id).distinct.map do |question|
      attempts.where(question_id: question.id).order(updated_at: :desc).first
    end
  end
end

Upvotes: 1

Related Questions