Changed fetch-image-dumps to use ljoins

2 files changed, 22 insertions, 18 deletions

diff --git a/src/tags.clj b/src/tags.clj
index d21531a..d292471 100644
--- a/src/tags.clj
+++ b/src/tags.clj
@@ -174,15 +174,16 @@ WHERE EXISTS
              :select ["m.content, m.message_id, m.created_on"
                       "u.nick, u.avatar, r.key"
                       (if user-tag-id
-                        [(str "EXISTS(SELECT 1 FROM tags WHERE tag = 'favorite' "
-                              "AND message_id = m.message_id AND user_id = ?) AS favorited")
+                        "t.created_on IS NOT NULL as favorited")]
+             :from   ["messages m"]
+             :ljoin  ["rooms r ON r.room_id = m.room_id"
+                      "users u ON u.user_id = m.user_id"
+                      (if user-tag-id
+                        ["tags t ON t.message_id = m.message_id AND t.user_id = ? AND t.tag = 'favorite'"
                          user-tag-id])]
-             :from   ["messages m, rooms r, users u"]
              :where  [(cond
                        nick ["m.user_id = (SELECT user_id FROM users WHERE LOWER(nick) = lower(?))" nick]
                        room ["m.room_id = (SELECT room_id FROM rooms where LOWER(key) = lower(?))" room])
-                      "m.user_id = u.user_id"
-                      "m.room_id = r.room_id"
                       (if image-only "m.is_image = true")
                       (if hide-vip "r.admin_only = false")
                       (cond date   [(format "m.created_on::date %s ?" comp-str) (to-sql-date date)]
diff --git a/src/utils.clj b/src/utils.clj
index 6d0a4ed..b315bb3 100755
--- a/src/utils.clj
+++ b/src/utils.clj
@@ -196,18 +196,20 @@
   (java.sql.Date. (.getTime dt)))
 
 (defn join-clauses [clauses]
-  (let [clause-func (fn [c]
-                      (cond (string? c) [c []]
-                            (vector? c) [(first c) (rest c)]
-                            :else (except! "Invalid query-clause: " c)))
-        pairs          (for [c clauses :when c]
-                         (clause-func c))
-        [clauses vars] (transpose pairs)]
-    [clauses
-     (apply concat vars)]))
+  (if clauses
+    (let [clause-func (fn [c]
+                        (cond (string? c) [c []]
+                              (vector? c) [(first c) (rest c)]
+                              :else (except! "Invalid query-clause: " c)))
+          pairs          (for [c clauses :when c]
+                           (clause-func c))
+          [clauses vars] (transpose pairs)]
+      [clauses
+       (apply concat vars)])
+    [[] []]))
 
-(defnk build-query [:select nil :from nil :where nil
-                    :ljoin nil
+(defnk build-query [:select nil :from nil
+                    :where nil :ljoin nil
                     :order nil :limit nil :indent "    "]
   (cond
    (not select) (except! "Invalid query missing SELECT")
@@ -215,6 +217,7 @@
    (not where)  (except! "Invalid query missing WHERE")
    :else (let [[sel-cls sel-var]     (join-clauses select)
                [from-cls from-var]   (join-clauses from)
+               [ljoin-cls ljoin-var] (join-clauses ljoin)
                [where-cls where-var] (join-clauses where)]
            (vec
             (concat
@@ -223,12 +226,12 @@
                    "\nFROM\n" indent
                    (str-join (str ",\n" indent) from-cls)
                    (if ljoin
-                     (str "\nLEFT JOIN " ljoin) "")
+                     (str-join "" (map #(str "\nLEFT JOIN " %) ljoin-cls)))
                    "\nWHERE\n" indent
                    (str-join (str " AND\n" indent) where-cls)
                    (if order (str "\nORDER BY " order) "")
                    (if limit (str "\nLIMIT " limit) ""))]
-             sel-var from-var where-var)))))
+             sel-var from-var ljoin-var where-var)))))
   
 (defn do-cmds [query]
   (with-connection *db*