From 1f5869fcfe8c537d223b750754025cb2937d4bad Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 7 Mar 2018 14:53:23 +0300 Subject: drop replies tree --- .../com/juick/service/MessagesServiceImpl.java | 51 ++++++++++++---------- 1 file changed, 27 insertions(+), 24 deletions(-) (limited to 'juick-server-jdbc/src/main/java') diff --git a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java index d7067901..fd93a5e1 100644 --- a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -98,6 +98,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ msg.setText(rs.getString(20)); msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(21))); msg.setUpdated(rs.getTimestamp(22).toInstant()); + int quoteUid = rs.getInt(23); + if (quoteUid > 0) { + User quoteUser = new User(); + quoteUser.setUid(quoteUid); + quoteUser.setName(rs.getString(24)); + msg.setTo(quoteUser); + } try { imagesService.setAttachmentMetadata(imgDir, baseImagesUrl, msg); } catch (Exception e) { @@ -306,7 +313,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "messages.readonly, messages.privacy, messages.replies," + "messages.attach, messages.place_id, messages.lat," + "messages.lon, COUNT(favorites.user_id) as likes, messages.hidden," - + "txt.tags, txt.repliesby, txt.txt, '' as q, messages.updated FROM messages " + + "txt.tags, txt.repliesby, txt.txt, '' as q, messages.updated, 0 as to_uid, " + + "NULL as to_name FROM messages " + "INNER JOIN users ON messages.user_id = users.id " + "INNER JOIN messages_txt AS txt " + "ON messages.message_id = txt.message_id " @@ -327,10 +335,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ List list = getJdbcTemplate().query( "SELECT replies.user_id, users.nick," + "replies.replyto, replies.ts," - + "replies.attach, replies.txt, IFNULL(q.txt,t.txt) as quote " + + "replies.attach, replies.txt, IFNULL(q.txt,t.txt) as quote, " + + "COALESCE(q.user_id, m.user_id) AS to_uid, COALESCE(qu.nick, mu.nick) AS to_name " + "FROM replies INNER 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) -> { Message msg = new Message(); @@ -349,6 +361,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ if (!StringUtils.isEmpty(quote)) { msg.setReplyQuote(MessageUtils.formatQuote(quote)); } + int quoteUid = rs.getInt(8); + if (quoteUid > 0) { + User quoteUser = new User(); + quoteUser.setUid(quoteUid); + quoteUser.setName(rs.getString(9)); + msg.setTo(quoteUser); + } return msg; }, @@ -736,7 +755,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "messages.attach,messages.place_id,messages.lat," + "messages.lon,COUNT(favorites.user_id) AS likes,messages.hidden," + "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q, " - + "messages.updated " + + "messages.updated, 0 as to_uid, NULL as to_name " + "FROM (messages INNER JOIN messages_txt " + "ON messages.message_id=messages_txt.message_id) " + "INNER JOIN users ON messages.user_id=users.id " @@ -763,11 +782,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ "0 as lon, 0 as likes, 0 as hidden, " + "NULL as tags, NULL as repliesby, replies.txt, " + "IFNULL(qw.txt, t.txt) as q, " + - "NOW() " + + "NOW(), " + + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name " + "FROM replies INNER JOIN users " + "ON replies.user_id = users.id " + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.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 qw.user_id=qu.id " + + "LEFT JOIN users mu ON m.user_id=mu.id " + "WHERE replies.message_id = :mid ORDER BY replies.reply_id ASC", new MapSqlParameterSource("mid", mid), new MessageMapper()); @@ -883,26 +906,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return reply; }, -hours); } - - @Transactional(readOnly = true) - @Override - public List getNotifications(User user, LocalDateTime before) { - return getNamedParameterJdbcTemplate().query("SELECT n.message_id as mid, n.reply_id, " + - "n.replyto, " + - "n.user_id, users.nick, users.banned, 0 as ago, n.ts, 0 as readonly, 0 as privacy, " + - "0 as replies, n.attach, 0 as place_id, 0 as lat, 0 as lon, 0 as likes, 0 as hidden, " + - "NULL as tags, NULL as repliesby, n.txt, IFNULL(qw.txt, t.txt) as q, NOW() " + - "FROM (SELECT * FROM replies WHERE EXISTS (SELECT 1 FROM subscr_messages WHERE suser_id=:uid " + - "AND replies.user_id!=:uid AND replies.message_id=message_id " + - (before != null ? "AND replies.ts < :before " : StringUtils.EMPTY) + - ")) as n LEFT JOIN users " + - "ON n.user_id = users.id LEFT JOIN replies qw ON n.message_id = qw.message_id " + - "AND n.replyto = qw.reply_id LEFT JOIN messages_txt t on n.message_id = t.message_id " + - "ORDER BY ts DESC LIMIT 20", new MapSqlParameterSource() - .addValue("uid", user.getUid()) - .addValue("before", before), - new MessageMapper()); - } @Transactional(readOnly = true) @Override public List getPopularCandidates() { -- cgit v1.2.3