Simplify multiple subqueries in UPDATE statement

Question

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, so 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?

The above mentioned tables definition is the following (with some details omitted):

CREATE TABLE discussions (
  id                   int unsigned NOT NULL AUTO_INCREMENT,
  title                varchar(200) NOT NULL,
  comments_count       int(10) unsigned NOT NULL DEFAULT '0',
  participants_count   int(10) unsigned NOT NULL DEFAULT '0',
  number_index         int(10) unsigned NOT NULL DEFAULT '0',
  start_time           datetime NOT NULL,
  start_user_id        int(10) unsigned DEFAULT NULL,
  start_post_id        int(10) unsigned DEFAULT NULL,
  last_time            datetime DEFAULT NULL,
  last_user_id         int(10) unsigned DEFAULT NULL,
  last_post_id         int(10) unsigned DEFAULT NULL,
  last_post_number     int(10) unsigned DEFAULT NULL,
  ... );

CREATE TABLE posts (
  id                   int(10) unsigned NOT NULL AUTO_INCREMENT,
  discussion_id        int(10) unsigned NOT NULL,
  number               int(10) unsigned DEFAULT NULL,
  time                 datetime NOT NULL,
  user_id              int(10) unsigned DEFAULT NULL,
  type                 varchar(100) DEFAULT NULL,
  ... );

Flarum uses MySQL as its primary storage backend, so MySQL-specific solution would be fine. However, it would be great if someone knows how to solve the problem in ANSI-SQL.

There is no shortcut to what you want. You have to do this way only i guess. But I would love to see if it can happen. — FallAndLearn, Nov 26 '16 at 19:26
@firegurafiku...I see you edited this post. Do you still need a solution nearly two years later? — Parfait, Oct 16 '18 at 21:42
@Parfait, if only out of curiosity. I just look after my questions when I can. Fix typos, for example :). — firegurafiku, Oct 16 '18 at 22:36

ScaisEdge · Answer 1 · 2016-11-26T19:55:58.173

1

you could use a inner join and use max and min for first and last

UPDATE discussions as d 
INNER JOIN posts as p on d.id = p.discussion_id
SET d.start_time = min(p.time),
    d.last_time = max(p.time),
    d.comments_count = count(*),
    d.participants_count = count(DISTINCT p.user_id) ,
    d.start_post_id = min(p.id ),
    d.start_user_id = min(p.user_id  ),
    d.last_post_id =  max(  p.id ),
    d.last_post_number = max( p.number ),
    d.last_user_id = max( p.user_id  ),
    d.comments_count = sum( case when p.type = 'comment' then 1 else 0)
GROUP BY d.id

edited Nov 26 '16 at 19:55

answered Nov 26 '16 at 19:24

ScaisEdge

131,976
10
91
107

I think it won't work as desired. Firstly you are changing the schema of the table 'discussions' – FallAndLearn Nov 26 '16 at 19:29
i have changed nothings .. i have simply refactored the query using .. sql features ..like required by OP – ScaisEdge Nov 26 '16 at 19:30
Looks promising! But I cannot get how `d.last_post_id = min( d.last_post_id )` is going to work here. MySQL issues *“Error: Invalid use of group function”* message about it. – firegurafiku Nov 26 '16 at 19:37
@firegurafiku . could be that you should intechange the max and min (i have not your data sample) but the concept is easy .. select order by col asc limit 1 is equals to mix(col) and for oreder desc limit 1 is equal to max so .. anyway .. i have update the answer .. set (hope )correctly the min and the max – ScaisEdge Nov 26 '16 at 19:41
Ops ..( i have copied the wrong cols) answer updated with p.* cols .. – ScaisEdge Nov 26 '16 at 19:45
@scaisEdge: Okay, got the `p.***` issue. But `d.last_post_id = max(p.id )` still isn't correct as I need to find the row with maximum `p.number` within a discussion, and return `p.id` instead of the number. – firegurafiku Nov 26 '16 at 19:50
@firegurafiku you need the result grouped by d.id .. i think .. i have update the answer – ScaisEdge Nov 26 '16 at 19:56

Simplify multiple subqueries in UPDATE statement

1 Answers1