honeysql merge-where building a large query

358 views Asked by At

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!

1

There are 1 answers

1
nilo de roock On

Look at it from a logical perspective

a AND b AND c = ( a AND b ) AND c = a AND ( b AND c ).

where a, b and c are propositions like

A = 1

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.