MYSQL - get parent images without resized children

Question

I have the following image table in mySQL, which has been given to me to correct, where all the images related to a set of products are stored. These images have been (most of them have been) or will be resized so that adaptive images can be used on the front end of an app. However when the front end calls the api and checks the requested product objects, some "main" images seem to not have "alternatives"(resized/children). Now I do not know how the api is saving these images when they are run through the resizer. I have just been asked to check the table to make sure all the images have alternative sizes, and to, as far as possible, try and see what is wrong. For this I have tried to run a query against the image table to get all the parent images that don't have "children".

A sample table is as follows, where the parent_id key is a foreign key that references the id key of the main image. This constraint was set on the table, where **** is replacing actual values for the question in hand:

CONSTRAINT `****` FOREIGN KEY (`parent_id`) REFERENCES `image` (`id`)

Sample table:

+----+-------+-------------+--------------+--------------+
| id | width |    path     |   publicId   |   parent_id  |
+----+-------+-------------+--------------+--------------+
| 1  |  300  |  somewhere  |      12      |     NULL     |
+----+-------+-------------+--------------+--------------+
| 2  |  400  |  somewhere2 |      34      |      1       |
+----+-------+-------------+--------------+--------------+
| 3  |  500  |  somewhere3 |      56      |      1       |
+----+-------+-------------+--------------+--------------+
| 4  |  200  |  somewhere4 |      78      |     NULL     |
+----+-------+-------------+--------------+--------------+
| 5  |  200  |  somewhere5 |      90      |     NULL     |
+----+-------+-------------+--------------+--------------+

In order to get all the parent images without children I used the following query:

SELECT publicId, version, mimeType FROM image i WHERE parent_id IS NULL AND NOT EXISTS (SELECT id FROM image r WHERE r.parent_id = i.id)

Is there a way I can check that this is in fact returning the right number of un-resized parent images given that there are total of 125397 rows in the table?

Answer 1

Do positive and negative testing on the data returned to get comfort over the query.

Positive testing is to confirm that the results returned by your query comply with your business requirements. Negative testing is to confirm that records excluded by your query should be excluded according to your business requirements.

However, the query seems to be the right one.