Hello.

I am writing in PHP, MySQL database.

There is a category entity (table categories, id and name fields), there is a product entity (products table, id and name fields) and they are connected by many to many (product_category table, id, category_id and product_id fields).

I have a task that sounds simple enough: "You must remove all empty categories."

I use the Laravel framework and the simplest thing that gets into my head is to request all categories with products (via communication) and then in a loop to delete each category that does not contain products.

But I can’t figure out how to do this in one request. Select the condition for selecting empty categories and delete all entries in the categories table that fit this condition.

  • Hello. I am also interested in this question, but the check shows that the request works as something wrong. The request is missing mention of the third table, we do not need it? - Alex Smirnov
  • Hey. What specific request are you talking about? For more, please :-) - Ivan Torgov

2 answers 2

In the case of laravel, you can use has or whereHas methods https://laravel.com/docs/5.3/eloquent-relationships#querying-relationship-existence

  • Given that I am writing code using Laravel, then for me this is the most suitable option :-) Something I did not pay much attention to this innovation. - Ivan Torg
  • @IvanTorgov for more often look at the documentation) - Alex_01
 DELETE A.* FROM categories A JOIN (select C.id from categories C left join products P on P.category_id=C.id where P.id is NULL ) B ON A.id=B.id

Before deleting, make sure that the internal subquery returns the id of the categories to be deleted.

  • Thank! I was once told about nested queries, but I never applied them in practice. Now I know when they are needed :-) - Ivan Bid