I want to make my Flarum forum update its discussion records after manual intervention into the database. (Flarum is still in beta and still lacks many features, do fixing things manually isn't that uncommon.) I managed to compose the following query which does what I wanted:
UPDATE discussions as d SET
d.start_time =
(SELECT min(p.time) FROM posts as p
WHERE p.discussion_id = d.id),
d.last_time =
(SELECT max(p.time) FROM posts as p
WHERE p.discussion_id = d.id),
d.comments_count =
(SELECT count(*) FROM posts as p
WHERE p.discussion_id = d.id AND p.type = 'comment'),
d.participants_count =
(SELECT count(DISTINCT p.user_id) FROM posts as p
WHERE p.discussion_id = d.id),
d.start_post_id =
(SELECT p.id FROM posts as p
WHERE p.discussion_id = d.id
ORDER BY p.number ASC LIMIT 1),
d.start_user_id =
(SELECT p.user_id FROM posts as p
WHERE p.discussion_id = d.id
ORDER BY p.number ASC LIMIT 1),
d.last_post_id =
(SELECT p.id FROM posts as p
WHERE p.discussion_id = d.id
ORDER BY p.number DESC LIMIT 1),
d.last_post_number =
(SELECT p.number FROM posts as p
WHERE p.discussion_id = d.id
ORDER BY p.number DESC LIMIT 1),
d.last_user_id =
(SELECT p.user_id FROM posts as p
WHERE p.discussion_id = d.id
ORDER BY p.number DESC LIMIT 1);
but it looks pretty ugly. I'm sure there is a way to write the same logic in a much more concise and efficient way, but I'm not really into SQL to know how to do it. Could someone tell me how to eliminate these almost duplicated subqueries?