ActiveRecord queries: Accessing outer field in subquery

Question

I'm confused at how to do an active record join. I have three models, User, Task, and Response. A user submits responses to tasks. I'm using the has_many, through: pattern in ActiveRecord.

class Task < ApplicationRecord
  has_many :users, through: :responses
  has_many :responses
  validates :response_limit, presence: true

class User < ApplicationRecord
  has_many :responses
  has_many :tasks, through: :responses

class Response < ApplicationRecord
  belongs_to :task, optional: false
  belongs_to :user, optional: false

In plain English, I'd like to create a query that given a query, returns all tasks that:

Has a Response count less than the :response_limit field in Task
The User has not responded to already.

I'm able to satisfy #2 with this nested query, which I got from an earlier question.

Task.where.not(id: Response.select(:task_id).where(user_id: params[:user_id]))

However, I'm having difficulty satisfying the first. I took at stab at it and got this:

Task.where.not(id: Response.select(:task_id).where(user_id: params[:user_id])).where('response_limit > (?)', Response.where(task_id:task.id).count)

My issue is that I'm not sure how to get the task_id from the outer query into the Response subquery.

Vane Trajkov · Accepted Answer · 2017-12-06 20:18:10Z

1

For this, you can try utilizing SQL GROUP BY and HAVING clauses that will help you count the responses and filter them based on a condition

Task.select("tasks.*, COUNT(responses.id) AS response_count")
    .joins(:responses)
    .group("tasks.id")
    .where.not(id: Response.select(:task_id).where(user_id: params[:user_id]))
    .having("COUNT(responses.id) < tasks.response_limit")

answered Dec 6, 2017 at 20:18

Vane Trajkov

7684 silver badges11 bronze badges

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

1 Comment

nao Over a year ago

Almost perfect. The only thing I had to change was replacing joins with left_outer_joins and adding .distinct, because with joins it will not return tasks that have no responses. Other than that, awesome answer! :)

Pranav Singhal · Accepted Answer · 2017-12-06 22:56:22Z

1

you can the number of responses to a task by

    Task.response.count

then simply compare it with the task's response_limit

answered Dec 6, 2017 at 22:56

Pranav Singhal

16811 bronze badges

Collectives™ on Stack Overflow

ActiveRecord queries: Accessing outer field in subquery

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related