diff options
Diffstat (limited to 'juick-server/src/main/java/com/juick/service/ShowQueriesServiceImpl.java')
-rw-r--r-- | juick-server/src/main/java/com/juick/service/ShowQueriesServiceImpl.java | 28 |
1 files changed, 15 insertions, 13 deletions
diff --git a/juick-server/src/main/java/com/juick/service/ShowQueriesServiceImpl.java b/juick-server/src/main/java/com/juick/service/ShowQueriesServiceImpl.java index 2ddfeb84..50e0d243 100644 --- a/juick-server/src/main/java/com/juick/service/ShowQueriesServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/ShowQueriesServiceImpl.java @@ -2,10 +2,12 @@ package com.juick.service; import com.juick.User; import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; import javax.inject.Inject; +import java.util.Collections; import java.util.List; /** @@ -22,19 +24,19 @@ public class ShowQueriesServiceImpl extends BaseJdbcService implements ShowQueri @Override public List<String> getRecommendedUsers(final User forUser) { - return getJdbcTemplate().queryForList( - "SELECT users.nick FROM subscr_users INNER JOIN users " + - "ON subscr_users.user_id=users.id " + - "WHERE subscr_users.user_id NOT IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + - "AND subscr_users.suser_id IN (SELECT user_id FROM subscr_users WHERE suser_id=?) " + - "AND subscr_users.user_id NOT IN (SELECT bl_user_id FROM bl_users WHERE user_id=?) " + - "AND subscr_users.user_id!=? AND users.lastmessage>UNIX_TIMESTAMP()-259200 " + - "GROUP BY subscr_users.user_id ORDER BY count(*) DESC LIMIT 10", - String.class, - forUser.getUid(), - forUser.getUid(), - forUser.getUid(), - forUser.getUid()); + if (forUser == null) + return Collections.emptyList(); + + return getNamedParameterJdbcTemplate().queryForList( + "SELECT users.nick FROM subscr_users su1 INNER JOIN users u " + + "ON su1.user_id = u.id " + + "WHERE NOT EXISTS (SELECT 1 FROM subscr_users su2 WHERE su2.suser_id = :uid and su1.user_id = su2.user_id) " + + "AND EXISTS (SELECT 1 FROM subscr_users su3 WHERE su3.suser_id = :uid and su3.user_id = su1.suser_id ) " + + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and su1.user_id = b.bl_user_id) " + + "AND su1.user_id != :uid AND u.lastmessage > UNIX_TIMESTAMP() - 259200 " + + "GROUP BY su1.user_id ORDER BY count(*) DESC LIMIT 10", + new MapSqlParameterSource("uid", forUser.getUid()), + String.class); } @Override |