From 1b5d8ba596869e7321dd7a0f4757d08306a4d9fd Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 26 Apr 2019 17:29:25 +0300 Subject: Minimize SQL queries for banned users --- .../com/juick/service/MessagesServiceImpl.java | 68 +++++++++++----------- 1 file changed, 35 insertions(+), 33 deletions(-) (limited to 'src/main/java/com/juick/service') 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", -- cgit v1.2.3