aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2018-12-20 00:36:16 +0300
committerGravatar Vitaly Takmazov2018-12-20 00:36:16 +0300
commitecf4b707f74298618d019bb8108c25f8fc5ad4c6 (patch)
tree22c3052aa8a83bef06a30f31573adbd3af21befd /src/main/java/com/juick
parentb19902c0f390d9d0122db33d5161a2263a2de26f (diff)
getMyFeed optimization
Diffstat (limited to 'src/main/java/com/juick')
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java40
1 files changed, 18 insertions, 22 deletions
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<Integer> 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);