From 70a50b2252ed313e3aee1a9ee2f33be8a46cb028 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Thu, 1 Mar 2018 19:18:03 +0300 Subject: sort discussions by last replay date --- .../com/juick/service/MessagesServiceImpl.java | 40 +++++++++++++--------- .../java/com/juick/service/MessageServiceTest.java | 4 +++ 2 files changed, 28 insertions(+), 16 deletions(-) (limited to 'juick-server-jdbc') 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 getDiscussions(final int uid, final int before) { + public List 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 getMessages(final Collection mids) { + public List getMessages(final List mids) { if (CollectionUtils.isNotEmpty(mids)) { - return getNamedParameterJdbcTemplate().query( + List 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 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 = ">_<"; -- cgit v1.2.3