honeysql 2021-05-01 | Slack Archive

snorremd17:05:12

Hi! I'm struggling to combine the with and insert-into functions to build a query. Any help is appreciated. EDIT: Found the answer using the pure clojure map specification:

{:with [[:workday {:insert-into :workdays
                   :values [{:work-date (t/today)
                             :work-interval (t/new-duration 7 :hours)
                             :account-id #uuid "5d90856c-5a48-46ae-8f6e-a06295ea0dfb"}]
                   :on-conflict [:work-date :account-id]
                   :do-update-set [:work-interval]
                   :returning [:id]}]]
 :insert-into [[:work-intervals [:workday-id :interval-start]]
               {:select [:workday.id (t/now)]
                :from :workday}]}

Where I want to end up:

WITH workday_insert AS (INSERT INTO workdays (work_date, work_interval, account_id) VALUES (?, ?, ?) ON CONFLICT (work_date, account_id) DO UPDATE SET work_interval = EXCLUDED.work_interval RETURNING id)
INSERT INTO work_intervals (workday_id, interval_start) INSERT INTO work_intervals (workday_id, interval_start) SELECT id, ? FROM workday_insert

What I have so far:

(-> (hsqlh/with [:workday-insert (-> (hsqlh/insert-into :workdays)
                                     (hsqlh/values [{:work-date (t/today)
                                                     :work-interval (t/new-duration 7 :hours)
                                                     :account-id #uuid "5d90856c-5a48-46ae-8f6e-a06295ea0dfb"}])
                                     (hsqlh/on-conflict :work-date :account-id)
                                     (hsqlh/do-update-set :work-interval)
                                     (hsqlh/returning :id))])
    (hsqlh/insert-into :work-intervals
                       [:workday-id :interval-start]
                       (-> (hsqlh/select :id (t/now))
                           (hsqlh/from :workday-insert))))

Producing:

["WITH workday_insert AS (INSERT INTO workdays (work_date, work_interval, account_id) VALUES (?, ?, ?) ON CONFLICT (work_date, account_id) DO UPDATE SET work_interval = EXCLUDED.work_interval RETURNING id) INSERT INTO work_intervals (workday_id, interval_start)"
 #time/date "2021-05-01"
 #time/duration "PT7H"
 #uuid "5d90856c-5a48-46ae-8f6e-a06295ea0dfb"]

It seems the select and from portion of the insert-into is simply dropped from the query. Not sure why. When running the insert-into function on its own and formatting I get:

INSERT INTO work_intervals (workday_id, interval_start) SELECT id, ? FROM workday_insert

For reference I'm using the latest code of the v2-branch.

com.github.seancorfield/honeysql {:git/url ""
                                  :sha "20cba15da22946d60f53dd6913caba990f7cddb7"

seancorfield18:05:54

@snorremd Can you open an issue on GitHub and I'll take a look at this next week when I'm back at work?

seancorfield18:05:59

In the meantime, have a look in the v2 tests for insert-into / :insert-into -- pretty sure there's an INSERT INTO .. SELECT .. example there somewhere.

snorremd18:05:44

I'll try to make a smallest possible reproduction of the working and non-working code in a repo and file an issue as soon as possible. This should make it easier for you to verify/test the issue. The INSERT INTO .. SELECT .. standalone case works well both with utility functions and the clojure map syntax, so there might be something with the utility functions and combining the insert-into and with statements. Many thanks for the quick reply. 👍

seancorfield19:05:42

@snorremd It's a bug in the insert-into helper.

seancorfield19:05:12

It doesn't expect to have a statement to already merge into.

seancorfield19:05:16

https://github.com/seancorfield/honeysql/issues/324

snorremd19:05:15

Ah, I see. Thanks for looking into that.

seancorfield19:05:15

@snorremd The fix is on the v2 branch if you want to test it?

snorremd19:05:28

Yeah, definitely.

seancorfield19:05:58

New sha 46b3c1773b57ad17efda63f5ceee4f24c221ad0a

snorremd19:05:58

I can verify that it now works as expected.

seancorfield20:05:21

Thanks for finding that bug! This is why we have Beta releases 🙂

snorremd20:05:51

Yeah, no problem! In the future, would you prefer if I just open issues for problems like these? I was not 100% sure if it was a bug in honeysql or some problem with my code, so I used the slack channel.

seancorfield20:05:17

Posting the example here is good. Others may see it and have input, in case there are workarounds or it isn't a bug.

👍 6

seancorfield20:05:55

And my exposure to more "exotic" SQL is somewhat limited so I'm not always sure whether a given syntax is legal, especially for PostgreSQL 🙂

2021-05-01

Channels