| 16 | } |
| 17 | |
| 18 | async getMomentById(id) { |
| 19 | const statement = ` |
| 20 | SELECT |
| 21 | m.id id, m.content content, m.createAt createTime, m.updateAt updateTime, |
| 22 | JSON_OBJECT('id', u.id, 'name', u.name, 'avatarUrl', u.avatar_url) author, |
| 23 | IF(COUNT(l.id),JSON_ARRAYAGG( |
| 24 | JSON_OBJECT('id', l.id, 'name', l.name) |
| 25 | ),NULL) labels, |
| 26 | (SELECT IF(COUNT(c.id),JSON_ARRAYAGG( |
| 27 | JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt, |
| 28 | 'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatarUrl', cu.avatar_url)) |
| 29 | ),NULL) FROM comment c LEFT JOIN user cu ON c.user_id = cu.id WHERE m.id = c.moment_id) comments, |
| 30 | (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8000/moment/images/', file.filename)) |
| 31 | FROM file WHERE m.id = file.moment_id) images |
| 32 | FROM moment m |
| 33 | LEFT JOIN user u ON m.user_id = u.id |
| 34 | LEFT JOIN moment_label ml ON m.id = ml.moment_id |
| 35 | LEFT JOIN label l ON ml.label_id = l.id |
| 36 | WHERE m.id = ? |
| 37 | GROUP BY m.id; |
| 38 | `; |
| 39 | try { |
| 40 | const [result] = await connection.execute(statement, [id]); |
| 41 | return result[0]; |
| 42 | } catch (error) { |
| 43 | console.log(error) |
| 44 | } |
| 45 | } |
| 46 | |
| 47 | async getMomentList(offset, size) { |
| 48 | const statement = ` |