Refactor messages service, remove 2 unneeded SQL queries

8 files changed, 159 insertions, 192 deletions

diff --git a/src/main/java/com/juick/service/MessagesService.java b/src/main/java/com/juick/service/MessagesService.java
index 995e8e53..88f79c14 100644
--- a/src/main/java/com/juick/service/MessagesService.java
+++ b/src/main/java/com/juick/service/MessagesService.java
@@ -77,8 +77,6 @@ public interface MessagesService {
 
     Message getReplyByUri(String replyUri);
 
-    User getMessageAuthor(int mid);
-
     List<Pair<Integer, User>> getMessagesRecommendations(Collection<Integer> mids);
 
     @Cacheable(value = "discover", key="{ #visitorUid, #before }")
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index add8db22..9fe1873a 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -278,7 +278,8 @@ 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, users.nick 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);
     }
@@ -413,33 +414,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Transactional(readOnly = true)
     @Override
     public Optional<Message> getMessage(final int mid) {
-
-        List<Message> list = getJdbcTemplate().query("SELECT messages.message_id as mid, 0 as rid, 0 as replyto, "
-                + "messages.user_id as uid, users.nick, users.banned as banned, " + "" + "messages.ts,"
-                + "messages.readonly, messages.privacy, messages.replies,"
-                + "messages.attach, COUNT(DISTINCT favorites.user_id) as likes, messages.hidden,"
-                + "'' as tags, txt.repliesby, txt.txt, '' as q, messages.updated as updated, 0 as to_uid, "
-                + "NULL as to_name, txt.updated_at, '' as reply_user_uri, '' as to_uri, '' as reply_uri, 0 as html, 0 as unread FROM messages "
-                + "INNER JOIN users ON messages.user_id = users.id " + "INNER JOIN messages_txt AS txt "
-                + "ON messages.message_id = txt.message_id " + "LEFT JOIN favorites "
-                + "ON messages.message_id = favorites.message_id AND favorites.like_id=1 "
-                + "LEFT JOIN messages_tags ON messages_tags.message_id=txt.message_id "
-                + "LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id "
-                + "WHERE messages.message_id = ? AND users.banned = 0 "
-                + "GROUP BY mid, rid, replyto, uid, nick, banned, messages.ts, readonly, "
-                + "privacy, replies, attach, repliesby, q, txt.txt, updated_at, reply_user_uri, to_uri, reply_uri, html, unread",
-                new MessageMapper(), mid);
-        if (!list.isEmpty()) {
-            final Message message = list.get(0);
-            Map<Integer, Set<Reaction>> reactionStats = updateReactionsFor(Collections.singletonList(mid));
-            message.setReactions(reactionStats.get(message.getMid()));
-            message.setEntities(MessageUtils.getEntities(message));
-            List<Pair<Integer, User>> allRecommendations = getMessagesRecommendations(List.of(mid));
-
-            message.setRecommendations(new HashSet<>(allRecommendations.stream()
-                    .filter(r -> r.getLeft().equals(mid)).map(Pair::getRight).toList()));
-            message.getRecommendations().forEach(r -> r.setAvatar(webApp.getAvatarUrl(r)));
-            return Optional.of(message);
+        var messages = getMessages(AnonymousUser.INSTANCE, List.of(mid));
+        if (messages.size() == 1) {
+            var message = messages.get(0);
+            if (!message.getUser().isBanned()) {
+                return Optional.of(messages.get(0));
+            }
         }
         return Optional.empty();
     }
@@ -461,47 +441,47 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 WHERE replies.message_id = ? AND replies.reply_id = ?
                 """;
         List<Message> list = getJdbcTemplate().query(sql, (rs, num) -> {
-                    Message msg = new Message();
-
-                    msg.setMid(mid);
-                    msg.setRid(rid);
-                    msg.setUser(new User());
-                    msg.getUser().setUid(rs.getInt(1));
-                    msg.getUser().setName(rs.getString(2));
-                    if (msg.getUser().getUid() == 0) {
-                        msg.getUser().setName(AnonymousUser.INSTANCE.getName());
-                        msg.getUser()
-                                .setUri(URI.create(Optional.ofNullable(rs.getString(11)).orElse(StringUtils.EMPTY)));
-                    }
-                    msg.setReplyto(rs.getInt(3));
-                    msg.setCreated(rs.getTimestamp(4).toInstant());
-                    msg.setAttachmentType(rs.getString(5));
-                    msg.setText(rs.getString(6));
-                    String quote = rs.getString(7);
-
-                    if (!StringUtils.isEmpty(quote)) {
-                        msg.setReplyQuote(MessageUtils.formatQuote(quote));
-                    }
-                    int quoteUid = rs.getInt(8);
-                    User quoteUser = new User();
-                    quoteUser.setUid(quoteUid);
-                    quoteUser.setName(Optional.ofNullable(rs.getString(9)).orElse(AnonymousUser.INSTANCE.getName()));
-                    quoteUser.setUri(URI.create(Optional.ofNullable(rs.getString(12)).orElse(StringUtils.EMPTY)));
-                    msg.setTo(quoteUser);
-                    msg.setUpdatedAt(rs.getTimestamp(10).toInstant());
-                    msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(13)).orElse(StringUtils.EMPTY)));
-                    msg.setHtml(rs.getBoolean(14));
-                    msg.setReplyToUri(URI.create(Optional.ofNullable(rs.getString(15)).orElse(StringUtils.EMPTY)));
-                    if (StringUtils.isNotEmpty(msg.getAttachmentType())) {
-                        try {
-                            storageService.setAttachmentMetadata(baseImagesUrl, msg);
-                        } catch (Exception e) {
-                            logger.warn("exception reading images for mid {} rid {}", msg.getMid(), msg.getRid(), e);
-                        }
-                    }
-                    msg.setEntities(MessageUtils.getEntities(msg));
-                    return msg;
-                }, mid, rid);
+            Message msg = new Message();
+
+            msg.setMid(mid);
+            msg.setRid(rid);
+            msg.setUser(new User());
+            msg.getUser().setUid(rs.getInt(1));
+            msg.getUser().setName(rs.getString(2));
+            if (msg.getUser().getUid() == 0) {
+                msg.getUser().setName(AnonymousUser.INSTANCE.getName());
+                msg.getUser()
+                        .setUri(URI.create(Optional.ofNullable(rs.getString(11)).orElse(StringUtils.EMPTY)));
+            }
+            msg.setReplyto(rs.getInt(3));
+            msg.setCreated(rs.getTimestamp(4).toInstant());
+            msg.setAttachmentType(rs.getString(5));
+            msg.setText(rs.getString(6));
+            String quote = rs.getString(7);
+
+            if (!StringUtils.isEmpty(quote)) {
+                msg.setReplyQuote(MessageUtils.formatQuote(quote));
+            }
+            int quoteUid = rs.getInt(8);
+            User quoteUser = new User();
+            quoteUser.setUid(quoteUid);
+            quoteUser.setName(Optional.ofNullable(rs.getString(9)).orElse(AnonymousUser.INSTANCE.getName()));
+            quoteUser.setUri(URI.create(Optional.ofNullable(rs.getString(12)).orElse(StringUtils.EMPTY)));
+            msg.setTo(quoteUser);
+            msg.setUpdatedAt(rs.getTimestamp(10).toInstant());
+            msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(13)).orElse(StringUtils.EMPTY)));
+            msg.setHtml(rs.getBoolean(14));
+            msg.setReplyToUri(URI.create(Optional.ofNullable(rs.getString(15)).orElse(StringUtils.EMPTY)));
+            if (StringUtils.isNotEmpty(msg.getAttachmentType())) {
+                try {
+                    storageService.setAttachmentMetadata(baseImagesUrl, msg);
+                } catch (Exception e) {
+                    logger.warn("exception reading images for mid {} rid {}", msg.getMid(), msg.getRid(), e);
+                }
+            }
+            msg.setEntities(MessageUtils.getEntities(msg));
+            return msg;
+        }, mid, rid);
 
         return list.isEmpty() ? null : list.get(0);
     }
@@ -515,22 +495,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
     @Transactional(readOnly = true)
     @Override
-    public User getMessageAuthor(final int mid) {
-        List<User> list = getJdbcTemplate().query(
-                "SELECT messages.user_id, users.nick "
-                        + "FROM messages INNER JOIN users ON messages.user_id = users.id WHERE messages.message_id = ?",
-                (rs, num) -> {
-                    User res = new User();
-                    res.setUid(rs.getInt(1));
-                    res.setName(rs.getString(2));
-                    return res;
-                }, mid);
-
-        return list.isEmpty() ? null : list.get(0);
-    }
-
-    @Transactional(readOnly = true)
-    @Override
     public List<Pair<Integer, User>> getMessagesRecommendations(final Collection<Integer> mids) {
         return getNamedParameterJdbcTemplate().query(
                 "SELECT DISTINCT favorites.message_id, users.id, users.nick, favorites.user_uri FROM favorites "
@@ -820,31 +784,32 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     public List<Message> getMessages(final User visitor, final List<Integer> mids) {
         if (CollectionUtils.isNotEmpty(mids)) {
 
-            var query = "WITH RECURSIVE banned(message_id, reply_id) "
-                    + "AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids) "
-                    + "AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) "
-                    + "OR EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)) "
-                    + "UNION ALL SELECT replies.message_id, replies.reply_id FROM replies INNER JOIN banned "
-                    + "ON banned.reply_id = replies.replyto AND replies.reply_id != replies.replyto AND banned.message_id=replies.message_id "
-                    + "WHERE replies.message_id IN (:ids)) " + "SELECT messages.message_id, 0 as rid, 0 as replyto, "
-                    + "messages.user_id,users.nick, 0 as usr_banned, " + "messages.ts,"
-                    + "messages.readonly,messages.privacy, 1.*messages.replies-COUNT(DISTINCT banned.reply_id) as replies,"
-                    + "messages.attach,COUNT(DISTINCT favorites.user_id) AS likes,messages.hidden,"
-                    + "'' as tags, messages_txt.repliesby, messages_txt.txt, '' as q, "
-                    + "messages.updated, 0 as to_uid, NULL as to_name, messages_txt.updated_at, '' as m_user_uri, "
-                    + "'' as to_uri, '' as msg_reply_uri, 0 as html, (1.*messages.replies - subscr_messages.last_read_rid) > 0 as unread "
-                    + "FROM (messages INNER JOIN messages_txt " + "ON messages.message_id=messages_txt.message_id) "
-                    + "INNER JOIN users ON messages.user_id=users.id " + "LEFT JOIN subscr_messages "
-                    + "ON messages.message_id=subscr_messages.message_id AND subscr_messages.suser_id=:uid "
-                    + "LEFT JOIN favorites " + "ON messages.message_id = favorites.message_id AND favorites.like_id=1 "
-                    + "LEFT JOIN banned " + "ON messages.message_id = banned.message_id "
-                    + "LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id "
-                    + "LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id "
-                    + "WHERE messages.message_id IN (:ids) GROUP BY "
-                    + "messages.message_id, rid, replyto, messages.user_id, users.nick, usr_banned, messages.ts, "
-                    + "messages.readonly, messages.privacy, messages.attach, messages.hidden, "
-                    + "messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at, "
-                    + "m_user_uri, msg_reply_uri, html, subscr_messages.last_read_rid";
+            var query = """
+                    WITH RECURSIVE banned(message_id, reply_id)
+                    AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids)
+                    AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id)
+                    OR EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid))
+                    UNION ALL SELECT replies.message_id, replies.reply_id FROM replies INNER JOIN banned
+                    ON banned.reply_id = replies.replyto AND replies.reply_id != replies.replyto AND banned.message_id=replies.message_id
+                    WHERE replies.message_id IN (:ids)) SELECT messages.message_id, 0 as rid, 0 as replyto,
+                    messages.user_id,users.nick, users.banned as usr_banned, messages.ts,
+                    messages.readonly,messages.privacy, 1.*messages.replies-COUNT(DISTINCT banned.reply_id) as replies,
+                    messages.attach,COUNT(DISTINCT favorites.user_id) AS likes,messages.hidden,
+                    '' as tags, messages_txt.repliesby, messages_txt.txt, '' as q,
+                    messages.updated, 0 as to_uid, NULL as to_name, messages_txt.updated_at, '' as m_user_uri,
+                    '' as to_uri, '' as msg_reply_uri, 0 as html, (1.*messages.replies - subscr_messages.last_read_rid) > 0 as unread
+                    FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id)
+                    INNER JOIN users ON messages.user_id=users.id LEFT JOIN subscr_messages
+                    ON messages.message_id=subscr_messages.message_id AND subscr_messages.suser_id=:uid
+                    LEFT JOIN favorites ON messages.message_id = favorites.message_id AND favorites.like_id=1
+                    LEFT JOIN banned ON messages.message_id = banned.message_id
+                    LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id
+                    LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id
+                    WHERE messages.message_id IN (:ids) GROUP BY
+                    messages.message_id, rid, replyto, messages.user_id, users.nick, usr_banned, messages.ts,
+                    messages.readonly, messages.privacy, messages.attach, messages.hidden,
+                    messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at,
+                    m_user_uri, msg_reply_uri, html, subscr_messages.last_read_rid""";
 
             List<Message> msgs = getNamedParameterJdbcTemplate().query(query,
                     new MapSqlParameterSource("ids", mids).addValue("uid", visitor.getUid()), new MessageMapper());
@@ -871,12 +836,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     }
 
     private Map<Integer, Set<Reaction>> updateReactionsFor(final List<Integer> mids) {
-        // This method always called from the transactional block, so it should not be marked as transactional itself
+        // This method always called from the transactional block, so it should not be
+        // marked as transactional itself
         return getNamedParameterJdbcTemplate().query("""
-                        select f.message_id as mid, f.like_id as lid,
-                        r.description as descr, count(f.like_id) as cnt
-                        from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id
-                        where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""",
+                select f.message_id as mid, f.like_id as lid,
+                r.description as descr, count(f.like_id) as cnt
+                from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id
+                where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""",
                 new MapSqlParameterSource("mids", mids), (ResultSet rs) -> {
                     Map<Integer, Set<Reaction>> results = new HashMap<>();
                     while (rs.next()) {
@@ -899,27 +865,28 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Override
     public List<Message> getReplies(final User user, final int mid) {
         List<Message> replies = getNamedParameterJdbcTemplate().query("WITH RECURSIVE banned(reply_id, user_id) AS ("
-                        + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid "
-                        + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) "
-                        + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies "
-                        + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) "
-                        + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, "
-                        + "replies.user_id, users.nick, users.banned, " + "replies.ts, "
-                        + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, "
-                        + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, "
-                        + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, "
-                        + "replies.updated_at, replies.user_uri as uri, "
-                        + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread "
-                        + "FROM replies LEFT 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 "
-                        + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)"
-                        + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) "
-                        + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) "
-                        + "ORDER BY replies.reply_id ASC", new MapSqlParameterSource()
+                + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid "
+                + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) "
+                + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies "
+                + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) "
+                + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, "
+                + "replies.user_id, users.nick, users.banned, " + "replies.ts, "
+                + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, "
+                + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, "
+                + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, "
+                + "replies.updated_at, replies.user_uri as uri, "
+                + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread "
+                + "FROM replies LEFT 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 "
+                + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)"
+                + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) "
+                + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) "
+                + "ORDER BY replies.reply_id ASC",
+                new MapSqlParameterSource()
                         .addValue("mid", mid)
                         .addValue("uid", user.getUid())
                         .addValue("now", Instant.now().atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE),
@@ -978,34 +945,37 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     public boolean deleteReply(final int uid, final int mid, final int rid) {
         Instant now = Instant.now();
         Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS);
-        Message reply = getReply(mid, rid);
-        if (reply != null) {
-            Instant ts = reply.getUpdatedAt();
-            User author = getMessageAuthor(mid);
-            SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid)
-                    .addValue("rid", rid).addValue("archive_uid", archiveUser.getUid());
-            if (ts.compareTo(messageDeletingWindow) >= 0) {
-                boolean result;
-                // allow to delete author replies and replies to author
-                if (author.getUid() == uid) {
-                    result = getNamedParameterJdbcTemplate()
-                            .update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid", parameters) > 0;
+        var message = getMessage(mid);
+        if (message.isPresent()) {
+            Message reply = getReply(mid, rid);
+            if (reply != null) {
+                Instant ts = reply.getUpdatedAt();
+                User author = message.get().getUser();
+                SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid)
+                        .addValue("rid", rid).addValue("archive_uid", archiveUser.getUid());
+                if (ts.compareTo(messageDeletingWindow) >= 0) {
+                    boolean result;
+                    // allow to delete author replies and replies to author
+                    if (author.getUid() == uid) {
+                        result = getNamedParameterJdbcTemplate()
+                                .update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid", parameters) > 0;
+                    } else {
+                        result = getNamedParameterJdbcTemplate().update(
+                                "DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid",
+                                parameters) > 0;
+                    }
+                    if (result) {
+                        getNamedParameterJdbcTemplate()
+                                .update("UPDATE messages SET replies=replies-1 WHERE message_id=:mid", parameters);
+                        updateRepliesBy(mid);
+                        return true;
+                    }
                 } else {
-                    result = getNamedParameterJdbcTemplate().update(
-                            "DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid",
+                    // only archive author replies
+                    return getNamedParameterJdbcTemplate().update(
+                            "UPDATE replies SET user_id=:archive_uid WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid",
                             parameters) > 0;
                 }
-                if (result) {
-                    getNamedParameterJdbcTemplate()
-                            .update("UPDATE messages SET replies=replies-1 WHERE message_id=:mid", parameters);
-                    updateRepliesBy(mid);
-                    return true;
-                }
-            } else {
-                // only archive author replies
-                return getNamedParameterJdbcTemplate().update(
-                        "UPDATE replies SET user_id=:archive_uid WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid",
-                        parameters) > 0;
             }
         }
         return false;
@@ -1030,15 +1000,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     public List<ResponseReply> getLastReplies(int hours) {
         var datetime = Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC);
         return getNamedParameterJdbcTemplate().query("""
-                        SELECT users2.nick,replies.message_id,replies.reply_id,
-                        users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html
-                        FROM ((replies INNER JOIN users ON replies.user_id=users.id)
-                        INNER JOIN messages ON replies.message_id=messages.message_id)
-                        INNER JOIN users AS users2 ON messages.user_id=users2.id
-                        WHERE replies.ts>:datetime AND messages.privacy>0
-                        """,
+                SELECT users2.nick,replies.message_id,replies.reply_id,
+                users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html
+                FROM ((replies INNER JOIN users ON replies.user_id=users.id)
+                INNER JOIN messages ON replies.message_id=messages.message_id)
+                INNER JOIN users AS users2 ON messages.user_id=users2.id
+                WHERE replies.ts>:datetime AND messages.privacy>0
+                """,
                 new MapSqlParameterSource()
-                        .addValue("datetime", datetime, Types.TIMESTAMP_WITH_TIMEZONE), (rs, rowNum) -> {
+                        .addValue("datetime", datetime, Types.TIMESTAMP_WITH_TIMEZONE),
+                (rs, rowNum) -> {
                     ResponseReply reply = new ResponseReply();
                     reply.setMuname(rs.getString(1));
                     reply.setMid(rs.getInt(2));
diff --git a/src/main/java/com/juick/service/SubscriptionServiceImpl.java b/src/main/java/com/juick/service/SubscriptionServiceImpl.java
index d6e97010..bf7d1045 100644
--- a/src/main/java/com/juick/service/SubscriptionServiceImpl.java
+++ b/src/main/java/com/juick/service/SubscriptionServiceImpl.java
@@ -53,7 +53,7 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip
     @Override
     public List<User> getSubscribedUsers(final int uid, final Message msg) {
         int mid = msg.getMid();
-        User author = messagesService.getMessageAuthor(mid);
+        User author = msg.getUser();
 
         List<User> subscribers = userService.getUserReaders(uid);
         List<User> mentionedUsers = userService.getUsersByName(MessageUtils.getMentions(msg).stream()
diff --git a/src/main/java/com/juick/www/api/Post.java b/src/main/java/com/juick/www/api/Post.java
index 97311c21..4f1c2cad 100644
--- a/src/main/java/com/juick/www/api/Post.java
+++ b/src/main/java/com/juick/www/api/Post.java
@@ -224,7 +224,7 @@ public class Post {
                                        @RequestParam Integer mid,
                                        @RequestParam(required = false, defaultValue = "0") Integer rid,
                                        @RequestParam String body) {
-        User author = rid == 0 ? messagesService.getMessageAuthor(mid) : messagesService.getReply(mid, rid).getUser();
+        User author = rid == 0 ? messagesService.getMessage(mid).orElseThrow(HttpBadRequestException::new).getUser() : messagesService.getReply(mid, rid).getUser();
         if (visitor.equals(author)) {
             if (messagesService.updateMessage(mid, rid, body, false)) {
                 Message result = rid == 0 ?
diff --git a/src/main/java/com/juick/www/controllers/Site.java b/src/main/java/com/juick/www/controllers/Site.java
index 06513fcc..bfe7d654 100644
--- a/src/main/java/com/juick/www/controllers/Site.java
+++ b/src/main/java/com/juick/www/controllers/Site.java
@@ -569,8 +569,6 @@ public class Site {
         model.addAttribute("headers", headers);
         model.addAttribute("visitorSubscribed", messagesService.isSubscribed(visitor.getUid(), msg.getMid()));
         model.addAttribute("visitorInBL", userService.isInBL(msg.getUser().getUid(), visitor.getUid()));
-        model.addAttribute("recomm", messagesService.getMessagesRecommendations(Collections.singletonList(msg.getMid()))
-                .stream().map(Pair::getRight).toList());
         List<Integer> blUIDs = new ArrayList<>();
         for (Message reply : replies) {
             if (reply.getUser().getUid() != msg.getUser().getUid() && !blUIDs.contains(reply.getUser().getUid())) {
diff --git a/src/main/java/com/juick/www/filters/AnythingFilter.java b/src/main/java/com/juick/www/filters/AnythingFilter.java
index 2ec82801..ffd11b29 100644
--- a/src/main/java/com/juick/www/filters/AnythingFilter.java
+++ b/src/main/java/com/juick/www/filters/AnythingFilter.java
@@ -45,8 +45,8 @@ public class AnythingFilter extends OncePerRequestFilter {
 
     @Override
     public void doFilterInternal(HttpServletRequest servletRequest,
-                                 HttpServletResponse servletResponse,
-                                 FilterChain filterChain) throws IOException, ServletException {
+            HttpServletResponse servletResponse,
+            FilterChain filterChain) throws IOException, ServletException {
         String upgrade = servletRequest.getHeader("Connection");
         if (upgrade != null && upgrade.equals("Upgrade")) {
             filterChain.doFilter(servletRequest, servletResponse);
@@ -57,21 +57,23 @@ public class AnythingFilter extends OncePerRequestFilter {
         int before = NumberUtils.toInt(components.getQueryParams().getFirst("before"), 0);
         if (before == 0) {
             boolean isPostNumber = WebUtils.isPostNumber(anything);
-            int messageId = isPostNumber ?
-                    NumberUtils.toInt(anything) : 0;
+            int messageId = isPostNumber ? NumberUtils.toInt(anything) : 0;
 
             if (isPostNumber && anything.equals(Integer.toString(messageId))) {
                 if (messageId > 0) {
-                    User author = messagesService.getMessageAuthor(messageId);
+                    var message = messagesService.getMessage(messageId);
+                    if (message.isPresent()) {
+                        User author = message.get().getUser();
 
-                    if (author != null) {
-                        servletResponse.setStatus(HttpServletResponse.SC_MOVED_PERMANENTLY);
-                        servletResponse.setHeader("Location", "/" + author.getName() + "/" + anything);
-                        return;
+                        if (author != null) {
+                            servletResponse.setStatus(HttpServletResponse.SC_MOVED_PERMANENTLY);
+                            servletResponse.setHeader("Location", "/" + author.getName() + "/" + anything);
+                            return;
+                        }
                     }
                 }
             }
-             ;
+            ;
             if (userService.getUsernames().stream().anyMatch(name -> name.equals(anything))) {
                 servletResponse.sendRedirect("/" + anything + "/");
             } else {
diff --git a/src/main/resources/templates/views/thread.html b/src/main/resources/templates/views/thread.html
index 7aecaea4..759c47e1 100644
--- a/src/main/resources/templates/views/thread.html
+++ b/src/main/resources/templates/views/thread.html
@@ -102,18 +102,15 @@
                 </div>
             </form>
             {% endif %}
-            {% if recomm is not empty %}
+            {% if msg.recommendations.size() > 0 %}
             <div class="msg-recomms">{{ i18n("messages","message.recommendedBy") }}
-                {% for rec in recomm %}
+                {% for rec in msg.recommendations %}
                 {% if rec.uri.toString() is empty %}
                     <a href="/{{ rec.name }}/">@{{ rec.name }}</a>{% if loop.index < (loop.length - 1) %}, {% endif %}
                 {% else %}
                     <a href="{{ rec.uri }}" data-user-uri="1">@{{ rec.name }}</a>{% if loop.index < (loop.length - 1) %}, {% endif %}
                 {% endif %}
                 {% endfor %}
-                {% if msg.recommendations.size() > recomm.size() %}
-                    &nbsp;{{ i18n("messages","message.recommendedOthers", msg.recommendations.size() - recomm.size()) }}
-                {% endif %}
             </div>
             {% endif %}
         </div>
diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java
index bb70e875..b7b78fa9 100644
--- a/src/test/java/com/juick/server/tests/ServerTests.java
+++ b/src/test/java/com/juick/server/tests/ServerTests.java
@@ -389,7 +389,7 @@ public class ServerTests {
         assertEquals("yo", msg.getText());
         User me = msg.getUser();
         assertEquals("mmmme", me.getName());
-        assertEquals("mmmme", messagesService.getMessageAuthor(mid).getName());
+        assertEquals("mmmme", msg.getUser().getName());
         int tagID = tagService.createTag("weather");
         Tag tag = tagService.getTag(tagID);
         Set<Tag> tagList = Set.of(tag);
@@ -1439,7 +1439,8 @@ public class ServerTests {
         jdbcTemplate.update("UPDATE messages_txt SET updated_at='1990-05-05 00:00:00' WHERE message_id=?",
                 original.getMid());
         assertThat(messagesService.deleteMessage(ugnich.getUid(), original.getMid()), is(true));
-        assertThat(messagesService.getMessageAuthor(original.getMid()), is(archiveUser));
+
+        assertThat(messagesService.getMessage(original.getMid()).orElseThrow().getUser(), is(archiveUser));
         jdbcTemplate.update("UPDATE messages_txt SET updated_at=? WHERE message_id=?",
                 Timestamp.from(Instant.now()),
                 original.getMid());