Fork me on GitHub
#honeysql
<
2023-01-06
>
David Yang13:01:29

Are there any patterns/libraries that normalize the results of queries with left joins? For example if I do something like select * from blog_posts left join comments I want to get a list of blog_post maps that have comments being a list of comment maps?

orestis13:01:55

Looking how popular ORMs do this (outside of Clojure), it seems they will do two queries. One for blog_posts, then one for comments.

orestis13:01:26

Clojure could easily let you group-by blog_posts.id

David Yang14:01:08

Thanks @U7PBP4UVA - ideally I’d like to get it into a format that I could pump into pyramid so then I could run pull queries

David Yang14:01:22

I was thinking if that’s too hard then I could just do something like (-> :blog_post/id distinct) and then iterate over that

orestis14:01:58

(group-by (fn [row] (select-keys row [:blog_post/id :blog_post/title ,,,]) rows)

orestis14:01:34

This should give you a map with the keys being the blog posts, and for each blog post the sql rows (you would need to map another select-keys there to get only the comment columns)...

David Yang14:01:06

I see - that makes sense - that would give me a map with blog-post maps as keys right?

David Yang14:01:17

but not necessarily preserve order

orestis14:01:05

No, order will be lost since you get a map. Another think you could consider is using aggregate functions - if you use PostgreSQL you can get one row per blog post with the comments as a PG array or a JSONB array.

David Yang14:01:34

cool - alright thanks for the help!

orestis14:01:20

The issues with SQL aggregates is data type loss: jsonb for example is only JSON, so if you have dates and such they will be converted to strings.

David Yang14:01:11

I don’t want to get too in bed with SQL in my queries, I feel like that complicates the queries and also require mentally understanding nuances of pg aggregates

orestis14:01:31

Yep that's the tradeoff 🙂

David Yang14:01:27

I basically want something like Datomic’s pull syntax for SQL - the closest thing I’ve found is https://walkable.gitlab.io/ but looking to do it over honeysql 🙂

orestis14:01:38

I haven't used any of those though.

valtteri20:01:15

I’ve wondered if anyone has built anything related on top of datafy/nav..? Those would let you traverse the schema. https://github.com/seancorfield/next-jdbc/blob/develop/doc/datafy-nav-and-schema.md