Fork me on GitHub
Slack Archive

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

2021-05-12

Channels

#honeysql
<
2021-05-12
>
snorremd09:05:14

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 [:workday.id interval]
                :from :workday}]
 :returning :*}

snorremd14:05:08

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.

seancorfield16:05:10

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

seancorfield16:05:10

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…

snorremd16:05:08

So something like this? 

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

seancorfield16:05:27

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

seancorfield16:05:46

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

snorremd16:05:13

Ah, thanks. That worked. 🙂

seancorfield16:05:58

: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

seancorfield16:05:31

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?

seancorfield17:05:53

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

seancorfield17:05:05

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

seancorfield17:05:23

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

seancorfield17:05:52

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

seancorfield17:05:23

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.

seancorfield17:05:12

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))

seancorfield19:05:21

You’re using HoneySQL V2?

Michaël Salihi19:05:19

OK I move to v2, thx

seancorfield19:05:54

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;

seancorfield20:05:12

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

seancorfield20:05:33

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

seancorfield20:05:41

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.

seancorfield16:05:04

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

seancorfield16:05:19

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

seancorfield16:05:29

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

https://github.com/seancorfield/honeysql/tree/v2#composite-types (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?

seancorfield16:05:05

Not sure what you mean.

seancorfield16:05:34

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

seancorfield16:05:10

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

seancorfield16:05:58

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?

seancorfield16:05:24

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.

seancorfield16:05:09

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. 👍