db refactoring: merge getMessages with isSubscribed query

2 files changed, 42 insertions, 38 deletions

diff --git a/src/main/java/com/juick/service/MessagesService.java b/src/main/java/com/juick/service/MessagesService.java
index 88f79c14..6881c924 100644
--- a/src/main/java/com/juick/service/MessagesService.java
+++ b/src/main/java/com/juick/service/MessagesService.java
@@ -67,11 +67,10 @@ public interface MessagesService {
     @CacheEvict(value = { "discover", "discussions", "messages", "replies" }, allEntries = true)
     void setReadOnly(int mid, boolean readonly);
 
-    boolean isSubscribed(int uid, int mid);
-
     int getMessagePrivacy(int mid);
 
     Optional<Message> getMessage(int mid);
+    Optional<Message> getMessage(int uid, int mid);
 
     Message getReply(int mid, int rid);
 
@@ -121,8 +120,8 @@ public interface MessagesService {
 
     List<Integer> getUserSearch(User visitor, int UID, String search, int privacy, int page);
 
-    @Cacheable(value = "messages", key="{ #visitor.uid, #mids.hashCode() }")
-    List<Message> getMessages(User visitor, List<Integer> mids);
+    @Cacheable(value = "messages", key="{ #uid, #mids.hashCode() }")
+    List<Message> getMessages(int uid, List<Integer> mids);
     @Cacheable(value = "replies", key="{ #user.uid, #mid }")
     List<Message> getReplies(User user, int mid);
 
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 9fe1873a..790e695e 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -114,6 +114,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
             msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(24)).orElse(StringUtils.EMPTY)));
             msg.setHtml(rs.getBoolean(25));
             msg.setUnread(rs.getBoolean(26));
+            msg.setSubscribed(rs.getBoolean("subscribed"));
             if (StringUtils.isNotEmpty(msg.getAttachmentType())) {
                 try {
                     storageService.setAttachmentMetadata(baseImagesUrl, msg);
@@ -395,26 +396,19 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
     @Transactional(readOnly = true)
     @Override
-    public boolean isSubscribed(final int uid, final int mid) {
-        List<Integer> list = getJdbcTemplate().queryForList(
-                "SELECT 1 FROM subscr_messages WHERE suser_id = ? AND message_id = ?", Integer.class, uid, mid);
-
-        return !list.isEmpty() && list.get(0) == 1;
-    }
-
-    @Transactional(readOnly = true)
-    @Override
     public int getMessagePrivacy(final int mid) {
         List<Integer> list = getJdbcTemplate().queryForList("SELECT privacy FROM messages WHERE message_id = ?",
                 Integer.class, mid);
 
         return list.isEmpty() ? -4 : list.get(0);
     }
-
+    public Optional<Message> getMessage(int mid) {
+        return getMessage(0, mid);
+    }
     @Transactional(readOnly = true)
     @Override
-    public Optional<Message> getMessage(final int mid) {
-        var messages = getMessages(AnonymousUser.INSTANCE, List.of(mid));
+    public Optional<Message> getMessage(int uid, final int mid) {
+        var messages = getMessages(uid, List.of(mid));
         if (messages.size() == 1) {
             var message = messages.get(0);
             if (!message.getUser().isBanned()) {
@@ -781,9 +775,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
     @Transactional(readOnly = true)
     @Override
-    public List<Message> getMessages(final User visitor, final List<Integer> mids) {
+    public List<Message> getMessages(int uid, 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)
@@ -791,13 +784,33 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                     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
+                    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,
+                    (SELECT EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid)) subscribed
                     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
@@ -810,31 +823,22 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                     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());
-
+                    new MapSqlParameterSource("ids", mids).addValue("uid", uid), new MessageMapper());
             Map<Integer, Set<Reaction>> likes = updateReactionsFor(mids);
-
             msgs.forEach(i -> i.setReactions(likes.get(i.getMid())));
-
             msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid())));
-
             msgs.forEach(i -> i.setEntities(MessageUtils.getEntities(i)));
-
             List<Pair<Integer, User>> allRecommendations = getMessagesRecommendations(mids);
-
             msgs.forEach(m -> {
                 m.setRecommendations(new HashSet<>(allRecommendations.stream()
                         .filter(r -> r.getLeft().equals(m.getMid())).map(Pair::getRight).toList()));
                 m.getRecommendations().forEach(r -> r.setAvatar(webApp.getAvatarUrl(r)));
             });
-
             return msgs;
         }
         return Collections.emptyList();
     }
-
     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
@@ -875,7 +879,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 + "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 "
+                + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread, "
+                + "0 as subscribed "
                 + "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 "
@@ -912,7 +917,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     public boolean deleteMessage(final int uid, final int mid) {
         Instant now = Instant.now();
         Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS);
-        Optional<Message> message = getMessage(mid);
+        Optional<Message> message = getMessage(uid, mid);
         if (message.isPresent()) {
             Instant ts = message.get().getUpdatedAt();
             SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid)
@@ -945,7 +950,7 @@ 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);
-        var message = getMessage(mid);
+        var message = getMessage(uid, mid);
         if (message.isPresent()) {
             Message reply = getReply(mid, rid);
             if (reply != null) {