Add union/intersection to the dsl

2 files changed, 43 insertions, 19 deletions

diff --git a/src/clojure_sql/compiler.clj b/src/clojure_sql/compiler.clj
index 6b3ec4a..d0b1206 100644
--- a/src/clojure_sql/compiler.clj
+++ b/src/clojure_sql/compiler.clj
@@ -223,24 +223,28 @@
                (if drop
                  (str " OFFSET " drop)))))
 
+(def ^:private set-operations {:union "UNION", :intersect "INTERSECT"})
+
 (defmulti compile-query (fn [db _] db) :default :postgres)
-(defmethod compile-query :postgres [db {:keys [tables fields joins where sort group having union take drop]}]
-  (if union
-    (->> union
-         (map (partial compile-query db))
-         (apply sequence)
-         ((p-lift string/join " UNION ")))
-    ($str (return "SELECT ")
-          (compile-fields db fields)
-          (if tables
-            ($str (return " FROM ")
-                  (compile-tables db joins tables))
-            ($str ""))
-          (compile-where db where)
-          (compile-group db group)
-          (compile-having db having)
-          (compile-sort db sort)
-          (compile-limit db take drop))))
+(defmethod compile-query :postgres [db {:keys [tables fields joins where sort group having take drop set-operation queries]}]
+  (or (if set-operation
+        (let [op-str (str ") " (get set-operations set-operation) " (")]
+          (->> queries
+               (map (partial compile-query db))
+               (apply sequence)
+               ((p-lift string/join op-str))
+               $add-parentheses)))
+      ($str (return "SELECT ")
+            (compile-fields db fields)
+            (if tables
+              ($str (return " FROM ")
+                    (compile-tables db joins tables))
+              ($str ""))
+            (compile-where db where)
+            (compile-group db group)
+            (compile-having db having)
+            (compile-sort db sort)
+            (compile-limit db take drop))))
 
 
 
diff --git a/src/clojure_sql/dsl.clj b/src/clojure_sql/dsl.clj
index 6ba81bc..0a05763 100644
--- a/src/clojure_sql/dsl.clj
+++ b/src/clojure_sql/dsl.clj
@@ -177,7 +177,8 @@
   (and (nil? (:group query))
        (nil? (:having query))
        (nil? (:take query))
-       (nil? (:drop query))))
+       (nil? (:drop query))
+       (nil? (:set-operation query))))
 
 (defn ^:private convert-to-subquery [query]
   (-> (table query)
@@ -195,7 +196,7 @@
 
 (def ^:private valid-join-type? (comp boolean #{:cross :inner :outer :full-outer}))
 (defn join
-  "Join two queries into one query The fields of the resultant query
+  "Join two queries into one query. The fields of the resultant query
   will be the union of the argument queries.
 
   If `type` is not provided then the join type will be automatically
@@ -376,3 +377,22 @@
     (if-let [old-drop (:drop query)]
       (assoc query :drop (+ old-drop n))
       (assoc query :drop n))))
+
+(defn ^:private union-compatible? [& queries]
+  (apply = (map (comp set keys :fields) queries)))
+
+(defn union
+  "Combine the results of two queries"
+  [& queries]
+  (assert (apply union-compatible? queries) "Unioned queries must expose the same fields.")
+  (assoc (q/->Query)
+    :set-operation :union
+    :queries queries))
+
+(defn intersection
+  "Take the common rows in two queries"
+  [& queries]
+  (assert (apply union-compatible? queries) "Unioned queries must expose the same fields.")
+  (assoc (q/->Query)
+    :set-operation :intersect
+    :queries queries))