For loops in MySQL...kind of

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:

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:

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:

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:

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:

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 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:

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.

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.

Comments

[Tim Bray]

It would be way more sensible to have a table called 'review_periods' with the time period.

Then join this table to multiply out the number of reviews. So insert into review alerts select alert_date = review_due_date - interval review_periods.period month, ..., from review_periods join .....

remember that join means mulitply :)

( and i'm never a fan of triggers because they can be changed without any version control)

[Julien Dephix]

Nice. INSERT INTO ... SELECT can be very interesting to work with indeed.

Note: I'm pretty sure you do not need to SELECT NEW.review_alert_id, you can just use the value directly.

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