Rails delete record from joins table: delete_all fails when using additional joins

Happens both at Rails V 5.2.8.1 and Rails 7.1.3.2

My models:

class User < ApplicationRecord
  has_and_belongs_to_many :comments
end

class Comment < ApplicationRecord
  belongs_to :book

  has_and_belongs_to_many :users
end

class CommentsUser < ApplicationRecord
  belongs_to :comment
  belongs_to :user
end

I can do regular select with join:


users_ids = [197]
book_id   = 659

CommentsUser.joins(:comment).where(comments: {book_id: book_id}, user_id: users_ids)
# CommentsUser Load (0.5ms)  SELECT "comments_users".* FROM "comments_users" INNER JOIN "comments" ON "comments"."id" = "comments_users"."comment_id" WHERE "comments"."book_id" = $1 AND "comments_users"."user_id" = $2  [["book_id", 659], ["user_id", 197]]

But it fails If I use delete_all (notice the "comments_users"."":

CommentsUser.joins(:comment).where(comments: {book_id: book_id}, user_id: users_ids).delete_all
# CommentsUser Destroy (0.8ms)  DELETE FROM "comments_users" WHERE ("comments_users"."") IN (SELECT "comments_users"."" FROM "comments_users" INNER JOIN "comments" ON "comments"."id" = "comments_users"."comment_id" WHERE "comments"."book_id" = $1 AND "comments_users"."user_id" = $2)  [["book_id", 659], ["user_id", 197]]

# ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  zero-length delimited identifier at or near """"
# LINE 1: ...comments_users" WHERE ("comments_users"."") IN (SE...

I want to use the same logic used in the first statement to use it for deleting the records- I can find the records, but I can't delete them- any ideas?

-------edit----- CommentsUser has no primary key

CommentsUser.primary_key
# => nil

I can use the where to find records, without adding primary key, is there no way to delete those records as well?

Solution

In a HABTM (Has and Belongs to Many) relationship, Rails does not generate a primary key for the join table by default. When using joins, Rails tries to reference the primary key of the table, and this lack of a primary key can cause SQL syntax errors when trying to delete records.

you can first select the IDs of the records and then perform the delete operation:

[edited]

comment_ids = Comment.where(book_id: book_id).pluck(:id)

CommentsUser.where(comment_id: comment_ids, user_id: users_ids).delete_all

Alternatively, you can use a has_many :through association instead of has_and_belongs_to_many, it allows the join table to have a primary key.