problems with aggregate

[Licenser]

Example:

(cql/aggregate [[:sum/hours :as :total]
           [:YEAR/date :as :year]
           [:tasks.desc :as :task]
           [:MONTH/date :as :month]
           [:taskID :as :tid]]
          [:MONTH/date :taskID])

has the following problems:

1) you can't ruse :month in the group by 2) name the field being grouped by since it creates an SQL error, unnamed fields give horrible maps as return.

[doooks]

This is a problem for me, too. How can we do arbitrary group bys, like "GROUP BY SUM(prices.pence / supplier_products.quantity)"

At the moment I am having to the grouping in code, on the whole result set, which is fairly horrific.

[doooks]

@Licenser I think you might find that the workaround looks something like (cql/aggregate table [["SUM(hours)" :as :total]]) etc. Hope this helps.

[Licenser]

Yes so it didn't solve the fact that it was very hard to reference things like :total in the later code I came up with this:

(def infex {'in "IN"
            'like "LIKE"
            'notlike "NOT LIKE"
            'notin "NOT IN"
            '= "="
            '<= "<="
            '< "<"
            '/ "/"
            '* "*"
            '+ "+"
            '- "-"
            '> ">"
            '>= ">="
            'and "AND"
            'or "OR"            
            'not= "!="})

(defn infixfn [stmt]
  (fn [& args]
    (str "(" (apply str (interpose (str " " stmt " ") args)) ")")))

(defn prefixfn [stmt]
  (fn [& args]
    (str stmt "(" (apply str (interpose "," args)) ")")))

(defn to-sql-data [v]
  (cond
   (vector? v)
   (str "(" (apply str (interpose "," (map to-sql-data v))) ")")
   (string? v)
   (str "\"" (clojure.string/replace v #"[\"\\]" (fn [x] (str  "\\\\"  (if (= x "\\") "\\") x))) "\"")
   (keyword? v)
   (apply str (interpose "." (map #(str "`" % "`") (re-seq #"[^.]+" (name v)))))   
   :else
   (str v)))

(defn outerfn [term]
  (cond
   (list? term)
   (let [[h & t] term]
     (conj (map
            (fn outerfn-map [x]
              (if (symbol? x)
                (list to-sql-data x)
                x)) t)
           (if-let [term (infex h)]
             (list infixfn term)
             (cond
              (= h 'distinct)
              (fn outerfn1 [& args]
                (str "DISTINCT " (apply str (interpose " " args))))
              (= h 'between)
              (fn outerfn2 [a b c]
                (str "(" a " BETWEEN " b " AND " c ")"))
              (= h 'not-nil?)
              (fn outerfn3 [a]
                (str "(" a " NOT NULL)"))
              (= h 'nil?)
              (fn outerfn4 [a]
                (str "(" a " NULL)"))
              :else
              (list prefixfn  (.toUpperCase (name h)))))))
   (vector? term)
   (list (fn outerfn5 [t] (str "(" (apply str (interpose ","  (map to-sql-data t))) ")")) term)
   :else
   term))

(defn replacefn [term]
  (cond
   (list? term)
   (walk replacefn outerfn term)
   (vector? term)
   (walk replacefn outerfn term)
   (symbol? term)
   term
   :else
   (to-sql-data term)))

(defmacro sqlify [term]
   (walk replacefn outerfn term))

feel free to use it it allows code such as:

;..
(cql/select
  (sqlify
      (andd
       (not (like :name "DUMMY%"))
       (= (year :date) year) 
       (in :tasks.taskID [5 6 7 8 9 11 12 13 14 15 16 18])
       (in (month :tasks.date) month))))
;...

[LauJensen]

Just to be clear, the problem you guys are seeing here is the qualifications of the columns listed in the group by, right?

[Licenser]

Wow that is a long time ago, the problem was when I remember right that the columns defined in aggregate were not reusable or functions in general rather hard.

[LauJensen]

Alright. I'll assume this has been fixed in a later update and re-open if Im proven wrong.