Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2023-11-21

Channels

#honeysql
<
2023-11-21
>
namenu15:11:10

I have this kind of where clauses. 

(h/where 
  (when (:id filter-options)
    [:= :id (:id filter-options)])
  (when (seq (:ids filter-options))
    [:in :id (:ids filter-options)])
  (when (:user-id filter-options)
    [:= :user-id (:user-id filter-options)])
  (when (seq (:user-ids filter-options))
    [:in :user-id (:user-ids filter-options)])
  (when (:crop-id filter-options)
    [:= :crop-id (:crop-id filter-options)])
  (when (seq (:crop-ids filter-options))
    [:in :crop-id (:crop-ids filter-options)])
  ; ... and many more filters ...
  )
I felt like "https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/operators/member-access-operators#null-conditional-operators--and-" was what I needed. The attempt I made was to (register-fn! :?= ...) but it seems that I cannot return nil from formatter.

namenu15:11:57

(sql/register-fn! :?=
                  (fn [_op [l r]]
                    (when (some? l)
                      [:= l r])))
                    
(sql/format [:and [:?= nil :a]])
; => [""]
is it possible to make it ["TRUE"] or so?

p-himik15:11:37

I think you can replace (when (some? r) ...) with (if (nil? r) true ...).

👍 1
namenu15:11:30

returning [true] works, but ["WHERE true AND true AND true"] 😅

p-himik15:11:28

Yeah, no way around that without either overriding handling of the :where clause or filtering the conditions themselves. This is how I'd write something like that (I don't use helper functions generally): 

(let [conds (filter peek [[:= :id (:id filter-options) ...])
      sqlmap {:select :id :from :t}]
  (cond-> sqlmap
    (seq conds)
    (assoc :where (into [:and] conds))))

namenu16:11:11

yes indeed, yet it is prevailing pattern for our code base. so I was looking for syntax extension.

seancorfield17:11:40

I would use cond-> for this and just thread through multiple h/where calls: 

(cond-> sql
    (:id filter-options)
    (h/where [:= :id (:id filter-options)])
    ..)
(that should do some simplification of the DSL to produce less redundant SQL)