Back
filter (clj)
(source)function
(filter expr1 clause1 & more)
Accepts alternating expressions and clauses and
produces a FILTER expression:
(filter :%count.* (where :> i 5))
Produces: COUNT(*) FILTER (WHERE i > ?)
Parameters: 5
Examples
honeysql
(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]))))