Clojurians Log v2
sql
Is there some builtin facility for placing joined data in a nested map? e.g., say I have a book table that has a column author_id. The author_id is a foreign key to the author table. I’d write a sql query like select * from book join book.author_id = author.id. I’d like the returned data to look like this:
{:book/title "foo"
:book/author {:author/name "bar"}}
No, but there's an issue open on next.jdbc for a utility to do that. There are just a lot of open issues about how exactly it should work...
Haha yeah I would be very curious what the impl looks like. Are folks doing this now?
There is no impl yet and I don't know what folks are doing in this area. But it gets requested from time to time, usually without enough detail for me to either design a solution or critique the proposal 🙂
There are a few times at work I would have liked something like this but mostly I just roll my own and there isn't always enough similarity between use cases to extract a clear generic pattern -- or at least not an elegant one in terms of all the knobs and dials folks are likey to want for such an operation...
Probably comes from folks that have used Datomic where this pattern is common. So curious, foreign references are very common in the sql world. Since you don’t see this come up often, I imagine you design the call differently. Would you instead first get the book, issue a second call to get the author, and then merge the two?
No, I'd do the JOIN. The open questions are all around how exactly to identify FKs and merge multiple records and how much schema knowledge is needed and how that should be provided/obtained. Is it a pure data transform? Should it be tied to DB schema introspection? etc.
Oh you would do it? And it comes up rarely for you?
As I said, I roll my own because each situation seems domain-specific. I've been looking for patterns -- the open issue on next.jdbc is quite old now -- but not finding enough commonality without a mess of knobs and dials. And designing a function or even a whole library for that is hard.
https://github.com/seancorfield/next-jdbc/issues/201 has been open for well over a year at this point.
I see. I guess I was more asking from your experience, this does not come up often?
I really like the way https://vincit.github.io/objection.js/ handles this (A not so ORMy ORM). I would have liked to see a similar, more clojury approach about this - but maybe/likely it could even be a separate package.
At the core they basically have a definition of how tables are related (what keys) and have a few helpers around those definitions like graph fetch queries (nesting) and joining related tables. I could imagine a library built around honeySQL for something like this.
@kenny I don't have a sense of how often it comes up, to be honest. It has come up multiple times, but I'm dealing with nearly 140K lines of Clojure that has been developed over 12 years so keeping track of a use case like that over time is kinda hard.
@kenny you can do it using subqueries + JSON queries. Here is an example In SQL Server (different but similar in postgres):
select a.*,
JSON_QUERY((select * from @People b where a.dad_id = b.id for json path, without_array_wrapper)) as dad,
(select * from @People b where a.id = b.dad_id for json path) as children
from @People a
for json path