Minimize SQL queries for banned users

1 files changed, 35 insertions, 33 deletions

diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 5e36093e..b1cb03d9 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -380,7 +380,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + "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 = ? "
+                        + "WHERE messages.message_id = ? AND users.banned = 0 "
                         + "GROUP BY mid, rid, replyto, uid, nick, banned, messages.ts, readonly, "
                         + "privacy, replies, attach, repliesby, q, updated_at, reply_user_uri, to_uri, reply_uri, html",
                 new MessageMapper(),
@@ -736,16 +736,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 .addValue("privacy", privacy)
                 .addValue("before", before);
 
-        ;
-        if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
-            throw new HttpNotFoundException();
-        }
-
         return getNamedParameterJdbcTemplate().queryForList(
-                "SELECT message_id FROM messages WHERE user_id = :uid" +
+                "SELECT message_id FROM messages INNER JOIN users" +
+                        " ON messages.user_id = users.id" +
+                        " WHERE user_id = :uid" +
                         (before > 0 ?
                                 " AND message_id < :before" : StringUtils.EMPTY) +
-                        " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20",
+                        " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20",
                 sqlParameterSource,
                 Integer.class);
     }
@@ -759,16 +756,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 .addValue("privacy", privacy)
                 .addValue("before", before);
 
-        if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
-            throw new HttpNotFoundException();
-        }
-
         return getNamedParameterJdbcTemplate().queryForList(
-                "SELECT messages.message_id FROM messages_tags INNER JOIN messages " +
-                        " ON messages.message_id = messages_tags.message_id WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid " +
+                "SELECT messages.message_id FROM messages_tags INNER JOIN messages" +
+                        " ON messages.message_id = messages_tags.message_id" +
+                        " INNER JOIN users" +
+                        " ON messages.user_id=users.id" +
+                        " WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid" +
                         (before > 0 ?
                                 " AND messages.message_id < :before " : StringUtils.EMPTY) +
-                        " AND messages.privacy >= :privacy ORDER BY messages.message_id DESC LIMIT 20",
+                        " AND messages.privacy >= :privacy AND users.banned = 0" +
+                        " ORDER BY messages.message_id DESC LIMIT 20",
                 sqlParameterSource,
                 Integer.class);
     }
@@ -781,16 +778,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 .addValue("privacy", privacy)
                 .addValue("daysback", daysback);
 
-        if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
-            throw new HttpNotFoundException();
-        }
-
         return getNamedParameterJdbcTemplate().queryForList(
-                "SELECT message_id FROM messages WHERE user_id = :uid" +
+                "SELECT message_id FROM messages" +
+                        " INNER JOIN users" +
+                        " ON messages.user_id = users.id" +
+                        " WHERE user_id = :uid" +
                         (daysback > 0 ?
                                 " AND ts >= date(NOW() - INTERVAL :daysback day)" +
-                                " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" : StringUtils.EMPTY) +
-                        " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20",
+                                        " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" : StringUtils.EMPTY) +
+                        " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20",
                 sqlParameterSource,
                 Integer.class);
     }
@@ -803,20 +799,21 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 .addValue("privacy", privacy)
                 .addValue("before", before);
 
-        if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
-            throw new HttpNotFoundException();
-        }
-
         return getNamedParameterJdbcTemplate().queryForList(
                 "SELECT message_id FROM " +
-                    "(SELECT message_id FROM favorites " +
-                            " WHERE user_id = :uid " +
-                            (before > 0 ?
+                        "(SELECT message_id FROM favorites " +
+                        " INNER JOIN users" +
+                        " ON messages.user_id = users.id" +
+                        " WHERE user_id = :uid AND users.banned = 0" +
+                        (before > 0 ?
                                     " AND message_id < :before " : StringUtils.EMPTY) +
                             " ORDER BY message_id DESC LIMIT 20) as r" +
                     " UNION ALL " +
                 "SELECT message_id FROM " +
-                    "(SELECT message_id FROM messages WHERE user_id = :uid" +
+                    "(SELECT message_id FROM messages" +
+                        " INNER JOIN users" +
+                        " ON messages.user_id = users.id" +
+                        " WHERE user_id = :uid AND users.banned = 0" +
                             (before > 0 ?
                                     " AND message_id < :before" : StringUtils.EMPTY) +
                             " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20) as m " +
@@ -833,8 +830,10 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 .addValue("before", before);
 
         return getNamedParameterJdbcTemplate().queryForList(
-                "SELECT message_id FROM favorites " +
-                        " WHERE user_id = :uid " +
+                "SELECT message_id FROM favorites" +
+                        " INNER JOIN users" +
+                        " ON favorites.user_id = users.id" +
+                        " WHERE user_id = :uid AND users.banned = 0 " +
                         (before > 0 ?
                                 " AND message_id < :before " : StringUtils.EMPTY) +
                         " ORDER BY message_id DESC LIMIT 20",
@@ -851,7 +850,10 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                 .addValue("before", before);
 
         return getNamedParameterJdbcTemplate().queryForList(
-                "SELECT message_id FROM messages WHERE user_id = :uid " +
+                "SELECT message_id FROM messages" +
+                        " INNER JOIN users" +
+                        " ON messages.user_id = users.id" +
+                        " WHERE user_id = :uid and users.banned = 0" +
                         (before > 0 ?
                                 " AND message_id < :before " : StringUtils.EMPTY) +
                         " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20",