Consolidated directory listing query

1 files changed, 33 insertions, 34 deletions

diff --git a/src/site.clj b/src/site.clj
index ef4e773..e3830c0 100755
--- a/src/site.clj
+++ b/src/site.clj
@@ -396,44 +396,43 @@
 (def *run-update-directory* true)
 (def *update-directory-sleep* (minutes 15))
 
-(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)))))
-           
+(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 update-directory! []
-  (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]))]
+  (let [res (vec (do-select [*directory-update-query*]))]
     (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*)