I’m using an adjacency list to store a hierarchy of categories. There can be many root categories each with many children/descendants.
defmodule WordSolverDal.Categories.Category do
use Ecto.Schema
import Ecto.Changeset
use Arbor.Tree,
foreign_key: :parent_id,
foreign_key_type: :integer
schema "categories" do
field(:name, :string)
field(:parent_id, :integer)
many_to_many(:answers, WordSolverDal.Answers.Answer, join_through: "answers_categories")
timestamps()
end
@required [:name]
@optional [:parent_id]
def changeset(category, attrs) do
category
|> cast(attrs, @required ++ @optional)
|> validate_required(@required)
|> validate_length(:name, min: 3)
|> unique_constraint(:name, name: :unique_categories_name_parent_id_null)
|> unique_constraint(:name, name: :unique_categories_name_parent_id)
end
end
The db throws a constraint error if a user tries to delete a record that has children due to the foreign key constraint on :parent_id.
constraint error when attempting to delete struct:
* "categories_parent_id_fkey" (foreign_key_constraint)
If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `foreign_key_constraint/3` on your changeset with the constraint
`:name` as an option.
This error gives advice on how to handle the error without raising an exception but I don’t see how this could work. As I understand it, foreign_key_constraint/3 is typically used for insert/update change sets.
How can I attempt a delete and not raise an exception as described? Or should I just rescue the error?
A Changeset is just a validation mechanism and there’s nothing inherent in them that prevents their use in deletion. I do agree that most people focus on insert and update when discussing Changesets, but validation is validation and there can be reasons to validate delete operations including your scenario. You’ll see that Ecto.Repo.delete/2 can take a Changeset: Ecto.Repo — Ecto v3.11.1.
Personally, I like the idea of Changesets and handling validation prior to attempting operations on the database, even for deletes… though I’d likely have delete-specific Changeset checks. It might be overly defensive on this, because I also absolutely believe that it’s not a substitute for correct constraints in the database, too, but by validating in the application I feel like I’m more in control. I also like raised exceptions to be logged and not depending on them for my logic means that I can be very consistent about that without thinking too much about it.
As far as I know, this is the idiomatic solution. Another option is to manually catch the exception within a try/rescue block, but this method is rather ugly so I’d recommend to use it as a last resort.
no_assoc_constraint will indeed give you nice Changeset error that you can directly work with in forms.
But if for one reason or another that’s not an option for you then you could always take the direct route:
category_id = 123 # desired parent id goes here
query = from(c in Category, where: c.parent_id == ^category_id)
children_count = Repo.aggregate(query, :count, :id)
if children_count > 0 do
# construct and/or handle an error here and act on it
else
# all good, you can delete the category
end
But isn’t this in effect just Ecto.Changeset.foreign_key_constraint/3 except performing the foreign key check fully manually in advance?
My understanding is that database constraint validations (unique, foreign key, etc.) in Changesets aren’t checked in advance and rely on database error reporting to return a Changeset error post-execution attempt. Using a Changeset here with a foreign_key_constraint/3 check allows the database constraint to abort the database transaction on query with the appropriate error where Ecto picks it up and report it as a Changeset error. So while what your saying will work, I’m not sure I see the advantage to handling that way rather than submitting a Changeset… two queries vs. one.
defmodule WordSolverDal.Categories.Category do
schema "categories" do
has_many :descendant_categories, __MODULE__, foreign_key: :parent_id
end
end
def delete_category(category) do
category
|> Ecto.Changeset.change()
|> Ecto.Changeset.no_assoc_constraint(:descendant_categories,
name: "categories_parent_id_fkey",
message: "there is descendant categor[y|ies] associated with this one"
)
|> Repo.delete()
end
