From 556d10c400add4b69d8832a40fb704fda4333080 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 28 Jan 2023 01:46:13 +0300 Subject: Improve SQL server compatibility --- src/main/java/com/juick/service/TagServiceImpl.java | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'src/main/java/com/juick/service/TagServiceImpl.java') diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index f15fd113..bbf21bb0 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -148,7 +148,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { "SELECT tags.name,COUNT(messages.message_id) " + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + "AND messages.message_id=messages_tags.message_id)) " + - "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id, tags.name ORDER BY tags.name ASC", new TagStatsMapper(), uid); } @@ -165,8 +165,9 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Transactional(readOnly = true) @Override public List getPopularTags() { - return getJdbcTemplate().queryForList( - "select name from tags where noindex=false order by stat_messages desc limit 20", String.class); + return getJdbcTemplate().queryForList(""" + select name from tags where noindex=false + order by stat_messages desc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""", String.class); } @Transactional(readOnly = true) @@ -179,9 +180,8 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { AND messages.message_id=messages_tags.message_id)) INNER JOIN tags ON messages_tags.tag_id=tags.tag_id WHERE tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore) - GROUP BY tags.tag_id HAVING COUNT(DISTINCT messages.user_id) > 1 - ORDER BY cnt DESC LIMIT 20 - """; + GROUP BY tags.tag_id, tags.name HAVING COUNT(DISTINCT messages.user_id) > 1 + ORDER BY cnt DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY"""; return getNamedParameterJdbcTemplate() .query(sql, new MapSqlParameterSource() .addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE), -- cgit v1.2.3