Mine is a smaller attempt. I didn't want to build a general query engine, I just wanted something that made sense inside a specific domain. The primary goal is to force the devs to act in a well-defined pattern.
The general apporach is very similar though: 1) Create a simple grammar in the form of OOP classes 2) Instrument an engine that translates 1) for the lower levels Il giorno lunedì 20 maggio 2024 alle 13:38:58 UTC+2 5kil...@googlemail.com ha scritto: > Hello Fabio, > > i just want to share my similar use-case, just to give a perspective to a > different way to solve dynamic querying. > > - 1. a common Query-Model > - 2. a Mapper that maps the Query-Model to Dynamic Jooq > - 3. a Repository for each use-case that resolves data with help of > MULTISET, Pagination, Filtering etc. > > The common Query-Model (FkQuery). > - See: > https://github.com/funkrusher/fk-framework-quarkus-jooq/tree/main/_core/fk_core/src/main/java/org/fk/core/query/model > > The mapper that maps the Query-Model to Dynamic Jooq: > - See: > https://github.com/funkrusher/fk-framework-quarkus-jooq/blob/main/_core/fk_core/src/main/java/org/fk/core/query/jooq/FkQueryJooqMapper.java > > The Repository: > - See: > https://github.com/funkrusher/fk-framework-quarkus-jooq/blob/main/_modules/fk_product/src/main/java/org/fk/product/repository/ProductRepository.java > > --- > > I think my solution is not recursive or as strong in regards to dynamic > flexibility, but it does also provide a dynamic way to interact with the > Repository > to filter specific data in a Table-Relationship (Main-Table, Subtables). > Its not as strongly typesafe though, because the query-model can also > contain invalid selectors, > because it can also come directly from frontends for example. > > The Repository here is providing a static (always the same) Select > Statements on multiple tables. In your solution you would dynamically build > a different > Select, so your way to do this would be more performant and more specific, > while this approach is more uniform but less dynamic. > > I found your approach interesting, because a clean way to define > "Fragments" and dynamically use them is surely helpful. > lukas...@gmail.com schrieb am Montag, 20. Mai 2024 um 10:14:05 UTC: > >> 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.tr...@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+...@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/d0f1b97b-a2c6-44ee-bb96-0778946be9fen%40googlegroups.com.
