within-group (clj)

(ns honey.sql.helpers-test
  (:refer-clojure :exclude [filter for group-by partition-by set update])
  (:require [clojure.test :refer [deftest is testing]]
            [honey.sql :as sql]
            [honey.sql.helpers :as h
             :refer [add-column add-index alter-table columns create-table create-table-as create-view
                     create-materialized-view drop-view drop-materialized-view
                     create-index
                     bulk-collect-into
                     cross-join do-update-set drop-column drop-index drop-table
                     filter from full-join
                     group-by having insert-into
                     join-by join lateral left-join limit offset on-conflict
                     on-duplicate-key-update
                     order-by over partition-by refresh-materialized-view
                     rename-column rename-table returning right-join
                     select select-distinct select-top select-distinct-top
                     values where window with with-columns
                     with-data within-group]]))

(deftest filter-within-order-by-test
  (testing "PostgreSQL filter, within group, order-by as special syntax"
    (is (= (sql/format {:select [[[:filter :%count.* {:where [:> :i 5]}] :a]
                                 [[:filter ; two pairs -- alias is on last pair
                                   [:avg :x [:order-by :y [:a :desc]]] {:where [:< :i 10]}
                                   [:sum :q] {:where [:= :x nil]}] :b]
                                 [[:within-group [:foo :y] {:order-by :x}]]]})
           [(str "SELECT COUNT(*) FILTER (WHERE i > ?) AS a,"
                 " AVG(x, y ORDER BY a DESC) FILTER (WHERE i < ?),"
                 " SUM(q) FILTER (WHERE x IS NULL) AS b,"
                 " FOO(y) WITHIN GROUP (ORDER BY x ASC)")
            5 10])))
  (testing "PostgreSQL filter, within group, order-by as helpers"
    (is (= (sql/format (select [(filter :%count.* (where :> :i 5)) :a]
                               [(filter ; two pairs -- alias is on last pair
                                 ;; order by must remain special syntax here:
                                 [:avg :x [:order-by :y [:a :desc]]] (where :< :i 10)
                                 [:sum :q] (where := :x nil)) :b]
                               [(within-group [:foo :y] (order-by :x))]))
           [(str "SELECT COUNT(*) FILTER (WHERE i > ?) AS a,"
                 " AVG(x, y ORDER BY a DESC) FILTER (WHERE i < ?),"
                 " SUM(q) FILTER (WHERE x IS NULL) AS b,"
                 " FOO(y) WITHIN GROUP (ORDER BY x ASC)")
            5 10]))))