Don't try to get nested results, fire off another query.
Although I would venture to guess you feel you need that because you're doing doing something stupid like trying to send SQL queries over the network where the cost of firing off another query becomes a great performance burden. But the answer to that is don't do stupid things. SQL was specifically designed to be run on the same machine as your application.
As I mentioned in the previous post, I am aware that this can be done with multiple queries and post-processing, but then I am implementing what essentially amounts to a join myself (of maybe I have something like an ORM do it for me). In any case, my data processing is now split up in two places.
And the point of Ultorg is that it provides an interface that does this post-processing into a nested representation. Except maybe for Gel (mentioned somewhere else here), I have not seen this in any other general interface for relational databases. If SQL supported nested results, this surely would be different.
It is a higher level concern so that isn't a bad thing. SQL is focused on giving you a view of your data. Whereas you want to combine many different views. That is where composing many different queries should be seen as quite natural, not a shortcoming of SQL. If that composition isn't natural, you may want to take a closer look at your application's architecture.
> but then I am implementing what essentially amounts to a join myself
Joins and aggregated views are very different concepts. I think this misunderstanding, which is one I've seen many times before, stems from the hacks used to work around the aforementioned problems. Many have come to believe those hacks aren't the hacks that they are, but actually how SQL is designed to be used.
> I have not seen this in any other general interface for relational databases.
Oracle has their thing. Its name escapes me. SQL itself has json_agg/jsonb_agg. Lots of options out there.
But it's not only a question of if it can be done, but if it should be done. There are a lot of other advantages to moving query aggregation out into another application. There is a reason why we stopped sticking SQL servers directly in front of the clients and it isn't just because of SQL using a table representation.
I can write a single join in SQL to get this, but then the customer information will be repeated on each purchase line. For my report I will also need to group orders from the same customer together. So now I have asked my database to duplicate the customer information across rows, only to deduplicate the data in my application again.
I can create two SQL queries, one for the customers, one for the purchases. I will still need to use joins (or WHERE-EXISTS clauses), because I do not want to extract all my customers and/or purchases from the database. Then, in the application I need to combine the results of those two queries. This will require me to implement the same join (or at least, its matching logic) yet again.
Neither of these really seem all that great to me. Of course, one can work around these limitations, many database adapters do. But in my opinion, this does not mean that SQL should not have provided a native solution for this. (There are tons of other things it could have left out as well, by that logic.)
The agg functions are probably the closest thing we have. I have mentioned my gripe with the JSON versions before, they lose valuable type information due to JSON's limited data model. The array_agg version is usually not very ergonomic on the receiving end. Both are also more cumbersome to write in SQL than I would like.
For what it's worth, I also think that SQL teaching generally emphasizes joins as the natural way to work with relational databases way to much. The agg functions might appear almost as an afterthought at the very end. (To be fair to the creators of teaching materials, to me, they also appear as an afterthought in SQL itself.)
Yes, this maps quite nicely to SQL. Consider this pseudocode:
for customer in (select * from customers) {
println(customer.name)
for purchase in (select * from purchases where cid = customer.id) {
println("\t"+purchase.date+": "+purchase.amount)
}
println()
}
Join has a place. This is not it. Joins are for joins in the mathematical sense, not view aggregation.
> I also think that SQL teaching generally emphasizes joins as the natural way to work with relational databases way to much.
Yes, agreed, and I am suggesting that is because of the hacks required to work around the reasons why you cannot write the above code under shitty implementations, which has lead to many conflating joins and view aggregation.
If you want something like the above code except using a declarative language instead then you can throw something like GraphQL in front of it. What are you really gaining by turning SQL into GraphQL?
Alternatively, you might use functions to aggregate in the query, but they're not standard, as far I know, which means that interfaces in programming languages often do not support them. The best bet might be aggregating into JSON, but this removes quite a bit of the type information that values in SQL usually carry. (Also, the aggregation functions are generally less pleasant to write than standard joins.)