Fork me on GitHub
#honeysql
<
2021-02-19
>
agile_geek10:02:11

Morning, I am looking for an example of how to add an as alias to a subquery?

dharrigan10:02:29

user=> (sql/format {:with [[:query {:select [:foo] :from [:bar]}]]})
["WITH query AS (SELECT foo FROM bar)"]

dharrigan10:02:18

If not, maybe some of the tests might give a clue?

agile_geek10:02:24

Thx @dharrigan @borkdude. I think that {:with …}example might work but thru trial and error I found this worked:

{:select [:sub.id]
                 :from
                 [[{:select [:id :name1 :name2]]
                    :from [:table1]
                    :modifiers [:distinct-on :name1 :name2]
                    :order-by [:name1 :name2]} :sub]]}
Suitably anonymised table names and columns plus sub query is simplified.

borkdude11:02:48

Sorry, the video wasn't related to your question, I just posted it here because it was about honeysql

😂 4
agile_geek11:02:38

Information is good, more information is better!

agile_geek11:02:30

I need to look at babashka as I think it may solve a bunch of issues with observability and debugging in my current client.

dharrigan11:02:39

fantastic! 🙂

aratare14:02:32

Hi there. Quick question about using HoneySQL/next.jdbc: Is there a nice or recommended way to handle conversion between dashes and underscores in keywords? There's :allow-dashed-names true but that's just allowing HoneySQL to include dashes in the formatted string. Thanks in advance.

dharrigan14:02:43

In my code, I use dashes for keywords, i.e., tenant-id, that will automatically convert to tenant_id on the rendered SQL that goes out to the db.

dharrigan14:02:46

Not had an issue.

borkdude14:02:08

I actually showed that in the video earlier today and wondered where this was done. Later I saw this is in the format.clj namespace

aratare14:02:36

Ah so it's automatic with no configuration I assume?

dharrigan14:02:43

not had to configure anything 🙂

aratare14:02:05

Splendid! Thanks a lot 🙂

dharrigan14:02:42

you're most welcome

borkdude14:02:49

I recommend using v2 (the alpha one): it has cool new features, like passing in maps as values

dharrigan14:02:53

Yes, I agree. If you're starting off on something new 🙂 I don't think it'll be that long before 2.0.0 Alpha2 becomes a beta, then a final 🙂

dharrigan14:02:14

I've converted my applications to use honeysql v2 (on branches atm) works wonderfully well.

dharrigan14:02:16

and easier too!

aratare14:02:58

> passing in maps as values How do you mean sorry?

borkdude14:02:30

(helpers/values [{:a 1 :b 2}])

borkdude14:02:04

you must omit the column names if you are using that though

borkdude14:02:10

because they will be inferred from the maps

borkdude14:02:45

check the video above if you want to know how this works :)

aratare14:02:53

I think that's already available in the current version? At least that's what the doc is telling me:

(-> (insert-into :properties)
    (values [{:name "John" :surname "Smith" :age 34}
             {:name "Andrew" :surname "Cooper" :age 12}
             {:name "Jane" :surname "Daniels" :age 56}])
    sql/format)

borkdude14:02:16

oh really? I thought that was new, sorry

aratare14:02:30

but totally agree that it is awesome to use 😄

dharrigan14:02:18

@seancorfield would it be possible for honeysqlv2 support create/edit/drop materialized view? which is the same as create view for postgresql?

dharrigan14:02:24

would be supar handy

dharrigan14:02:54

I would replace this (-> (sql/raw (str "refresh materialized view " view " with data"))

dharrigan14:02:07

and (-> (sql/raw (str "create materialized view " view " with data"))

dharrigan14:02:47

I can raise a jira if you are willing, no issues if not 🙂

dharrigan14:02:01

I'm too "enterprisey"

seancorfield15:02:41

Sure, create a GH issue so I don't forget.

dharrigan15:02:22

done. thank you.

seancorfield16:02:50

Quick Q @dharrigan Is create view different from create materialized view?

seancorfield16:02:14

Ah, yes, different syntax. So many options.

dharrigan16:02:34

Not suggesting perhaps to support all the options, maybe even refresh materialized view <blah> with [no] data would be great 🙂