diff options
| -rw-r--r-- | src/main/java/com/juick/service/MessagesServiceImpl.java | 27 |
1 files changed, 15 insertions, 12 deletions
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index bb8c48e1..d0c89ad8 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -277,7 +277,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ void updateRepliesBy(int mid) { List<String> users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies " + "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? " - + "GROUP BY replies.user_id ORDER BY COUNT(replies.reply_id) DESC LIMIT 5", String.class, mid); + + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC LIMIT 5", String.class, mid); String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(",")); getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid); } @@ -439,17 +439,20 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public Message getReply(final int mid, final int rid) { - List<Message> list = getJdbcTemplate().query("SELECT replies.user_id, users.nick," - + "replies.replyto, replies.ts," + "replies.attach, replies.txt, COALESCE(q.txt,t.txt) as quote, " - + "COALESCE(q.user_id, m.user_id) AS to_uid, COALESCE(qu.nick, mu.nick) AS to_name, " - + "replies.updated_at, replies.user_uri as uri, " - + "q.user_uri AS to_uri, replies.reply_uri AS reply_uri, replies.html, q.reply_uri " - + "FROM replies LEFT JOIN users ON replies.user_id = users.id " - + "LEFT JOIN replies q ON replies.message_id = q.message_id and replies.replyto = q.reply_id " - + "LEFT JOIN messages_txt t ON replies.message_id = t.message_id " - + "LEFT JOIN messages m ON replies.message_id = m.message_id " - + "LEFT JOIN users qu ON q.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id " - + "WHERE replies.message_id = ? AND replies.reply_id = ?", (rs, num) -> { + var sql = """ + SELECT replies.user_id, users.nick, + replies.replyto, replies.ts, replies.attach, replies.txt, COALESCE(q.txt,t.txt) as quote, + COALESCE(q.user_id, m.user_id) AS to_uid, COALESCE(qu.nick, mu.nick) AS to_name, + replies.updated_at, replies.user_uri as uri, + q.user_uri AS to_uri, replies.reply_uri AS reply_uri, replies.html, q.reply_uri + FROM replies LEFT JOIN users ON replies.user_id = users.id + LEFT JOIN replies q ON replies.message_id = q.message_id and replies.replyto = q.reply_id + LEFT JOIN messages_txt t ON replies.message_id = t.message_id + LEFT JOIN messages m ON replies.message_id = m.message_id + LEFT JOIN users qu ON q.user_id=qu.id LEFT JOIN users mu ON m.user_id=mu.id + WHERE replies.message_id = ? AND replies.reply_id = ? + """; + List<Message> list = getJdbcTemplate().query(sql, (rs, num) -> { Message msg = new Message(); msg.setMid(mid); |