From 3ea93238b71eb1af0bdfcbab4559c9a6734944e7 Mon Sep 17 00:00:00 2001 From: Carlo Zancanaro Date: Tue, 12 Nov 2013 10:21:53 +1100 Subject: Fix grouping - it used to allow for groupings leaving a non-grouped field Now it takes a third "projection" argument in which one can perform aggregate function over the existing fields. The fields of the resulting query are the union of the grouping fields and the projected fields (with the projected fields taking precedence). If you try to project a field without applying some sort of function to it then you'll get an exception, but at the moment no function calls are actually validated as aggregate functions (in order to do so we'd need a knowledge of all the aggregate functions, which isn't possible in general). --- README.md | 23 +++++++++++++++++++++++ src/clojure_sql/dsl.clj | 30 +++++++++++++++++++++++++----- 2 files changed, 48 insertions(+), 5 deletions(-) diff --git a/README.md b/README.md index d29cb5f..4eed172 100644 --- a/README.md +++ b/README.md @@ -210,6 +210,29 @@ be the return value of the associated query function call (`deref`, ### 0.2.0 +* **Breaking:** `group` has changed significantly. It now takes a third + argument which is an 'auxiliary projection'. The fields in the + resulting query will be the union of the grouped fields and the + projection (with the projection taking precedence). Each value in + the projection must be, in some way, an aggregate of the grouped + values (this is not, and cannot really be, enforced, but we try to + warn you if you're obviously wrong). + + :::clojure + (-> (q/table :users) + (q/project [:age :name]) + (q/group [:age] {'(string_agg :name ",") :names})) + ;=> ["SELECT \"users1785\".\"age\" AS \"age\", (\"string_agg\"(\"users1785\".\"name\",?)) AS \"names\" + FROM \"users\" AS \"users1785\" + GROUP BY \"users1785\".\"age\"" + ","] + + (-> (q/table :users) + (q/project [:age :name]) + (q/group [:age] {:name :name})) + ;=> Exception! Expr is not a function application - could not + possible be an aggregate + * **Breaking:** Remove `having`, use `select` instead now * `sort` can now sort on expressions, not just table names diff --git a/src/clojure_sql/dsl.clj b/src/clojure_sql/dsl.clj index 16bc2e5..413b9c0 100644 --- a/src/clojure_sql/dsl.clj +++ b/src/clojure_sql/dsl.clj @@ -61,6 +61,10 @@ :right right :type type }))) +(defn ^:private not-a-function-expression [expr] + (throw (ex-info "Expr is not a function application - could not possibly be an aggregate" + {:expr expr}))) + (defn ^:private invalid-union [queries] (throw (ex-info "Cannot union queries with different fields" {:queries queries}))) @@ -326,19 +330,35 @@ (defn group "Apply a grouping to a query. - `fields` is a sequential collection of fields to group by. + `fields` is a collection of fields to group by. `projection` is a + map with which to perform a projection. + + The projection must perform an aggregate function on each + non-grouped field, or else the resulting group is invalid. This is + not verifiable without knowing the full set of aggregate + functions (which is not possible in general), so any projection will + be accepted as long as a function is called as a part of each + projection. If the query has already been grouped then this will create a subquery." - [query fields] + [query fields projection] (let [query (if (:group query) (convert-to-subquery query) query) fields-seq (if (sequential? fields) fields - [fields])] - (assoc query :group (map (partial resolve-field (:tables query) (:fields query)) - fields-seq)))) + [fields]) + fields-map (into {} (map vector fields fields))] + (doseq [[expr name] projection] + (try (or (some #{expr} fields-seq) + (seq expr)) + (catch Exception e + (not-a-function-expression expr)))) + (-> query + (assoc :group (map (partial resolve-field (:tables query) (:fields query)) + fields-seq)) + (project (into fields-map projection))))) (defn take "Limit the number of results of a query. -- cgit v1.2.3