Fork me on GitHub
Slack Archive

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

2024-05-13

Channels

#honeysql
<
2024-05-13
>
sheluchin16:05:53

Anyone hooked up HoneySQL for use in a https://docs.getdbt.com/ project? I'm thinking something along the lines of writing the models in HoneySQL, compiling them into DBT models as SQL and then letting DBT do the rest. Shouldn't be hard, I'm just wondering if anyone has something going.

p-himik16:05:58

If it's regular SQL then there are no show-stoppers. There's no need for any prior art here, you can simply rely on HoneySQL and its docs.

sheluchin16:05:00

It's almost regular SQL, just with Jinja templating tags involved. Would need to register a HoneySQL fn to create {{ ref('model_name') }} tags and similar. Besides that there are a bunch of DBT CLI commands like compile, run, etc. that expect SQL files in certain places. I'd like to have it so I only have to touch HoneySQL files and the rest happens seamlessly.

Noah Bogart20:05:18

i'm trying to get the :join https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1126/doc/getting-started/sql-special-syntax-#join working but i'm struggling a bit. i want 

SELECT *
FROM foo
LEFT JOIN (populations pm INNER JOIN customers pc ON pm.id = pc.id AND pm.other_id = pc.other_id)
ON foo.fk_id = pm.id
I have 
(-> (hh/select :*)
    (hh/from :foo)
    (hh/left-join [[:join [:populations :pm]
                    (hh/join [:customers :pc]
                             [:and [:= :pm.id :pc.id]
                              [:= :pm.other_id :pc.other_id]])]]
                  [:and [:= :foo.fk_id :pm.id]
                   [:= :foo.other_fk_id :pm.other_id]])
    (sqlv2/format)
    (println))
but this produces 
[SELECT * FROM "foo" LEFT JOIN JOIN(POPULATIONS("pm"), (INNER JOIN "customers" AS "pc" ON ("pm"."id" = "pc"."id") AND ("pm"."other_id" = "pc"."other_id"))) ON ("foo"."fk_id" = "pm"."id") AND ("foo"."other_fk_id" = "pm"."other_id")]

✅ 1
Noah Bogart20:05:10

I can't seem to get the :join call right such that it correctly nests the initial table and alias

Noah Bogart20:05:34

huh, it seems that even the example doesn't work for me: 

(sqlv2/format {:join [[[:join :tbl1 {:left-join [:tbl2 [:using :id]]}]]]})
; ["INNER JOIN JOIN(\"tbl1\", (LEFT JOIN \"tbl2\" USING (\"id\")))"]

Noah Bogart20:05:48

is this determined by a dialect setting? we're using: 

(sqlv2/set-dialect! :ansi :quoted true)

p-himik20:05:32

Are you using the most recent version of HoneySQL?

Noah Bogart20:05:27

good call, we're using 2.4.1011. lemme update and see if that fixes it

Noah Bogart20:05:50

thanks, should have checked the version first

p-himik20:05:00

Can't quite achieve what you want because I can only get populations(pm) and not populations pm or populations as pm.

👍 1
Noah Bogart20:05:33

i can work around that, i'm just glad to get the nested join working

p-himik20:05:03

How would you work around that?

Noah Bogart20:05:33

just not use an alias in this case

p-himik20:05:54

An alternative is to use :raw. But seems like it's a limitation of HoneySQL.

Noah Bogart20:05:44

[:join [:raw "populations pm"] ...] does the trick

Noah Bogart20:05:55

not ideal but much nicer than no aliases lol