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