I'm trying to use honeysql to programmatically build a query, adding on where clauses as I go.
Coming from python and using sqlalchemy, I can do something like:
In [3]: query = model.Account.query
In [4]: query = query.filter_by(id=1)
In [5]: query = query.filter_by(email='[email protected]')
In [6]: query = query.filter_by(username='someuser')
In [7]: query = query.filter_by(is_active=1)
In [8]: printquery(query)
SELECT *
FROM account
WHERE account.id = 1 AND account.email = '[email protected]'
AND account.username = 'someuser' AND account.is_active = 1
However, using honeysql, my where clause isn't as clean.
user=> (require '[honeysql.core :as sql])
user=> (require '[honeysql.helpers :refer :all])
user=> (->
#_=> (select :*)
#_=> (from :test)
#_=> (merge-where [:= :a 1])
#_=> (merge-where [:= :b 2])
#_=> (merge-where [:= :c 3])
#_=> (merge-where [:= :d 4])
#_=> sql/format)
["SELECT * FROM test WHERE (((a = 1 AND b = 2) AND c = 3) AND d = 4)"]
I am aware that they're logically the same thing, but as I start to get more and more complex, I'm starting to get nervous that I'm gonna get some subtle query acting weird with extra parens that causes me problems.
Am I being crazy? Should I stop worrying and learn to love the extra parens (It is clojure after all)? Or is there a better pattern for query building I'm not aware of? Should I build my where clauses as a big vector, and add them all to the query map at the very end?
Any advice would be greatly appreciated!
Look at it from a logical perspective
where a, b and c are propositions like
The AND operator is associative, meaning that it doesn't matter where the parenthesis are placed, or how many, as long as the sequence of the propositions remains the same.
Worked out examples here.