From ecf4b707f74298618d019bb8108c25f8fc5ad4c6 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Thu, 20 Dec 2018 00:36:16 +0300 Subject: getMyFeed optimization --- .../com/juick/service/MessagesServiceImpl.java | 40 ++++++++++------------ .../java/com/juick/server/tests/ServerTests.java | 6 ++-- 2 files changed, 21 insertions(+), 25 deletions(-) (limited to 'src') diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 4e5dca81..9d9a809d 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -577,29 +577,25 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("before", before); List mids = getNamedParameterJdbcTemplate().queryForList( - "(SELECT message_id FROM messages " + - " INNER JOIN subscr_users ON (subscr_users.suser_id = :uid AND subscr_users.user_id = messages.user_id) " + - " WHERE " + - (before > 0 ? - " message_id < :before AND " : StringUtils.EMPTY) + - " (privacy >= 0 OR (privacy >= -2 AND privacy <= -1" + - " AND EXISTS (SELECT 1 FROM wl_users w WHERE w.wl_user_id = :uid and w.user_id = messages.user_id))) " + - " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + - "(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :uid = bt.user_id))" + - " UNION " + - " (SELECT message_id FROM messages WHERE user_id=:uid " + - (before > 0 ? - " AND message_id < :before " : StringUtils.EMPTY) + + "SELECT message_id FROM messages WHERE " + + "(user_id=:uid OR " + + "(EXISTS (SELECT 1 FROM subscr_users WHERE subscr_users.suser_id = :uid " + + "AND subscr_users.user_id = messages.user_id) " + + "AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + + "(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) AND :uid = bt.user_id) " + + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 " + + "AND EXISTS (SELECT 1 FROM wl_users w WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) " + (recommended ? - ") UNION " + - " (SELECT f.message_id as message_id FROM favorites f INNER JOIN messages ON " + - "f.message_id=messages.message_id WHERE " + - "EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)" + - (before > 0 ? - " AND f.message_id < :before " : StringUtils.EMPTY) : StringUtils.EMPTY) + - " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)" + - " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + - "(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :uid = bt.user_id)) " + + "OR (EXISTS (SELECT 1 FROM favorites WHERE favorites.message_id=messages.message_id " + + "AND favorites.user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=:uid)) " + + "AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN (SELECT tag_id FROM messages_tags " + + "WHERE message_id = messages.message_id) and :uid = bt.user_id) " + + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 " + + "AND EXISTS (SELECT 1 FROM wl_users w " + + "WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) " + + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)" : StringUtils.EMPTY) + + ") " + + (before > 0 ? "AND message_id < :before " : StringUtils.EMPTY) + "ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java index 5902220f..32afd4f9 100644 --- a/src/test/java/com/juick/server/tests/ServerTests.java +++ b/src/test/java/com/juick/server/tests/ServerTests.java @@ -1041,8 +1041,8 @@ public class ServerTests { tagService.updateTags(newMid, Collections.singletonList(banned)); assertThat(messagesService.getMessage(newMid).get().getTags().size(), is(0)); privacyQueriesService.blacklistUser(freefd, userService.getUserByUID(newUid).orElse(AnonymousUser.INSTANCE)); - assertTrue(messagesService.getMessages(AnonymousUser.INSTANCE, messagesService.getMyFeed(freefd.getUid(), 0, true)) - .stream().noneMatch(m -> m.getMid() == newMid)); + assertTrue(messagesService.getMyFeed(freefd.getUid(), 0, true) + .stream().noneMatch(m -> m == newMid)); } @Test public void tagsShouldBeDeserializedFromXml() throws JAXBException { @@ -1093,7 +1093,7 @@ public class ServerTests { DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance(); DocumentBuilder db = dbf.newDocumentBuilder(); - Document doc = db.parse(new ByteArrayInputStream(sw.toString().getBytes(CharEncoding.UTF_8))); + Document doc = db.parse(new ByteArrayInputStream(sw.toString().getBytes(StandardCharsets.UTF_8))); Node juickNode = doc.getDocumentElement(); NamedNodeMap attrs = juickNode.getAttributes(); assertEquals("date should be in ts field", DateFormattersHolder.getMessageFormatterInstance().format(currentDate), -- cgit v1.2.3