1 files changed, 34 insertions, 33 deletions
diff --git a/src/site.clj b/src/site.clj
index e3830c0..ef4e773 100755
--- a/src/site.clj
+++ b/src/site.clj
@@ -396,43 +396,44 @@
 (def *run-update-directory* true)
 (def *update-directory-sleep* (minutes 15))
 
-(def *directory-update-query* "
-SELECT m.user_id,
-       u.nick,
-       u.avatar,
-       message_id,
-       m.created_on,
-       msg_count,
-       content
-FROM   (SELECT user_id,
-               created_on,
-               message_id,
-               content,
-               MAX(created_on) OVER (PARTITION BY user_id) max_created_on,
-               COUNT(*) OVER (PARTITION BY user_id)        msg_count
-        FROM   messages
-        WHERE  room_id = 1
-               AND is_image = true) AS m,
-       users u
-WHERE  m.created_on = max_created_on
-       AND m.user_id = u.user_id
-ORDER  BY msg_count DESC")
-
+(defn directory-search [offset]
+  (let [directory @*directory-listing* 
+        users (subvec directory
+                      (min (count directory) 
+                           (* offset *per-directory-page*))
+                      (min (count directory)
+                           (* (inc offset) *per-directory-page*)))
+        user-ids (apply str (interpose ", " (map #(%1 :user_id) users)))
+        qry (str "SELECT u.user_id, u.nick, u.avatar, m.content, m.message_id
+                  FROM users u, messages m
+                  WHERE u.user_id in (" user-ids ")
+                  AND m.user_id = u.user_id
+                  AND m.message_id = (SELECT message_id FROM messages 
+                           WHERE user_id = u.user_id
+                           AND room_id = 1
+                           AND is_image = true
+                           ORDER BY created_on DESC LIMIT 1)")]
+    (when (> (count user-ids) 0)
+      (let [res (do-select [qry])
+            keys (map :user_id res)
+            res-dict (zipmap keys res)]
+        (map (fn [u]
+               (let [u-id (u :user_id)]
+                 (process-directory-listing (merge u (res-dict u-id)))))
+             users)))))
+           
 (defn update-directory! []
-  (let [res (vec (do-select [*directory-update-query*]))]
+  (let [qry "SELECT u.user_id, COUNT(m) as cnt
+             FROM users u, messages m
+             WHERE u.user_id = m.user_id
+             AND m.room_id = 1
+             AND m.is_image = true
+             GROUP BY u.user_id
+             ORDER BY COUNT(m) DESC"
+        res (vec (do-select [qry]))]
     (dosync (ref-set *directory-listing* res))
     res))
 
-(defn directory-search [offset]
-  (let [directory @*directory-listing*
-        users     (subvec directory
-                          (min (count directory)
-                               (* offset *per-directory-page*))
-                          (min (count directory)
-                               (* (inc offset) *per-directory-page*)))]
-    (when (> (count users) 0)
-      (map process-directory-listing users))))
-
 (defn update-directory-agent-func [x]
   (update-directory!)
   (Thread/sleep *update-directory-sleep*)