Fork me on GitHub
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?


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


Clojure could easily let you group-by

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


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


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


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!


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


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 but looking to do it over honeysql 🙂


I haven't used any of those though.


I’ve wondered if anyone has built anything related on top of datafy/nav..? Those would let you traverse the schema.