From 906bf8ef21463f99820764807b656cb617b0aaa2 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 28 Jan 2023 18:24:32 +0300 Subject: Cleanup Tag model --- src/main/java/com/juick/service/MessagesServiceImpl.java | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) (limited to 'src/main/java/com/juick/service/MessagesServiceImpl.java') diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 790e695e..4247cee5 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -153,7 +153,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setInt(1, mid); - ps.setInt(2, newTags.get(i).TID); + ps.setInt(2, newTags.get(i).getId()); } @Override @@ -1032,13 +1032,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List getPopularCandidates() { var beforeTime = Instant.now().minus(2, ChronoUnit.HOURS); + var nsfw = tagService.getTag("NSFW", true); var sql = """ SELECT replies.message_id FROM replies INNER JOIN messages ON replies.message_id = messages.message_id LEFT JOIN favorites ON favorites.message_id = messages.message_id LEFT JOIN messages_tags ON messages_tags.message_id = messages.message_id WHERE COALESCE(messages_tags.tag_id, 0) != 2 - AND COALESCE(messages_tags.tag_id, 0) != 805 AND replies.ts > :before + AND COALESCE(messages_tags.tag_id, 0) != :nsfw_id AND replies.ts > :before AND NOT EXISTS (SELECT 1 FROM favorites WHERE message_id = messages.message_id AND user_id = 2) GROUP BY replies.message_id HAVING COUNT(DISTINCT(replies.user_id)) > 5 UNION ALL SELECT favorites.message_id FROM favorites @@ -1051,7 +1052,9 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ GROUP BY favorites.message_id HAVING COUNT(DISTINCT favorites.user_id) > 2 """; return getNamedParameterJdbcTemplate().queryForList(sql, new MapSqlParameterSource() - .addValue("before", beforeTime.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE), + .addValue("nsfw_id", nsfw.getId()) + .addValue("before", beforeTime.atOffset(ZoneOffset.UTC), + java.sql.Types.TIMESTAMP_WITH_TIMEZONE), Integer.class); } -- cgit v1.2.3