-3

User has many comments and comments belong to user. Comment table has status column which is boolean type.

Now I want to get users whose last comment has status true.

How can this be done in ActiveRecord query?

Roshan
  • 905
  • 9
  • 21
Anand Shrivastava
  • 85
  • 9

3 Answers3

1

You could try this query (get users those last comment has status true)

User.joins(:comments)
    .where('comments.created_at = (SELECT MAX(comments.created_at) FROM comments WHERE comments.user_id = users.id)')
    .where('comments.status = true')
    .group('users.id')

This actually requires a sub query, so the idea is first fetching out all users last comment and filtering out with status as true to find out the users id with help of join.

Ashik Salman
  • 1,819
  • 11
  • 15
0
@user_ids = Comment.where(status: true).pluck(:user_id)

Then

User.where(id: @user_ids)

Otherwise use a join, will give you all the fields from User and Comment for every result.

Comment.joins(:user).where(user_id: @user_ids)
Ashik Salman
  • 1,819
  • 11
  • 15
Fabrizio Bertoglio
  • 5,890
  • 4
  • 16
  • 57
  • Not the right answer because I did not mention Question model any where. also I want to get the users those last comment has status false. – Anand Shrivastava Sep 07 '17 at 08:48
0
users = []
User.all.each do |user|
  users << user if user.comments && user.comments.last.status
end

users # list of all user whose last comment status = true

users object consist all the users whose last comment status is true.

Sachin R
  • 11,606
  • 10
  • 35
  • 40
  • I want a rails active record query no loop. – Anand Shrivastava Sep 07 '17 at 08:48
  • 1
    @AnandShrivastava based on rails guide this is the correct answer (if it works). This is a Query for retrieving multiple objects in Batches Also, please explain us why you need an active record query and no loop http://guides.rubyonrails.org/active_record_querying.html#retrieving-multiple-objects-in-batches – Fabrizio Bertoglio Sep 07 '17 at 09:01
  • Because it is obvious that loop will take more time in compare to active record query and I consider it to be not good practice suppose if so many users like 10000 etc then load time will be more. does it make sense? – Anand Shrivastava Sep 07 '17 at 10:40