Thanks for your message. For the record of others following this discussion, it's a follow-up to this Stack Overflow question: https://stackoverflow.com/q/78495895/521799
Fetch plans like these aren't a new idea. JPA does indeed already explore such functionality via entity graphs. I've seen similar things in .NET's LLBLGen, and numerous other environments. Spring Data and their whole idea of DDD tries to model database interaction via a set of aggregates and roots, which aren't too different from fetch plans / entity graphs. In a way, GraphQL does this as well. Your quick prototype isn't so different. The most revolutionary attempt so far IMO is Oracle 23ai's JSON-relational duality views concept: https://oracle-base.com/articles/23/json-relational-duality-views-23 The idea is always similar: - You have a root - From this root, you want to optionally navigate a tree of related data It's not "patronising" to look into prior art, because we can learn from others' successes and mistakes, so I'm not quite sure why you'd like others to refrain from making comparisons. If you will, "constructive feedback" is only ever possible if it is informed, and in order to be informed, one needs to combine the experience of others' with additional ideas. Why doesn't jOOQ have this yet? jOOQ's primary objective is to model the SQL language. In SQL, we write queries against the underlying relational data model, which is typically but not necessarily normalised at least in 3NF. This is a fundamental part of jOOQ's philosophy, and thus any DDD-style root/aggregate traversal does seem foreign to jOOQ (and to SQL). The impedance mismatch between DDD and SQL is non-negligible. To this day, I'm not 100% sure whether this is because DDD is fundamentally flawed, or existing attempts at implementing DDD principles are fundamentally flawed, in that they seem to completely dismiss the idea of an underlying relational data model (e.g. by being "data store agnostic"). The way these flaws manifest, in my opinion, is that the existing frameworks are extremely limited in the way they allow for non-entity querying. Everything is always fetched entirely (all attributes, all rows), and hardly any projection, aggregation, computation, and other type of ordinary SQL operation is possible. JPA's entity graphs can hardly do anything (and a beginner will not easily grasp whether they should choose entity graphs, criteria query, jpql, ordinary entity navigation, or just plain SQL!). GraphQL is perhaps the most powerful here, but even there, you can't go far beyond projecting pre-defined column expressions and writing simple filters on them. jOOQ embraces querying, and there's nothing missing technically on a low level from jOOQ anymore since the introduction of MULTISET, nested ROW, etc. to implement arbitrary root/aggregate based views on top of jOOQ: https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-change-how-you-think-about-sql/ Some third parties have long ago started embracing this paradigm and built GraphQL (or similar) implementations on top of jOOQ and its MULTISET, e.g. hasura does this: https://hasura.io/blog/the-ultimate-graphql-for-java-guide See also: https://github.com/jOOQ/jOOQ/issues/10122 Obviously, your criticism here is for MULTISET to be "too complex," and thus queries becoming "not maintainable." And indeed, like many other things SQL, its usage can become repetitive after the 100th query. I don't think this disqualifies jOOQ, it's possible to build user-opinionated libraries on top of jOOQ to remove some of the repetitiveness, but obviously hard to judge without concrete examples. For jOOQ it is *much* more important to get the low level operators right, *first*, and then, if reasonable, build convenience on top of them. The philosophy behind this is: It's worse to say "this is not possible, yet" than to say "this is not convenient, yet" For example, with MULTISET having shipped in 3.15, to-many join path relations, join path correlation, have been very powerful new features in jOOQ 3.19, which make even more sense now: - https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/explicit-path-join/ - https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/implicit-path-correlation/ These building blocks are so powerful (and hardly seen elsewhere!) that I'm positive to be able to tackle even more steps in the near future. A lesser-known feature of jOOQ 3.19 is an improved implementation of inline derived tables, which allow you to create a derived table from any jOOQ Table expression by appending where() (which is only convenience, not a new operator): - https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/inline-derived-tables/ There has also been an idea in the past to allow for other relational operators on arbitrary table expressions, such as Table.select() but it was rejected because the time wasn't ripe for such an operator yet, at the time: - https://github.com/jOOQ/jOOQ/issues/13066 Another rejected feature request for convenience over nesting MULTISET or ROW operators is this one: - https://github.com/jOOQ/jOOQ/issues/13069 These rejections just show that for a game changing feature to be implemented, the time must be ripe, and it just hasn't been yet. I still believe that there's an extremely low hanging but also equally well hidden fruit to be picked here, where by only adding 1-2 convenience methods on top of existing query operators, what you have in mind here is possible to implement in jOOQ in an entirely query based way, without building new "query languages" on top of jOOQ. But it's very hard to discover the exact API that will be the game changer, and not the disappointment like many other attempts at tackling fetching trees of relational data by making compromises on the power that the SQL language would otherwise allow. I believe that Oracle 23ai's JSON-relational duality views are the closest to a thorough solution that unifies both worlds (and even allows for writing back!) The only flaws that I can see so far is that they require: 1. Completely static views. This is a big flaw IMO - in order to change anything in the resulting tree structure, the view has to be changed, for every consumer of the view! With jOOQ, this is all arbitrarily dynamic. 2. The use of JSON in the projection, when this could be done with ORDBMS capabilities of Oracle. Oracle has the standard SQL MULTISET operator as well, though I understand that it was probably far easier to implement and reaches a bigger target audience from the start. But it is a very well-designed approach nonetheless and it illustrates where jOOQ will go in the future. *So, to summarise:* 1. jOOQ will continue to add more convenience for nesting data structures in queries with even more focus on path traversal and projections / selections / correlations based on paths 2. jOOQ will not offer an alternative, "opinionated" API on top of jOOQ that solves only root/aggregate based entity traversal (and thus not repeat the mistake of countless ORMs of separating querying from entity interactions, leading to confusion and limitation). Any jOOQ solution will be fully integrated into the entirety of the query language in a way that would even be idiomatic for SQL itself. An example of 2) is the DAO API, which I regret so much. It is such a terrible bikeshed, being the only really opinionated thing in jOOQ. Sure, users like it, and it can't be removed again for this reason. But it is not well-designed. It was a "quick win," that will never implement the features users ultimately desire. Jakarta Data is making the same mistake right now by copying Spring's Repository and thus opening up endless bikesheds instead of truly innovating, at least in my opinion. In order to respond to your criticism, jOOQ's approach is the *only* way to result in code: - That is not hard to read - That is consistent - That is easy to maintain Users can build opinionated APIs on top of jOOQ if they think its approach is not convenient enough, though, incidentally, well-designed convenience within the jOOQ query system may lead to satisfactory results for not-extremely-opinionated users nonetheless. On Sat, May 18, 2024 at 6:41 PM Fabio Trabattoni <fabio.trabatt...@gmail.com> wrote: > Hello everyone, > > I hope this message finds you well. I’d like to share an idea for a > potential feature request, inspired by a brief discussion on SO with Lukas. > > *INTRO* When starting a Java Spring project, developers often use JPA for > data mapping because it allows for quick and straightforward setup. > However, as the project grows, they start encountering performance issues > like the infamous N+1 problem et similia. > > At this stage, devs make one of the two things happen: > > 1. Refactoring JPA Mappings: They push themselves to restructure > mappings and use more efficient querying mechanisms with JPA. > 2. Switching to Alternatives: They opt for alternatives like JOOQ or > QueryDSL to handle their queries. > > Unfortunately, most of the times ,both approaches result in code that is > hard to read, inconsistent, and difficult to maintain. At least in my > experience. > > *THE IDEA *Here’s the idea: a strongly opinionated way of defining > queries that starts from the basic thought steps when dealing with data > querying in Java. The focus would be on defining a query plan with filter > criteria and specifying what to load and how it should be loaded, possibly > even in a recursive fashion. > > 1. Specify Filters: A straightforward mechanism to define and apply > filters to the data set. > 2. Specify What to Fetch: A clear way to determine which fields to > retrieve. > 3. Recursive Query Plan: The ability to define a query plan that can > handle recursive fetching and recursive criteria, ensuring that related > data is queried efficiently. > > At a glance it could look something like this: > https://github.com/thestroke82/leanquery/blob/master/src/main/java/org/frappa/leanquery/controller/CustomerController.java > I've worked with a custom solution very much like this in the past and > observed it performing its job remarkably well. The strongest advantage of > such an approach is that as the codebase grows, readability remains > practically constant, significantly improving maintainability. *CONCLUSION > *The ideas are now out in the open, both in words and code (see the > GitHub link above). I’m eager to hear your thoughts and advice, and I hope > we can work together to evolve this concept into a formal feature request > for JOOQ. > > One final request: Please refrain from comments that patronize or dismiss > the idea with statements like "JPA already does that" or "X does that too." > Instead, I’m looking for constructive feedback and suggestions on how we > can make this feature a valuable addition to the JOOQ ecosystem. > > Thank you for your time and consideration. I look forward to your feedback > and collaboration! > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to jooq-user+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/99bbd0c3-e5af-4ee7-a897-7ff869098288n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/99bbd0c3-e5af-4ee7-a897-7ff869098288n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4Zi8aPYHDukwqFZkv63ueJir2gY4C3ZimEibbOZkborQ%40mail.gmail.com.
