diff options
| -rw-r--r-- | src/com/juick/server/MessagesQueries.java | 34 |
1 files changed, 13 insertions, 21 deletions
diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java index 80d7a280..87784f63 100644 --- a/src/com/juick/server/MessagesQueries.java +++ b/src/com/juick/server/MessagesQueries.java @@ -236,31 +236,20 @@ public class MessagesQueries { public static ArrayList<Integer> getMyFeed(Connection sql, int uid, int before) { ArrayList<Integer> mids = new ArrayList<Integer>(20); - /* - my $sql="SELECT DISTINCT t.message_id FROM ("; - $sql.="SELECT STRAIGHT_JOIN message_id FROM subscr_users INNER JOIN messages ON subscr_users.user_id=messages.user_id WHERE privacy>=0 AND subscr_users.suser_id=$uid"; - $sql.=" UNION SELECT message_id FROM (subscr_users INNER JOIN messages ON subscr_users.user_id=messages.user_id) INNER JOIN wl_users ON messages.user_id=wl_users.user_id WHERE privacy=-1 AND subscr_users.suser_id=$uid AND wl_users.wl_user_id=$uid"; - $sql.=" UNION SELECT message_id FROM messages WHERE user_id=$uid"; - $sql.=" UNION SELECT message_id FROM subscr_messages WHERE suser_id=$uid"; - $sql.=" UNION SELECT message_id FROM favorites WHERE user_id=$uid"; - $sql.=" UNION SELECT message_id FROM favorites INNER JOIN subscr_users ON (subscr_users.suser_id=$uid AND favorites.user_id=subscr_users.user_id)"; - $sql.=") AS t LEFT JOIN messages_tags USING(message_id) WHERE (tag_id NOT IN (SELECT tag_id FROM bl_tags WHERE user_id=$uid) OR tag_id IS NULL)"; - if($before_mid>0) { - $sql.=" AND message_id<$before_mid"; - } - $sql.=" ORDER BY t.message_id DESC LIMIT 20"; - - */ PreparedStatement stmt = null; ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM feed WHERE user_id=? AND message_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=-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.setInt(1, uid); stmt.setInt(2, before); + stmt.setInt(3, uid); + stmt.setInt(4, uid); } else { - stmt = sql.prepareStatement("SELECT message_id FROM feed 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=-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.setInt(1, uid); + stmt.setInt(2, uid); + stmt.setInt(3, uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -282,12 +271,15 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages_access WHERE user_id=? AND message_id<? ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM (SELECT message_id FROM messages_access WHERE user_id=? AND message_id<? UNION SELECT message_id FROM messages WHERE user_id=? AND privacy<-1 AND message_id<?) AS t ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); stmt.setInt(2, before); + stmt.setInt(3, uid); + stmt.setInt(4, before); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages_access WHERE user_id=? ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM (SELECT message_id FROM messages_access WHERE user_id=? UNION SELECT message_id FROM messages WHERE user_id=? AND privacy<-1) AS t ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); + stmt.setInt(2, uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -366,10 +358,10 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND privacy>0 AND messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages INNER JOIN favorites ON (favorites.user_id=11574 AND favorites.message_id=messages.message_id) WHERE messages.message_id<? AND messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); stmt.setInt(1, before); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages INNER JOIN favorites ON (favorites.user_id=11574 AND favorites.message_id=messages.message_id) WHERE messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); } rs = stmt.executeQuery(); rs.beforeFirst(); |