7

Rails 7 adds the ability to delete and update queries using GROUP_BY and HAVING

 3 years ago
source link: https://blog.saeloun.com/2021/12/15/rails-7-updates-to-group-by-and-having
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

For a very long time in Rails, it was not possible to combine delete statements with GROUP_BY and HAVING queries. It does seem like something that should have been supported out of the box, but unfortunately was overlooked — until now!

Before

Using GROUP_BY or HAVING in a delete statement was ignored. Let’s look at an example.

Here, a user has multiple votes, and we’re trying to count users who have less than three votes.

User.joins(:votes).group("users.id").having("count(votes.id) < 3").count
=> {2=>1, 1=>2}

If we do update_all with group and having clause ActiveRecord would ignore it.

User.joins(:votes).group("users.id").having("count(votes.id) < 3").update_all(consistent: true)

# actual

UPDATE "users" SET "consistent" = ? WHERE "users"."id" IN (
  SELECT "users"."id" FROM "users" INNER JOIN "votes" ON "votes"."user_id" = "users"."id"
)  [["consistent", "t"]]

# expected

UPDATE "users" SET "consistent" = ? WHERE "users"."id" IN (
  SELECT "users"."id" FROM "users" INNER JOIN "votes" ON "votes"."user_id" = "users"."id"
  GROUP BY users.id HAVING (count(votes.id) < 3)
)  [["consistent", "t"]]

However, when we try to delete these users, we end up with an error.

User.joins(:votes).group("users.id").having("count(votes.id) < 3").delete_all
=> Traceback (most recent call last):
        1: from (irb):10
ActiveRecord::ActiveRecordError (delete_all doesn't support group, having)

After

Fortunately, Rails 7 allows GROUP BY and HAVING clauses with update_all and uses nested queries when doing DELETE with GROUP BY and HAVING clauses.

Interestingly, MySQL does not support using DELETE or UPDATE statements in combination with GROUP_BY or HAVING. To circumvent this, the PR uses nested queries.

DELETE FROM "users" WHERE "users"."id" IN (
  SELECT "users"."id" FROM "users" INNER JOIN "votes" ON "votes"."user_id" = "users"."id" GROUP BY "users"."id" HAVING (count(votes.id) < 3))
)  [["consistent", "t"]]

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK