Minimize SQL queries for banned users

6 files changed, 43 insertions, 43 deletions

diff --git a/src/main/java/com/juick/server/CommandsManager.java b/src/main/java/com/juick/server/CommandsManager.java
index fdea0d83..634deb35 100644
--- a/src/main/java/com/juick/server/CommandsManager.java
+++ b/src/main/java/com/juick/server/CommandsManager.java
@@ -410,7 +410,7 @@ public class CommandsManager {
     public CommandResult commandUser(User user, URI attachment, String... arguments) {
         User blogUser = userService.getUserByName(arguments[0]);
         int page = arguments[1].length();
-        if (!blogUser.isAnonymous()) {
+        if (!blogUser.isAnonymous() && !blogUser.isBanned()) {
             List<Integer> mids = messagesService.getUserBlog(blogUser.getUid(), 0, 0);
             return CommandResult.fromString(String.format("Last messages from @%s:\n%s", arguments[0],
                     printMessages(user, mids, false)));
diff --git a/src/main/java/com/juick/server/api/Messages.java b/src/main/java/com/juick/server/api/Messages.java
index 60e05d6d..402d2162 100644
--- a/src/main/java/com/juick/server/api/Messages.java
+++ b/src/main/java/com/juick/server/api/Messages.java
@@ -106,7 +106,7 @@ public class Messages {
         List<Integer> mids;
         if (!StringUtils.isEmpty(uname)) {
             User user = userService.getUserByName(uname);
-            if (!user.isAnonymous()) {
+            if (!user.isAnonymous() && !user.isBanned()) {
                 if (!StringUtils.isEmpty(media)) {
                     mids = messagesService.getUserPhotos(user.getUid(), 0, before);
                 } else if (!StringUtils.isEmpty(tag)) {
@@ -180,9 +180,6 @@ public class Messages {
             if (!messagesService.canViewThread(mid, visitor.getUid())) {
                 return FORBIDDEN;
             } else {
-                if (userService.getUserByName(msg.getUser().getName()).isBanned()) {
-                    throw new HttpNotFoundException();
-                }
                 msg.getUser().setAvatar(webApp.getAvatarUrl(msg.getUser()));
                 msg.setRecommendations(new HashSet<>(messagesService.getMessagesRecommendations(
                         Collections.singletonList(msg.getMid()))
diff --git a/src/main/java/com/juick/server/api/activity/Profile.java b/src/main/java/com/juick/server/api/activity/Profile.java
index 4e375a54..701b1949 100644
--- a/src/main/java/com/juick/server/api/activity/Profile.java
+++ b/src/main/java/com/juick/server/api/activity/Profile.java
@@ -136,7 +136,7 @@ public class Profile {
                                                @RequestParam(required = false, defaultValue = "0") int before) {
         User visitor = UserUtils.getCurrentUser();
         User user = userService.getUserByName(userName);
-        if (!user.isAnonymous()) {
+        if (!user.isAnonymous() && !user.isBanned()) {
             UriComponentsBuilder uri = UriComponentsBuilder.fromUriString(baseUri);
             String personUri = uri.path(String.format("/u/%s", userName)).toUriString();
             List<Integer> mids = messagesService.getUserBlog(user.getUid(), 0, before);
diff --git a/src/main/java/com/juick/server/api/rss/Feeds.java b/src/main/java/com/juick/server/api/rss/Feeds.java
index c72f3a5e..47d9fd5e 100644
--- a/src/main/java/com/juick/server/api/rss/Feeds.java
+++ b/src/main/java/com/juick/server/api/rss/Feeds.java
@@ -19,6 +19,7 @@ package com.juick.server.api.rss;
 
 import com.juick.User;
 import com.juick.server.util.HttpBadRequestException;
+import com.juick.server.util.HttpNotFoundException;
 import com.juick.server.util.UserUtils;
 import com.juick.service.MessagesService;
 import com.juick.service.UserService;
@@ -46,7 +47,7 @@ public class Feeds {
     @RequestMapping(value = "/rss/{userName}/blog", method = RequestMethod.GET, produces = "text/xml; charset=utf-8")
     public ModelAndView getBlog(@PathVariable String userName) {
         User user = userService.getUserByName(userName);
-        if (!user.isAnonymous()) {
+        if (!user.isAnonymous() && !user.isBanned()) {
             List<Integer> mids = messagesService.getUserBlog(user.getUid(), 0, 0);
             ModelAndView modelAndView = new ModelAndView();
             modelAndView.setViewName("messagesView");
@@ -54,7 +55,7 @@ public class Feeds {
             modelAndView.addObject("messages", messagesService.getMessages(UserUtils.getCurrentUser(), mids));
             return modelAndView;
         }
-        throw new HttpBadRequestException();
+        throw new HttpNotFoundException();
     }
 
     @RequestMapping(value = "/rss/", method = RequestMethod.GET, produces = "text/xml; charset=utf-8")
diff --git a/src/main/java/com/juick/server/www/controllers/MessagesWWW.java b/src/main/java/com/juick/server/www/controllers/MessagesWWW.java
index 41c95dcb..e1c1bed8 100644
--- a/src/main/java/com/juick/server/www/controllers/MessagesWWW.java
+++ b/src/main/java/com/juick/server/www/controllers/MessagesWWW.java
@@ -224,7 +224,7 @@ public class MessagesWWW {
             if (paramTag == null) {
                 throw new HttpNotFoundException();
             } else if (!paramTag.getName().equals(paramTagStr)) {
-                String url = user.getName() + "/?tag=" + URLEncoder.encode(paramTag.getName(), CharEncoding.UTF_8);
+                String url = user.getName() + "/?tag=" + URLEncoder.encode(paramTag.getName(), StandardCharsets.UTF_8);
                 return "redirect:/" + url;
             }
         }
@@ -510,7 +510,7 @@ public class MessagesWWW {
         visitor.setAvatar(webApp.getAvatarWebPath(visitor));
         Optional<com.juick.Message> message = messagesService.getMessage(mid);
 
-        if (!message.isPresent() || message.get().getUser().isBanned()) {
+        if (message.isEmpty()) {
             throw new HttpNotFoundException();
         }
 
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",