Clojurians Log v2

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"

{:with [[:workday (workday-upsert m)]]
 :insert-into [[:work-intervals [:workday-id :interval]]
               {:select [:workday.id 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. https://github.com/seancorfield/honeysql/blob/v2/src/honey/sql.cljc#L257 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…

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"]

[: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

Oh I had missed :composite

Perfect, thanks!

https://github.com/seancorfield/honeysql/tree/v2#composite-types (README) — although the usage shown is with insert rather than select

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

Not sure what you mean.

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

Yes

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 🙂

So something like this?

(-> {:insert-into [[:foo [:interval]]
                   {:select [[:lift (->Interval nil nil)]]
                    :from :test}]}
    hsql/format)

; Execution error (ExceptionInfo) at honey.sql/format-dsl (sql.cljc:891).
; Unknown SQL clauses: :beginning, :end

Oh thanks, very useful information.

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

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

Great, thanks. 👍

: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

I tried and it gives me this error:

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

It works with the helpers function call:

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

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"]

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"]

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?

No "1.0.461"

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.

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" " "]

See https://cljdoc.org/d/com.github.seancorfield/honeysql/2.0.0-rc2/doc/getting-started/sql-operator-reference

Great, very relevant. Thanks a lot.