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

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"]]