Add helper to coalesce joined result sets

[seancorfield]

Is your feature request related to a problem? Please describe.

If you JOIN across multiple tables, you still get a flat hash map result. It would be nice to get structured hash maps back. Similarly, given structured hash maps as input, it would be nice to be able to flatten them to pass to insert-multi! for example. I'm not sure if there's an equivalent use case for update! but having the helper separately would at least allow users to decide how to handle it.

Describe the solution you'd like

Given a hash map with keys from different namespaces and a key identifying something primary in the hash map, return a hash map with keys matching the primary namespace at the top-level and a synthesized key for each other ns that maps to a hash map containing those keys. If a 1:many relationship is identified, those submaps would be coalesced into a vector of hash maps.

{:pet/id 1 :pet/name "Fido" :owner/id 2 :owner/name "George"}
;;=>
{:pet/id 1 :pet/name "Fido" :pet/owner {:owner/id 2 :owner/name "George"}}

If :owner was identified as 1:many, then :pet/owner would be a vector of hash maps and multiple rows with the same :pet/id would be collapsed.

Describe alternatives you've considered

Users can already write their own versions of these helpers.

Additional context

Cases to watch out for: mismatched columns in the primary ns (suggests someone got the primary key wrong); mismatched vector/hash map in the expand version; mismatched primary/secondary namespaces in the expand version.

[orestis]

Isn’t this effectively re-creating aggregate functions + group-by? Postgres allows you to do this generating arrays and JSON objects, it’s useful prior art.

[seancorfield]

@orestis That's fine if you're working with PG but a lot of people aren't and still want this sort of manipulation of flat result sets.

[orestis]

@orestis That's fine if you're working with PG but a lot of people aren't and still want this sort of manipulation of flat result sets.

Oh for sure, I just meant that it would be useful to see how it’s design in Postgres as prior art, to get some ideas (or things to avoid).

I would love to avoid JSON mainly because types (mainly dates) are erased and all you get is strings. So I can definitely see the usefulness in aggregating at the application later.

[slipset]

So imagine I have to tables foo and bar and I executed a query like select f.*, b.* from foo f, bar b where f.col = b.other_col Putting this through straight next.jdbc I get a bunch of rows on a form of:

[{:foo/id
  :foo/name
  :foo/col
  :bar/id
  :bar/name
  :bar/other_col}
  ...]

What I would like to have is something like:

{:foo [{id, name, col} {id, name, col} ...]
 :bar [{id, name, other_col}, {id, name, other_col} ...]}

The following code gets me where I want if I run it over the result from execute!:

(defn group-row [row]
  (->> row
       (group-by (comp keyword namespace first)) ;; group by table name
       (medley/map-vals #(into {} %)) ;; group-by messes up the maps
       (medley/map-vals #(medley/map-keys (comp keyword name) %)))) ;; I wanted non-namespaces keys

(defn group-result [r]
  (->> r
       (map group-row)
       (apply merge-with (fn [e1 e2] ; "transpose the matrix"
                          (if (set? e1)
                            (conj e1 e2)
                            (set (vector e1 e2)))))))

[seancorfield]

I've thought about this on and off for over two years now, and I consider it out of scope for next.jdbc. There are several ways to solve the problem, so it would require a certain number of "knobs and dials", and it falls squarely in the data manipulation space rather than the JDBC space so it's not really next.jdbc's job to handle this: it should be a separate library.