Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2023-03-15

Channels

#sql
<
2023-03-15
>
slipset12:03:59

Need a little brain help here. 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} ...]}
ie I would love for the result to be grouped by table. Is this possible with a mix of sql and next.jdbc trickery?

isak14:03:24

Here is how I'd do what you are asking in SQL Server: 

select
	(select t.* 
	 from sys.tables t 
	 where exists (
		select 1 from sys.columns c 
		where c.object_id = t.object_id
	) for json path, without_array_wrapper) as [tables],

	(select c.* 
	 from sys.columns c 
	 where c.object_id in (
		select object_id from sys.tables
	) for json path) as [columns]
for json path, without_array_wrapper
Though for me I'd rather have a tree than 2 flat lists, so I'd do it this way, but depends on use case I guess: 
select
	(select t.* for json path, without_array_wrapper) as [table],
	(select c.* from sys.columns c where c.object_id = t.object_id for json path) as [columns]
from sys.tables t
for json path
This is SQL server, but it is also possible in postgres, I just don't have the syntax.

slipset14:03:45

I’m at: 

(defn group-row [row]
  (->> row
       (group-by (comp keyword namespace first))
       (medley/map-vals #(into {} %))
       (medley/map-vals #(medley/map-keys (comp keyword name) %))))

(defn group-result [r]
  (->> r
       (map group-row)
       (apply merge-with (fn [e1 e2]
                          (if (set? e1)
                            (conj e1 e2)
                            (set (vector e1 e2)))))))
which I can apply to a result from next.jdbc. I’m sure this can be rewritten to some sort of builder fn.

👌 2
isak14:03:39

Fair enough. I've avoided that type of logic, because it breaks down if cardinalities change, or you refer to the same table more than once in different contexts, etc.

seancorfield15:03:14

This is a ticket where I've been thinking about those sorts of coalescing and transforming operations https://github.com/seancorfield/next-jdbc/issues/201 @slipset -- feel free to add ideas there and maybe sketches of code?