For loops in MySQL...kind of
Tags: mysql, loop
Published: 2023-12-06 15:47:05

Hi everyone, sorry it's been a while!

I had a bit of a breakthrough today, finally achieving (or at least emulating the behaviour of) a for loop in MySQL, which many sources online have said isn't really possible and it's better to use a scripting language like Python or PHP to do it. That is a feasible solution in most cases, however, when I'm inside an `AFTER INSERT` trigger in a table where the entries are themselves added by another `AFTER INSERT` trigger...things get a little tricky there!

## So, what's the situation?

In the platform I'm building, we have reviews on sites every so often, with the due date entered by a user when they have that information, and we need alerts to go out when the review is 9 months away, 6 months away, and 3 months away, as well as on the due date.

So our reviews table looks like this:

```sql
CREATE TABLE `reviews` (
  `review_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL,
  `review_due_date` datetime NOT NULL,
  PRIMARY KEY (`review_id`),
  KEY `FK_reviews_site_id_idx` (`site_id`),
  CONSTRAINT `FK_reviews_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE ON UPDATE CASCADE
);
```

All simple enough, then we have another table with the alerts: 

```sql
CREATE TABLE `review_alerts` (
  `review_alert_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `review_id` int(11) unsigned NOT NULL,
  `alert_type` enum('due_date','3_month_date','6_month_date','9_month_date') NOT NULL,
  `alert_date` datetime NOT NULL,
  PRIMARY KEY (`review_alert_id`)
);
```

And now we need a way to track which users have seen and acknowledged the alerts:

```sql
CREATE TABLE `review_alert_acknowledgements` (
  `review_alert_acknowledgement_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `review_alert_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  `acknowledged` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`review_alert_acknowledgement_id`),
  UNIQUE KEY `UQ_review_alert_id_user_id` (`review_alert_id`,`user_id`),
  KEY `FK_review_alert_acknowledgements_user_id_idx` (`user_id`),
  CONSTRAINT `FK_review_alert_acknowledgements_review_alert_id` FOREIGN KEY (`review_alert_id`) REFERENCES `review_alerts` (`review_alert_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_review_alert_acknowledgements_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
);
```

So when we create a new review, we need to automatically insert its alert dates into the `alerts` table, then automatically insert acknowledgement records into the `acknowledgements` table, for each of those alerts **and** for each user. A for loop situation if I've ever seen one.

## So, how on earth do we even begin to approach this?

Let's begin with the easy part: inserting the alerts when we create a new review, this goes in the `AFTER INSERT` trigger on `reviews`:

```sql
BEGIN
  INSERT INTO `review_alerts` (`review_id`,`alert_date`,`alert_type`) VALUES (NEW.review_id,NEW.review_due_date,'due_date');
  INSERT INTO `review_alerts` (`review_id`,`alert_date`,`alert_type`) VALUES (NEW.review_id,NEW.review_due_date - INTERVAL 3 MONTH,'3_month_date');
  INSERT INTO `review_alerts` (`review_id`,`alert_date`,`alert_type`) VALUES (NEW.review_id,NEW.review_due_date - INTERVAL 6 MONTH,'6_month_date');
  INSERT INTO `review_alerts` (`review_id`,`alert_date`,`alert_type`) VALUES (NEW.review_id,NEW.review_due_date - INTERVAL 9 MONTH,'9_month_date');
END
```

And now the tricky part: insert the acknowledgement records for each alert and user when creating the alerts, oh, and did I also mention we only want a subset of users, based on them having the `admin` role? A lovely little added complication there, I'm sure you'll agree. Let's go into the `AFTER INSERT` trigger on `review_alerts`:

```sql
BEGIN
  FOR user_id IN -- Oh, wait, that won't work, how about...
  FOR EACH (SELECT user_id FROM users) -- Yeah that won't work either. How on earth do I achieve this?
END
```

Enter the [`INSERT SELECT`](https://www.w3schools.com/sql/sql_insert_into_select.asp) statement.

First of all, can we achieve a table with all the user IDs we want, coupled with just a number? Yes! Let's try:

```sql
SELECT
  (SELECT 1),
  user_id
  FROM `users` WHERE user_role = 'admin'; -- The roles are a bit more involved than just a field in the users table, but this is just for demo purposes!
```

And sure enough, we get

```
+------------+---------+
| (SELECT 1) | user_id |
+------------+---------+
| 1          | 1       |
| 1          | 2       |
| 1          | 3       |
| 1          | 5       |
| 1          | 8       |
| 1          | 13      |
| 1          | 21      |
+------------+---------+
```

Success! And now we simply replicate this functionality in our INSERT SELECT statement within the AFTER INSERT trigger.

```sql
BEGIN
  INSERT INTO `review_alert_acknowledgements` (`review_alert_id`,`user_id`) SELECT DISTINCT
    (SELECT NEW.review_alert_id),
    users.user_id
    FROM `users`
    WHERE users.role = 'admin';
END
```

Do you need the `DISTINCT`? If your role assignment system works as I've reduced it to here for the purposes of this demo, then no. Every user will either be an admin or be something else. In my system as-is however, then it is probable that you'll need it in order to avoid any unique key constraint errors. For example if some of your admins are also superadmins and you want them to be able to do everything an admin can do (because why wouldn't you?)

And that's it! At least for "looping" in an `INSERT` statement, anyway.