Hacker News Reader

Comment by win311fwg | Hacker News Reader

> because SQL offers no good way to get nested results

Because you don't need nested results. You can fire off another query.

Although I am venturing to guess that you feel you need nested queries 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 that. SQL was specifically designed to be run on the same machine as your application.

If you need to present the data to network clients where nesting is necessary for performance reasons, turn your application into the network database service that produces nested data (the "API server" as the kids call it). Leave the SQL as an implementation detail hidden behind the scenes.

View on HN

I do want to be presented with nested results in the end, though!

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.

> my data processing is now split up in two places.

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 at the interface point, 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 actually how SQL is designed to be used, not the hacks that they are.

> I have not seen this in any other general interface for relational databases.

Oracle has their thing. Its name escapes me at the moment. Postgres has json_agg/jsonb_agg and similarly SQL itself has array_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.

Reply on news.ycombinator.com