aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/service
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2019-04-26 17:29:25 +0300
committerGravatar Vitaly Takmazov2019-04-26 17:29:25 +0300
commit1b5d8ba596869e7321dd7a0f4757d08306a4d9fd (patch)
tree5c2ec24ecd1f15fa01618d2938d55e3df6b01f76 /src/main/java/com/juick/service
parenta34350b62784d4332243ba40ffe928afd91f67d3 (diff)
Minimize SQL queries for banned users
Diffstat (limited to 'src/main/java/com/juick/service')
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java68
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",