From dec25289272868118619294dbbd8b4a9c12b09d7 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 18 Dec 2022 03:11:14 +0300 Subject: Fix `getUserBlogAtDay` query --- src/main/java/com/juick/service/MessagesServiceImpl.java | 16 ++++++++-------- 1 file changed, 8 insertions(+), 8 deletions(-) (limited to 'src/main/java/com/juick') diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 5969443c..bb8c48e1 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -44,10 +44,7 @@ import org.springframework.transaction.annotation.Transactional; import javax.inject.Inject; import java.net.URI; -import java.sql.PreparedStatement; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Timestamp; +import java.sql.*; import java.time.Instant; import java.time.ZoneOffset; import java.time.temporal.ChronoUnit; @@ -727,15 +724,18 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public List getUserBlogAtDay(final int uid, final int privacy, final int daysback) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("uid", uid) - .addValue("privacy", privacy).addValue("daysback", daysback); + var backDate = Instant.now().minus(daysback, ChronoUnit.DAYS).atOffset(ZoneOffset.UTC); + SqlParameterSource sqlParameterSource = new MapSqlParameterSource() + .addValue("uid", uid) + .addValue("privacy", privacy) + .addValue("date", backDate, Types.TIMESTAMP_WITH_TIMEZONE); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid" + (daysback > 0 - ? " AND ts >= date(NOW() - INTERVAL :daysback day)" - + " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" + ? " AND ts >= :date" + + " AND ts < :date" : StringUtils.EMPTY) + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); -- cgit v1.2.3