aboutsummaryrefslogtreecommitdiff
path: root/juick-server-jdbc/src/main
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2017-09-25 17:48:49 +0300
committerGravatar Vitaly Takmazov2017-09-25 17:48:49 +0300
commitbabfd641b99101c077d3a7a0d1e251c9aacc9301 (patch)
tree3a1bf0e3184b7d6de7f55337a1161db8f33caf4e /juick-server-jdbc/src/main
parent83af39fd6c3f89670130a8f6a63898e35e172cbf (diff)
jdbc: fix notifications query
Diffstat (limited to 'juick-server-jdbc/src/main')
-rw-r--r--juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java22
1 files changed, 9 insertions, 13 deletions
diff --git a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java
index 430039ea..a2a6c164 100644
--- a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -844,20 +844,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
public List<com.juick.Message> getNotifications(User user, LocalDateTime before) {
- return getNamedParameterJdbcTemplate().query("SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " +
- "replies.user_id, users.nick, users.banned, " +
- "TIMESTAMPDIFF(MINUTE, replies.ts, NOW()), replies.ts, " +
- "0 as readonly, 0 as privacy, 0 as replies, " +
- "replies.attach, 0 as place_id, 0 as lat, " +
- "0 as lon, 0 as likes, 0 as hidden, " +
- "NULL as tags, NULL as repliesby, replies.txt, " +
- "IFNULL(qw.txt, t.txt) as q FROM replies INNER JOIN users " +
- "ON replies.user_id = users.id " +
- "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id " +
- "LEFT JOIN messages_txt t on replies.message_id = t.message_id " +
- "WHERE EXISTS (SELECT 1 FROM subscr_messages WHERE suser_id=:uid " +
- "AND replies.message_id=message_id) " +
+ return getNamedParameterJdbcTemplate().query("SELECT n.message_id as mid, n.reply_id, n.replyto, " +
+ "n.user_id, users.nick, users.banned, 0 as ago, n.ts, 0 as readonly, 0 as privacy, " +
+ "0 as replies, n.attach, 0 as place_id, 0 as lat, 0 as lon, 0 as likes, 0 as hidden, " +
+ "NULL as tags, NULL as repliesby, n.txt, IFNULL(qw.txt, t.txt) as q " +
+ "FROM (SELECT * FROM replies WHERE EXISTS (SELECT 1 FROM subscr_messages WHERE suser_id=3694 " +
+ "AND replies.user_id!=3694 AND replies.message_id=message_id " +
(before != null ? "AND replies.ts < :before " : StringUtils.EMPTY) +
+ ")) as n LEFT JOIN users " +
+ "ON n.user_id = users.id LEFT JOIN replies qw ON n.message_id = qw.message_id " +
+ "AND n.replyto = qw.reply_id LEFT JOIN messages_txt t on n.message_id = t.message_id " +
"ORDER BY ts DESC LIMIT 20", new MapSqlParameterSource()
.addValue("uid", user.getUid())
.addValue("before", before),