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?
1
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.
1
There is very little of what you’re doing here that makes sense.
Is the assocation between comments and users really many to many? That’s pretty attypical.
What you actually want is most likely a one to many assocation between users and comments (a comment belongs to the user that wrote it):
class Book < ApplicationRecord
has_many :comments
has_many :commenting_users, through: :comments, source: :user
end
# rails g model comment body:string book:belongs_to user:belongs_to
class Comment < ApplicationRecord
belongs_to :book
belongs_to :user
end
class User < ApplicationRecord
has_many :comments
has_many :commented_books, through: :comments, source: :book
end
In this case there is no need for a separate join table as the comments table is the the join table.
In a has_and_belongs_to_many assocation the association does not use a intermediate model. It’s just a wrapper around a simple join table with the two foreign keys. It’s only suitible when the join is just plumbing and you don’t need any logic or additional columns to describe the relations between the two entities or need to create/delete the join table rows directly.
How often that scenario actually occurs is debatable.
If you want a join model use a has_many through: assocation and name the model CommentUser and table comment_users. You also need to ensure that the table has a primary key column or your model won’t work very well. You can either just roll back and do it again the right way or rename the table in a migration and add the primary key if you have pushed this dud into production.