Removing orphaned Parents with Doctrine

Previously, we considered a common real-life case where, to maintain referential integrity, we might delete all child entries when the parent entry is deleted. For example to delete author with all his articles.

But what if we have to remove author, when article is to be removed?

I know. It sounds crazy! It sounds like a nonsense!

Or no?

Real-Life Case

Here is a real-life case where we'd rather remove parent entry with child entry.

⚠ Warning! This situation looks like a mistake in DB structure design, but who said that all DB's in our universe are flawless?

Suppose we have an Attachment entity and attachments table, which contains a data about attachment files, sizes, paths, extensions, etc. Suppose your project has a bunch of entities, which could have an attachment. For example in the blog application Author can have some profile photo, the same time Post can have a specified cover image, or thumbnail image or something else.

The most obvious way to use attachments in Author and Post entities is to create attachment_id field and foreign key for it.

And here we have two questions:

• Could one attachment be shared by several authors or posts in our case?
• What should be done with attachment entry when related author or post is to be removed?

In theory this DB structure allows the same attachment to be used multiple times in different places. And from this point of view you should not remove attachment entry when you delete the related entry.

But in practice when building application forms it's much easier to create Upload new attachment field than Upload new attachment, or select existing one. And if your app is built this way, there are no options for attachments to be shared. And in this case, if you delete some post, the orphaned attachment will stay in DB.

Is it Ok? In my opinion it should be removed too!

Of course it can be done manually. But let's use Doctrine

Remove parents with Doctrine

Now we're going to try remove attachment entries related with some post. And we assume attachment entries are not shared with any other post or author or anything else! It's important!

With this assumption Doctrine association attributes in Post entity could look like this:

#[ORM\ManyToOne(targetEntity: Attachment::class, cascade: ['remove'])]
#[ORM\JoinColumn(nullable: true, onDelete: 'SET NULL' )]
private ?Attachment $attachment = null;

Here we use cascade attribute of ManyToOne. I think Doctrine developer had added the cascade attribute to the ManyToOne association, thinking of cascade persist, not cascade remove. But it works this way too 😈.

So now we can try to delete the post:

$post = $posts->find(1); // Suppose we are going to delete post number 1
$entityManager->remove($post);
$entityManager->flush();

This will cause Doctrine to execute the next queries:

SELECT attachment_id FROM posts WHERE posts.id = 1; 
-- Suppose we found attachment_id = 10
DELETE FROM attachments WHERE id = 10;
DELETE FROM posts WHERE id = 1;

First of all Doctrine fetches attachment ids, then tries to delete found attachments, and finally tries to delete the article.

Important! To allow the removal of an attachment before an article, it should probably set onDelete: 'SET NULL' on the $attachment field. And the field should be nullable of course.

And it works!

Instead of a conclusion

So we used non-standard feature of Doctrine to solve a non-standard problem, which probably should not exists in well designed DB structure.

Sounds like a piece of useless job.

Maybe in our case we should think of DB structure refactor...

Let me know in comments, what do you think about it.

Thanks for reading 😀