Clojurians Log v2
honeysql
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.
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.
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.
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(-> (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))[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")]I can't seem to get the :join call right such that it correctly nests the initial table and alias
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\")))"]is this determined by a dialect setting? we're using:
(sqlv2/set-dialect! :ansi :quoted true)Are you using the most recent version of HoneySQL?
good call, we're using 2.4.1011. lemme update and see if that fixes it
https://github.com/seancorfield/honeysql/releases/tag/v2.4.1026 there we go, i was one version behind for this
thanks, should have checked the version first
Can't quite achieve what you want because I can only get populations(pm) and not populations pm or populations as pm.
i can work around that, i'm just glad to get the nested join working
How would you work around that?
just not use an alias in this case
Ah. :)
An alternative is to use :raw.
But seems like it's a limitation of HoneySQL.
[:join [:raw "populations pm"] ...] does the trick
not ideal but much nicer than no aliases lol
I considered it a bug that [:populations :pm] did not work to create an alias. Fixed on develop and a new snapshot will be available shortly. I'll probably cut a new release this weekend.
