I'm a beginner working on a Ruby on Rails application where I want to ensure that deleting a user also deletes their associated messages, participants, and rooms. I have set up associations, but I am facing issues with the deletion process. It's a business directory that has a chat feature and after building it out (following some tutorial) I'm discovering I can't delete the User
I have the following models: User
class User < ApplicationRecord
# Include default devise modules. Others available are:
# :confirmable, :lockable, :timeoutable, :trackable and :omniauthable
devise :database_authenticatable, :registerable,
:recoverable, :rememberable, :validatable
# definine the relationships
has_many :services, dependent: :destroy
has_many :reviews, dependent: :destroy
has_many :favorites, dependent: :destroy
has_many :messages, dependent: :destroy
has_many :participants, dependent: :destroy # Add this line
has_many :rooms, through: :participants, dependent: :destroy # added to test
# used to display all users except the current user
#change to (somehow) only show users that have started a chat with them
scope :all_except, ->(user) { where.not(id: user) }
def self.with_messages
joins(:messages).where.not(messages: { body: nil }).distinct
end
# broadcast that a new user has been added
after_create_commit { broadcast_append_to "users" }
# roles that say what kind of user they are
enum role: { customer: 0, businessperson: 1, admin: 2 }
#validates that the username is unique
validates :username, presence: true, uniqueness: true
#makes sure the user inputs a phone number in an appropriate format 0XXXXXXXXX
validates :phone_number, format: { with: /\A0\d{9}\z/, message: 'must start with 0 and be 10 digits' }, allow_blank: true
# UNCOMMENT after you've seeded user in the schema with :profile picture
#mount_uploader :profile_picture, ProfilePictureUploader # to upload profile picture
def self.ransackable_attributes(auth_object = nil)
["created_at", "email", "encrypted_password", "firstname", "id", "lastname", "password_digest", "phone_number", "profession", "remember_created_at", "reset_password_sent_at", "reset_password_token", "role", "updated_at", "username"]
end
def average_rating
services_with_reviews = services.includes(:reviews).where.not(reviews: { rating: nil })
total_ratings = services_with_reviews.sum { |service| service.reviews.average(:rating).round(0) }
if services_with_reviews.size.positive?
total_ratings / services_with_reviews.size
else
0 # or any default value you prefer when there are no reviews available
end
end
def total_num_ratings
services_with_reviews = services.includes(:reviews).where.not(reviews: { rating: nil })
services_with_reviews.size
end
def self.find_for_database_authentication(warden_conditions)
conditions = warden_conditions.dup
if login = conditions.delete(:login)
where(conditions.to_h).where(["lower(username) = :value OR lower(email) = :value", { value: login.downcase }]).first
else
find_by(conditions)
end
end
end
Participants
class Participant < ApplicationRecord
belongs_to :user
belongs_to :room
end
Room
class Room < ApplicationRecord
validates_uniqueness_of :name
scope :public_rooms, -> { where(is_private: false) }
after_create_commit { broadcast_if_public } #only broadcasting a channel/room if it's public
has_many :messages, dependent: :destroy
has_many :participants, dependent: :destroy
# broadcast the adding of a room if its a public one
def broadcast_if_public
broadcast_append_to 'rooms' unless is_private
end
def self.create_private_room(users, room_name)
single_room = Room.create(name: room_name, is_private: true)
users.each do |user|
Participant.create(user_id: user.id, room_id: single_room.id)
end
single_room
end
def participant?(room, user)
room.participants.where(user: user).exists?
end
end
Message
class Message < ApplicationRecord
belongs_to :user
belongs_to :room
after_create_commit { broadcast_append_to room }
before_create :confirm_participant
def confirm_participant
return unless room.is_private
is_participant = Participant.where(user_id: user.id, room_id: room.id).first
throw :abort unless is_participant
end
end
My Schema
ctiveRecord::Schema[7.0].define(version: 2023_11_17_214308) do
create_table "active_admin_comments", force: :cascade do |t|
t.string "namespace"
t.text "body"
t.string "resource_type"
t.integer "resource_id"
t.string "author_type"
t.integer "author_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.index ["author_type", "author_id"], name: "index_active_admin_comments_on_author"
t.index ["namespace"], name: "index_active_admin_comments_on_namespace"
t.index ["resource_type", "resource_id"], name: "index_active_admin_comments_on_resource"
end
create_table "admin_users", force: :cascade do |t|
t.string "email", default: "", null: false
t.string "encrypted_password", default: "", null: false
t.string "reset_password_token"
t.datetime "reset_password_sent_at"
t.datetime "remember_created_at"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.index ["email"], name: "index_admin_users_on_email", unique: true
t.index ["reset_password_token"], name: "index_admin_users_on_reset_password_token", unique: true
end
create_table "messages", force: :cascade do |t|
t.integer "user_id", null: false
t.integer "room_id", null: false
t.text "body"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.index ["room_id"], name: "index_messages_on_room_id"
t.index ["user_id"], name: "index_messages_on_user_id"
end
create_table "participants", force: :cascade do |t|
t.integer "user_id", null: false
t.integer "room_id", null: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.index ["room_id"], name: "index_participants_on_room_id"
t.index ["user_id"], name: "index_participants_on_user_id"
end
create_table "rooms", force: :cascade do |t|
t.string "name"
t.boolean "is_private", default: false
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
create_table "users", force: :cascade do |t|
t.string "username"
t.string "email", default: "", null: false
t.string "password_digest"
t.string "profession"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "role", default: 0
t.string "encrypted_password", default: "", null: false
t.string "reset_password_token"
t.datetime "reset_password_sent_at"
t.datetime "remember_created_at"
t.string "firstname"
t.string "lastname"
t.string "phone_number"
t.index ["email"], name: "index_users_on_email", unique: true
t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
end
add_foreign_key "messages", "rooms", on_delete: :cascade
add_foreign_key "messages", "users", on_delete: :cascade
add_foreign_key "participants", "rooms"
add_foreign_key "participants", "users"
add_foreign_key "services", "locations"
end
The error message plus something a commenter asked me to do I though i might as well add the results of his suggestion (unless i got it wrong somewhere)
irb(main):013:0> User.last.destroy
User Load (21.2ms) SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT ? [["LIMIT", 1]]
TRANSACTION (1.6ms) begin transaction
Service Load (2.9ms) SELECT "services".* FROM "services" WHERE "services"."user_id" = ? [["user_id", 8]]
Review Load (1.1ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."user_id" = ? [["user_id", 8]]
Favorite Load (0.8ms) SELECT "favorites".* FROM "favorites" WHERE "favorites"."user_id" = ? [["user_id", 8]]
Message Load (1.4ms) SELECT "messages".* FROM "messages" WHERE "messages"."user_id" = ? [["user_id", 8]]
User Destroy (23.9ms) DELETE FROM "users" WHERE "users"."id" = ? [["id", 8]]
TRANSACTION (1.6ms) rollback transaction
C:/Users/XW3R/.local/share/gem/ruby/3.2.0/gems/sqlite3-1.6.5-x64-mingw-ucrt/lib/sqlite3/statement.rb:108:in `step': SQLite3::ConstraintException: FOREIGN KEY constraint failed (ActiveRecord::InvalidForeignKey)
C:/Users/XW3R/.local/share/gem/ruby/3.2.0/gems/sqlite3-1.6.5-x64-mingw-ucrt/lib/sqlite3/statement.rb:108:in `step': FOREIGN KEY constraint failed (SQLite3::ConstraintException)
irb(main):014:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_keys = OFF")
(0.4ms) PRAGMA foreign_keys = OFF
=> []
irb(main):015:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_key_check")
(1.9ms) PRAGMA foreign_key_check
=> []
irb(main):016:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_keys = ON")
(0.1ms) PRAGMA foreign_keys = ON
=> []
irb(main):017:0>
Checking what's violating the contraint
irb(main):017:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_keys = OFF")
(2.2ms) PRAGMA foreign_keys = OFF
=> []
irb(main):018:0> User.last.destroy
User Load (1.5ms) SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT ? [["LIMIT", 1]]
TRANSACTION (0.2ms) begin transaction
Service Load (0.6ms) SELECT "services".* FROM "services" WHERE "services"."user_id" = ? [["user_id", 8]]
Review Load (0.3ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."user_id" = ? [["user_id", 8]]
Favorite Load (0.4ms) SELECT "favorites".* FROM "favorites" WHERE "favorites"."user_id" = ? [["user_id", 8]]
Message Load (1.7ms) SELECT "messages".* FROM "messages" WHERE "messages"."user_id" = ? [["user_id", 8]]
User Destroy (2.6ms) DELETE FROM "users" WHERE "users"."id" = ? [["id", 8]]
TRANSACTION (10.9ms) commit transaction
(Object doesn't support #inspect)
=>
irb(main):019:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_key_check")
(6.9ms) PRAGMA foreign_key_check
=> [{"table"=>"participants", "rowid"=>13, "parent"=>"users", "fkid"=>1}]
irb(main):020:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_keys = ON")
(0.1ms) PRAGMA foreign_keys = ON
=> []
irb(main):021:0>
After adding belongs_to :participant to Message model and restarting the console
PS C:\Users\XW3R\Documents\GitHub\WindhoekTradespersonPlatform> rails console
Loading development environment (Rails 7.0.8)
irb(main):001:0> User.last.destroy
User Load (0.5ms) SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT ? [["LIMIT", 1]]
TRANSACTION (0.1ms) begin transaction
Service Load (0.2ms) SELECT "services".* FROM "services" WHERE "services"."user_id" = ? [["user_id", 7]]
Review Load (0.1ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."service_id" = ? [["service_id", 36]]
Image Load (0.2ms) SELECT "images".* FROM "images" WHERE "images"."service_id" = ? [["service_id", 36]]
Favorite Load (0.2ms) SELECT "favorites".* FROM "favorites" WHERE "favorites"."service_id" = ? [["service_id", 36]]
Service Destroy (1.1ms) DELETE FROM "services" WHERE "services"."id" = ? [["id", 36]]
Review Load (0.2ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."user_id" = ? [["user_id", 7]]
Favorite Load (0.2ms) SELECT "favorites".* FROM "favorites" WHERE "favorites"."user_id" = ? [["user_id", 7]]
Participant Load (0.2ms) SELECT "participants".* FROM "participants" WHERE "participants"."user_id" = ? [["user_id", 7]]
Participant Destroy (0.4ms) DELETE FROM "participants" WHERE "participants"."id" = ? [["id", 3]]
Room Load (0.2ms) SELECT "rooms".* FROM "rooms" WHERE "rooms"."id" = ? LIMIT ? [["id", 22], ["LIMIT", 1]]
Message Load (0.3ms) SELECT "messages".* FROM "messages" WHERE "messages"."room_id" = ? [["room_id", 22]]
Message Destroy (0.4ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 11]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 12]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 13]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 14]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 15]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 16]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 17]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 18]]
Message Destroy (0.9ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 19]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 20]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 21]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 22]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 23]]
Message Destroy (0.3ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 24]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 25]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 26]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 27]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 28]]
Message Destroy (0.6ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 29]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 30]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 31]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 32]]
Message Destroy (0.1ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 33]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 35]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 36]]
Message Destroy (0.2ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 41]]
Message Destroy (0.4ms) DELETE FROM "messages" WHERE "messages"."id" = ? [["id", 42]]
Participant Load (0.2ms) SELECT "participants".* FROM "participants" WHERE "participants"."room_id" = ? [["room_id", 22]]
Participant Destroy (0.4ms) DELETE FROM "participants" WHERE "participants"."id" = ? [["id", 4]]
TRANSACTION (2.5ms) rollback transaction
=> false
irb(main):002:0>
EDIT I've tried messing around with the relations, adding stuff and deleting stuff so much so that I'm worried I may have made things worse.
I've added the rest of the code for the models maybe you'll see something. And thank you for the comments so far even though I hadn't given you everything.
Also from what i understand the reason for the participant's class is to have something to check if a person was a participant of that room/conversation. Or to add them as a participant.
To be honest, now that I think about it using users instead of participants would have been better. I regret following that but I'm here now.
EDIT 2 Added the error Code
EDIT 3 Added @Alex's suggestion to see what was violating the constraint
EDIT 4
addedbelongs_to :participant to Message model and restarted the console. Tried User.last.destroy and got false
EDIT 5 Not sure if I messed something up so I'm reposting my models. Below that is an error that I'm getting. It worked but now it's not working so I think there's something I did.
User
class User < ApplicationRecord
# Include default devise modules. Others available are:
# :confirmable, :lockable, :timeoutable, :trackable and :omniauthable
devise :database_authenticatable, :registerable,
:recoverable, :rememberable, :validatable
# definine the relationships
has_many :services, dependent: :destroy
has_many :reviews, dependent: :destroy
has_many :favorites, dependent: :destroy
has_many :messages, dependent: :destroy
has_many :participants, dependent: :destroy # Add this line
has_many :rooms, through: :participants # added to test # deleted "dependent: :destroy "
# used to display all users except the current user
#change to (somehow) only show users that have started a chat with them
scope :all_except, ->(user) { where.not(id: user) }
def self.with_messages
joins(:messages).where.not(messages: { body: nil }).distinct
end
# broadcast that a new user has been added
after_create_commit { broadcast_append_to "users" }
# roles that say what kind of user they are
enum role: { customer: 0, businessperson: 1, admin: 2 }
#validates that the username is unique
validates :username, presence: true, uniqueness: true
#makes sure the user inputs a phone number in an appropriate format 0XXXXXXXXX
validates :phone_number, format: { with: /\A0\d{9}\z/, message: 'must start with 0 and be 10 digits' }, allow_blank: true
# UNCOMMENT after you've seeded user in the schema with :profile picture
#mount_uploader :profile_picture, ProfilePictureUploader # to upload profile picture
def self.ransackable_attributes(auth_object = nil)
["created_at", "email", "encrypted_password", "firstname", "id", "lastname", "password_digest", "phone_number", "profession", "remember_created_at", "reset_password_sent_at", "reset_password_token", "role", "updated_at", "username"]
end
def average_rating
services_with_reviews = services.includes(:reviews).where.not(reviews: { rating: nil })
total_ratings = services_with_reviews.sum { |service| service.reviews.average(:rating).round(0) }
if services_with_reviews.size.positive?
total_ratings / services_with_reviews.size
else
0 # or any default value you prefer when there are no reviews available
end
end
def total_num_ratings
services_with_reviews = services.includes(:reviews).where.not(reviews: { rating: nil })
services_with_reviews.size
end
def self.find_for_database_authentication(warden_conditions)
conditions = warden_conditions.dup
if login = conditions.delete(:login)
where(conditions.to_h).where(["lower(username) = :value OR lower(email) = :value", { value: login.downcase }]).first
else
find_by(conditions)
end
end
end
Messages
class Message < ApplicationRecord
belongs_to :user
belongs_to :room
belongs_to :participant
after_create_commit { broadcast_append_to room }
before_create :confirm_participant
def confirm_participant
return unless room.is_private
is_participant = Participant.where(user_id: user.id, room_id: room.id).first
throw :abort unless is_participant
end
end
Participants
class Participant < ApplicationRecord
belongs_to :user
belongs_to :room # dependent: :destroy removed recentely
end
Review
class Review < ApplicationRecord
belongs_to :user, dependent: :destroy
belongs_to :service, dependent: :destroy
# only have one review UNCOMMENT LATER
#validates :service_id, uniqueness: { scope: :user_id, message: "You have already reviewed this service" }
end
The error
PS C:\Users\XW3R\Documents\GitHub\WindhoekTradespersonPlatform> rails console
Loading development environment (Rails 7.0.8)
irb(main):001:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_keys = OFF")
(0.1ms) PRAGMA foreign_keys = OFF
=> []
irb(main):002:0> User.last.destroy
User Load (16.6ms) SELECT "users".* FROM "users" ORDER BY "users"."id" DESC LIMIT ? [["LIMIT", 1]]
TRANSACTION (0.1ms) begin transaction
Service Load (0.7ms) SELECT "services".* FROM "services" WHERE "services"."user_id" = ? [["user_id", 6]]
Review Load (0.9ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."service_id" = ? [["service_id", 1]]
Review Destroy (10.4ms) DELETE FROM "reviews" WHERE "reviews"."id" = ? [["id", 8]]
User Load (0.6ms) SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ? [["id", 2], ["LIMIT", 1]]
Service Load (0.3ms) SELECT "services".* FROM "services" WHERE "services"."user_id" = ? [["user_id", 2]]
Review Load (0.1ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."service_id" = ? [["service_id", 38]]
Image Load (0.6ms) SELECT "images".* FROM "images" WHERE "images"."service_id" = ? [["service_id", 38]]
Favorite Load (0.4ms) SELECT "favorites".* FROM "favorites" WHERE "favorites"."service_id" = ? [["service_id", 38]]
Service Destroy (1.4ms) DELETE FROM "services" WHERE "services"."id" = ? [["id", 38]]
Review Load (11.1ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."service_id" = ? [["service_id", 39]]
Image Load (0.4ms) SELECT "images".* FROM "images" WHERE "images"."service_id" = ? [["service_id", 39]]
Favorite Load (0.1ms) SELECT "favorites".* FROM "favorites" WHERE "favorites"."service_id" = ? [["service_id", 39]]
Service Destroy (0.2ms) DELETE FROM "services" WHERE "services"."id" = ? [["id", 39]]
Review Load (0.6ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."service_id" = ? [["service_id", 40]]
Image Load (0.1ms) SELECT "images".* FROM "images" WHERE "images"."service_id" = ? [["service_id", 40]]
Favorite Load (0.2ms) SELECT "favorites".* FROM "favorites" WHERE "favorites"."service_id" = ? [["service_id", 40]]
Service Destroy (0.2ms) DELETE FROM "services" WHERE "services"."id" = ? [["id", 40]]
Review Load (0.7ms) SELECT "reviews".* FROM "reviews" WHERE "reviews"."user_id" = ? [["user_id", 2]]
Review Destroy (1.2ms) DELETE FROM "reviews" WHERE "reviews"."id" = ? [["id", 5]]
TRANSACTION (12.2ms) rollback transaction
=> false
irb(main):003:0> ActiveRecord::Base.connection.execute("PRAGMA foreign_key_check")
(2.4ms) PRAGMA foreign_key_check
=> [{"table"=>"participants", "rowid"=>13, "parent"=>"users", "fkid"=>1}]
irb(main):004:0> exit
First, a few notes:
has_many :participants, dependent: :destroy
has_many :rooms, through: :participants, dependent: :destroy
# this `dependent: :destroy` is redundant ^
# so is this one v
has_many :messages, dependent: :destroy
# because you have sqlite deleting it v
add_foreign_key "messages", "users", on_delete: :cascade
# messages should probably belong to Participant in this set up
class Message < ApplicationRecord
belongs_to :participant
end
The code that you show works, I assume you're not showing everything. This is the only way I know how to debug this:
User.last.destroy
#=> FOREIGN KEY constraint failed (SQLite3::ConstraintException) ¯\_(ツ)_/¯
# disable foreign key constraints
ActiveRecord::Base.connection.execute("PRAGMA foreign_keys = OFF")
# this should work now (you have to run this again)
User.last.destroy
# check foreign keys manually to get a helpful error
ActiveRecord::Base.connection.execute("PRAGMA foreign_key_check")
#=> [{"table"=>"payments", "rowid"=>1, "parent"=>"users", "fkid"=>0}]
# now you know where ^
# turn it back on
ActiveRecord::Base.connection.execute("PRAGMA foreign_keys = ON")