diff options
author | 2013-11-16 03:24:33 +0700 | |
---|---|---|
committer | 2013-11-16 03:24:33 +0700 | |
commit | 2ddc060cee3e4b7f71819f95b833d802613a51b5 (patch) | |
tree | 508a659016665050663bb2cde0d75d61f9a6b288 | |
parent | dbf04c83e5b5aaef220e543554c66e74680ba62e (diff) |
MessagesQueries.getMyFeed + my own messages
-rw-r--r-- | src/com/juick/server/MessagesQueries.java | 40 |
1 files changed, 33 insertions, 7 deletions
diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java index 017c3350..8a3b1268 100644 --- a/src/com/juick/server/MessagesQueries.java +++ b/src/com/juick/server/MessagesQueries.java @@ -22,6 +22,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; +import java.util.Collections; /** * @@ -380,21 +381,19 @@ public class MessagesQueries { } public static ArrayList<Integer> getMyFeed(Connection sql, int uid, int before) { - ArrayList<Integer> mids = new ArrayList<Integer>(20); + ArrayList<Integer> mids = new ArrayList<Integer>(40); PreparedStatement stmt = null; ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE message_id<? AND (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); stmt.setInt(2, before); stmt.setInt(3, uid); - stmt.setInt(4, uid); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE (privacy>=0 OR (privacy>=-2 AND privacy<=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?))) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); stmt.setInt(2, uid); - stmt.setInt(3, uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -406,6 +405,33 @@ public class MessagesQueries { } finally { Utils.finishSQL(rs, stmt); } + + try { + if (before > 0) { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + stmt.setInt(2, before); + } else { + stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE user_id=? ORDER BY message_id DESC LIMIT 20"); + stmt.setInt(1, uid); + } + rs = stmt.executeQuery(); + rs.beforeFirst(); + while (rs.next()) { + mids.add(rs.getInt(1)); + } + } catch (SQLException e) { + System.err.println(e); + } finally { + Utils.finishSQL(rs, stmt); + } + + Collections.sort(mids, Collections.reverseOrder()); + int remove = mids.size() - 20; + for (int i = 0; i < remove; i++) { + mids.remove(20); + } + return mids; } @@ -555,11 +581,11 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sqlSearch.prepareStatement("SELECT @id AS message_id FROM messages WHERE MATCH(?) AND @id<? ORDER BY id DESC LIMIT 25"); + stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE MATCH(?) AND id<? ORDER BY id DESC LIMIT 25"); stmt.setString(1, search); stmt.setInt(2, before); } else { - stmt = sqlSearch.prepareStatement("SELECT @id AS message_id FROM messages WHERE MATCH(?) ORDER BY id DESC LIMIT 25"); + stmt = sqlSearch.prepareStatement("SELECT id AS message_id FROM messages WHERE MATCH(?) ORDER BY id DESC LIMIT 25"); stmt.setString(1, search); } rs = stmt.executeQuery(); |