Hi!
I have a small forum on Discourse for students whose research I supervise. Few days ago I converted it to Flarum using the notorious copy&paste technique, with subsequent fixing posts' authors and creation times in the database directly. It worked, but I noticed that discussion dates didn't not update automatically when I edited their posts metadata, as well as user counts for posts and discussions.
I had to additionally run the following query to make it work:
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);
UPDATE users as u SET
u.comments_count =
(SELECT count(*) FROM posts as p
WHERE p.user_id = u.id AND p.type = 'comment'),
u.discussions_count =
(SELECT count(*) FROM discussions as d
WHERE d.start_user_id = u.id);
So, the questions:
- Does Flarum have some built-in function to synchronize its metadata and counters?
- Does somebody know if the above query can be simplified? If does, please answer this SO question.
