aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/service/MessagesServiceImpl.java
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2022-12-18 03:11:14 +0300
committerGravatar Vitaly Takmazov2022-12-18 03:11:14 +0300
commitdec25289272868118619294dbbd8b4a9c12b09d7 (patch)
tree0acafcf7da7105deb4b9337ee9bad32d47b177fa /src/main/java/com/juick/service/MessagesServiceImpl.java
parentc5abe180a8351279fddfb6a27050244272a9727d (diff)
Fix `getUserBlogAtDay` query
Diffstat (limited to 'src/main/java/com/juick/service/MessagesServiceImpl.java')
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java16
1 files changed, 8 insertions, 8 deletions
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<Integer> 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);