sort discussions by last replay date

2 files changed, 28 insertions, 16 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 de8b256e..d7067901 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
@@ -97,6 +97,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
             msg.setRepliesBy(rs.getString(19));
             msg.setText(rs.getString(20));
             msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(21)));
+            msg.setUpdated(rs.getTimestamp(22).toInstant());
             try {
                 imagesService.setAttachmentMetadata(imgDir, baseImagesUrl, msg);
             } catch (Exception e) {
@@ -305,7 +306,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         + "messages.readonly, messages.privacy, messages.replies,"
                         + "messages.attach, messages.place_id, messages.lat,"
                         + "messages.lon, COUNT(favorites.user_id) as likes, messages.hidden,"
-                        + "txt.tags, txt.repliesby, txt.txt, '' as q FROM messages "
+                        + "txt.tags, txt.repliesby, txt.txt, '' as q, messages.updated FROM messages "
                         + "INNER JOIN users ON messages.user_id = users.id "
                         + "INNER JOIN messages_txt AS txt "
                         + "ON messages.message_id = txt.message_id "
@@ -511,18 +512,20 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
     @Transactional(readOnly = true)
     @Override
-    public List<Integer> getDiscussions(final int uid, final int before) {
+    public List<Integer> getDiscussions(final int uid, final Long to) {
         SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
                 .addValue("uid", uid)
-                .addValue("before", before);
+                .addValue("to", new Timestamp(to));
 
-        return getNamedParameterJdbcTemplate().queryForList(
-                "SELECT message_id FROM subscr_messages WHERE suser_id = :uid" +
-                        (before > 0 ?
-                                " AND message_id < :before " : " ") +
-                        "ORDER BY message_id DESC LIMIT 20",
+        return getNamedParameterJdbcTemplate().query(
+                "SELECT messages.message_id, messages.updated FROM subscr_messages " +
+                        "INNER JOIN messages ON messages.message_id=subscr_messages.message_id " +
+                        "WHERE suser_id = :uid " +
+                        (to != 0 ?
+                                "AND updated < :to " : StringUtils.EMPTY) +
+                        "ORDER BY updated DESC, message_id DESC LIMIT 20",
                 sqlParameterSource,
-                Integer.class);
+                (rs, rowNum) -> rs.getInt(1));
     }
 
     @Transactional(readOnly = true)
@@ -722,9 +725,9 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
 
     @Transactional(readOnly = true)
     @Override
-    public List<com.juick.Message> getMessages(final Collection<Integer> mids) {
+    public List<com.juick.Message> getMessages(final List<Integer> mids) {
         if (CollectionUtils.isNotEmpty(mids)) {
-            return getNamedParameterJdbcTemplate().query(
+            List<com.juick.Message> msgs = getNamedParameterJdbcTemplate().query(
                     "SELECT messages.message_id, 0 as rid, 0 as replyto, "
                             + "messages.user_id,users.nick, 0 as banned, "
                             + "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
@@ -732,15 +735,18 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                             + "messages.readonly,messages.privacy,messages.replies,"
                             + "messages.attach,messages.place_id,messages.lat,"
                             + "messages.lon,COUNT(favorites.user_id) AS likes,messages.hidden,"
-                            + "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q "
+                            + "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q, "
+                            + "messages.updated "
                             + "FROM (messages INNER JOIN messages_txt "
                             + "ON messages.message_id=messages_txt.message_id) "
                             + "INNER JOIN users ON messages.user_id=users.id "
                             + "LEFT JOIN favorites "
                             + "ON messages.message_id = favorites.message_id "
-                            + "WHERE messages.message_id IN (:ids) GROUP BY messages.message_id ORDER BY messages.message_id DESC",
+                            + "WHERE messages.message_id IN (:ids) GROUP BY messages.message_id",
                     new MapSqlParameterSource("ids", mids),
                     new MessageMapper());
+            msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid())));
+            return msgs;
         }
         return Collections.emptyList();
     }
@@ -756,7 +762,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                         "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 " +
+                        "IFNULL(qw.txt, t.txt) as q, " +
+                        "NOW() " +
                         "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 " +
@@ -880,10 +887,11 @@ 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 n.message_id as mid, n.reply_id, n.replyto, " +
+        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 " +
+                        "NULL as tags, NULL as repliesby, n.txt, IFNULL(qw.txt, t.txt) as q, NOW() " +
                         "FROM (SELECT * FROM replies WHERE EXISTS (SELECT 1 FROM subscr_messages WHERE suser_id=:uid " +
                         "AND replies.user_id!=:uid AND replies.message_id=message_id " +
                         (before != null ? "AND replies.ts < :before " : StringUtils.EMPTY) +
diff --git a/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java b/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java
index bdb9cdcb..4ebfe056 100644
--- a/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java
+++ b/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java
@@ -35,6 +35,8 @@ import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
 import javax.inject.Inject;
 import java.sql.Timestamp;
 import java.time.Instant;
+import java.time.LocalDateTime;
+import java.time.ZoneId;
 import java.util.ArrayList;
 import java.util.Collections;
 import java.util.List;
@@ -164,6 +166,8 @@ public class MessageServiceTest extends AbstractJUnit4SpringContextTests {
         assertEquals(-1, userService.checkPassword(ugnich.getName(), "xy"));
         subscriptionService.subscribeMessage(msg.getMid(), ugnich.getUid());
         assertEquals(1, subscriptionService.getUsersSubscribedToComments(msg.getMid(), user.getUid()).size());
+        assertThat(messagesService.getDiscussions(ugnich.getUid(), Instant.now().toEpochMilli()).get(0),
+                                equalTo(msg.getMid()));
         messagesService.deleteMessage(user_id, mid);
         messagesService.deleteMessage(user_id, mid2);
         String htmlTagName = ">_<";