Fork me on GitHub

Hello. What's the story around using your own Records/types with honeysql V2 . In version 1 there seems to be a honeysql.format/ToSQL protocol to tell honeysql how to deal with Record parameters. In v2 this seems to be gone? When I pass in a record type as a parameter to honeysql v2's format function it incorrectly parses the record fields as sql clauses:

:message "Unknown SQL clauses: :beginning, :end"
Any thoughts on how to solve this in v2 is much appreciated. The use case would be to pass the record as a parameter value that can then be handled by next.jdbc's settable parameter protocol which would dispatch on the Record type. Edit: Since breaking changes can be introduced with V2, perhaps this would be a good time to just not treat Records as SQL clauses maps? Edit 2: The expression in question where interval in the select statement is the record in question.
{:with [[:workday (workday-upsert m)]]
 :insert-into [[:work-intervals [:workday-id :interval]]
               {:select [ interval]
                :from :workday}]
 :returning :*}


So I think the problem is related to how the format-selectable-dsl function checks if something is a map, which records are. It seems the code should check that something is both a map and not a record, but I'm not sure if this is then something that would have to be done a lot of places. Edit: I've not had the chance to test this in practice, but could test this after work today if you'd be interested in a pull-request for this.


@snorremd I think you can wrap such things with [:lift ..]


That’s the “escape” mechanism for passing sequential and map-like types through as parameter values, if I’m understanding what you’re trying to do…


So something like this?

(-> {:insert-into [[:foo [:interval]]
                   {:select [[:lift (->Interval nil nil)]]
                    :from :test}]}
Produced an error:
; Execution error (ExceptionInfo) at honey.sql/format-dsl (sql.cljc:891).
; Unknown SQL clauses: :beginning, :end


:select expects a sequence of expressions, but those can be aliased, so a function call without an alias still needs to be written as if it did have one — the docs have examples and talk about this.


{:select [[[:lift (->Interval nil nil)]]] .. should do it.


Ah, thanks. That worked. 🙂


:select [:a [:b :c] [[:fun 42] :d]] => SELECT a, b AS c, FUN(42) AS d

👍 2
Michaël Salihi16:05:47

Hi everybody! I begin to playing with honeysql and I wonder how I can do SELECT (first_name, last_name) as name


user=> (sql/format {:select [[[:composite :first_name :last_name] :name]]})
["SELECT (first_name, last_name) AS name"]

Michaël Salihi17:05:53

I tried and it gives me this error:

Assert failed: Alias should have two parts[:composite :first_name :last_name]
(= 2 (count x))

Michaël Salihi17:05:39

It works with the helpers function call:

(h/format {:select [[(composite :first_name :last_name) :name]]})

Michaël Salihi17:05:01

I prefer the first syntax. Any idea?


You have missed a level of nesting. Funnily enough I was just explaining this to someone else…


:select [:a :b :c] => SELECT a, b, c


:select [:a [:b :c]] => SELECT a, b AS c


:select [:a [[:foo :bar] :c]] => SELECT a, FOO(bar) AS c


And as I showed above:

user=> (sql/format {:select [[[:composite :first_name :last_name] :name]]})
["SELECT (first_name, last_name) AS name"]
Note the nesting level.


If I add spaces it might be easier to see:

user=> (sql/format {:select [    [  [:composite :first_name :last_name] :name  ]    ]})
["SELECT (first_name, last_name) AS name"]

Michaël Salihi18:05:24

No success, always same error with a simple copy paste:

user=> (sql/format {:select [[[:composite :first_name :last_name] :name]]})
Execution error (AssertionError) at honeysql.format/seq->sql (format.cljc:385).
Assert failed: Alias should have two parts[:composite :first_name :last_name]
(= 2 (count x))


You’re using HoneySQL V2?

Michaël Salihi19:05:19

OK I move to v2, thx


Sorry, yeah, what I suggested is for V2. On V1, you’d have to use the helper I think or sql/call.

👍 2
Michaël Salihi20:05:27

I supposed sqlite is not well supported. With particulary syntax like:

SELECT first_name || " " || last_name as name FROM users;


[:|| :first_name " " :last_name] should work.


:select [ [ [:|| ...] :name ] ]


user=> (sql/format {:select [ [ [:|| :first_name " " :last_name] :name ] ]})
["SELECT first_name || ? || last_name AS name" " "]
Several DBs have || as a string concatenation operator.

Michaël Salihi20:05:42

Great, very relevant. Thanks a lot.


[:composite :first_name :last_name] is a function call/operator syntax


[[:composite :first_name :last_name] :name] is an aliased expression


and :select takes a sequence of expressions

Michaël Salihi16:05:48

Oh I had missed :composite

Michaël Salihi16:05:55

Perfect, thanks!

seancorfield16:05:30 (README) — although the usage shown is with insert rather than select

Michaël Salihi16:05:37

Last thing, is there an equivalent of "%pattern%" for a where clause?


Not sure what you mean.


[:like :first_name "%foo%"] ?


BTW, it can be really inefficient to query with a leading % in a regex — it will do a full table scan in all cases.


Trailing % is OK, because SQL can use the stem value (the portion before the %) to narrow down the index range, assuming you have an index on the column 🙂

Michaël Salihi16:05:09

Oh thanks, very useful information.

Michaël Salihi16:05:56

So with your snippet, if I want pass a var so I'll do [:like :first_name (str "%" foo "%")]

Michaël Salihi16:05:23

or is a cleaner way to do it?


If you have just a string and you want it to be a SQL regex and match anywhere in the column value, you have to add the % yourself.


I think :like will be case sensitive in most cases (depends on the DB and how it is setup) so there’s also :ilike for a case-insensitive comparison

Michaël Salihi16:05:51

Great, thanks. 👍