convert mysql query into rails query

Question

Hi all I have a problem converting mysql query into rails query. I have these models -

class User < ApplicationRecord
   has_many :comments, foreign_key: "commenter_id"
end

class Comment < ApplicationRecord
  belongs_to :commenter, class_name: "User"
end

Can anyone help me out with converting following query into rails query-

UPDATE comments 
INNER JOIN users on comments.commenter_id = users.id 
SET comments.deleted_at = users.deleted_at 
WHERE users.deleted_at IS NOT NULL

I am trying to make soft-delete comments whose commenter was softly deleted.

UPDATE 1: so far I can able to do it by using this-

User.only_deleted.includes(:comments).find_each do |u|
  u.comments.update_all(deleted_at: u.deleted_at)
end

But I want to do this on single query without having to iterate over the result.

UPDATE 2: I am using acts_as_paranoid gem, so unscoping user is needed and my final query became:

User.unscoped{Comment.joins(:commenter).where.not(users: {deleted_at: nil}).update_all("comments.deleted_at = users.deleted_at")

It looks like you are trying so soft delete comments when a user is soft deleted. Then I recommend in the action that soft-delete a user, to also soft delete the comments. @user.comments.update_all(deleted_at: @user.deleted_at) or something like that. — Maxence
– Maxence, Commented Sep 24, 2019 at 9:35
@Maxence I am trying to update all the comments from all users in one single query. — Bisho Silwal
– Bisho Silwal, Commented Sep 24, 2019 at 9:57

max · Accepted Answer · 2019-09-24 12:32:15Z

2

This should work on MySQL:

Comment
    .joins(:user)
    .where.not(users: { deleted_at: nil })
    .update_all("comments.deleted_at = users.deleted_at")

This won't work on Postgres since its missing a FROM clause for users.

A less performant but polyglot option is:

Comment
  .joins(:user)
  .where.not(users: { deleted_at: nil })
  .update_all("deleted_at = ( SELECT users.deleted_at FROM users WHERE comments.id = users.id )")

This is still probably an order of magnitude better than iterating through the records in Ruby since you eliminate the traffic delay between your app server and the db.

edited Sep 24, 2019 at 12:32

answered Sep 24, 2019 at 11:59

max

103k15 gold badges113 silver badges177 bronze badges

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

Comments

Mark · Accepted Answer · 2019-09-24 10:41:02Z

0

From your comments, I think this is what you want:

Comment.where.not(user_id: nil).each { |comment| comment.update_attributes(deleted_at: comment.user.deleted_at)

Or slightly more readable:

Comment.all.each do |comment|
  next unless comment.user.present?
  comment.update_attributes(deleted_at: comment.user.deleted_at)
end

edited Sep 24, 2019 at 10:41

answered Sep 24, 2019 at 10:22

Mark

6,5415 gold badges24 silver badges52 bronze badges

1 Comment

Bisho Silwal Over a year ago

I don't want to iterate on result since in large data-set it might caught an issue. So one single query without iteration is what I am concern about.

Jignesh Gohel · Accepted Answer · 2019-09-24 13:39:10Z

0

The code below should execute number of queries corresponding to deleted_users and without loading User and any associated Comments in memory

deleted_users_data_arr = User.only_deleted.pluck(:id, :deleted_at)

deleted_users_data_arr.each do |arr|
  deleted_user_id = arr[0]
  user_deleted_at = arr[1]

  Comment.where(commenter_id: deleted_user_id).update_all(deleted_at: user_deleted_at)
end

answered Sep 24, 2019 at 13:39

Jignesh Gohel

6,5806 gold badges61 silver badges92 bronze badges

1 Comment

max Over a year ago

You should at least use .find_each which will load the records in batches instead of exhausting the available memory and causing a crash.

Collectives™ on Stack Overflow

convert mysql query into rails query

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related