Hi Oskar, I've left this discussion for others to answer, but it looks as though there hasn't been much feedback so far.
We are aware of the lack of "join deduplication" support being a certain limitation to jOOQ (or to any SQL oriented approach). As I told you before off the list, the main reason for this problem is the fact that by using JOIN or LEFT JOIN, you are effectively denormalising your result set, bringing it into a tabular form that doesn't contain the full information of your original join path anymore. Automatic "deduplication" isn't really possible even if it appears to be in very simple cases. HQL and JPQL do not have this problem, because they are languages tailored precisely to navigate object graphs and you have no control over the number of SQL queries that are effectively generated under the hood in order to materialise such an object graph. While this can be very convenient, their approach leads to many other missing parts, e.g. UNIONs, FULL OUTER JOINs, CTE and derived tables and many other SQL features that can only be done with SQL, which again doesn't work well with second-level caching, which again is needed to navigate, and more importantly, persist the object graph efficiently. The way more SQL-oriented APIs usually work around this limitation is by providing synthetic join operations that are not really SQL joins. We've been looking into this kind of solution in the past but haven't come around to implementing it. For instance, LLBLGen calls these things "prefetch paths": http://www.llblgen.com/documentation/4.2/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_prefetchpaths_adapter.htm The can of worms you refer to is probably inevitable. Consider the following section from the SQLAlchemy manual: The Importance of Ordering A query which makes use of subqueryload() <http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#sqlalchemy.orm.subqueryload> in conjunction with a limiting modifier such as Query.first() <http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.first> ,Query.limit() <http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.limit>, or Query.offset() <http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.offset> should *always* include Query.order_by() <http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.order_by> against unique column(s) such as the primary key, so that the additional queries emitted by subqueryload() <http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html#sqlalchemy.orm.subqueryload> include the same ordering as used by the parent query. Without it, there is a chance that the inner query could return the wrong rows As soon as a framework adds synthetic clauses to the SQL language, those clauses will have a huge impact on all of the language. This is true for jOOQ more than for any of the mentioned SQL APIs, as jOOQ really models all of SQL as a language. Long story short, we have recognised this limitation and we're thinking about it and we also appreciate all feedback from the community with respect to possible solutions. But it is not at all an easy problem to solve thoroughly. As a library vendor, there's no such thing as a "not automatic but just something" solution, I'm afraid. Having said so, if there is a quick win where we could add some convenience API, that'll do of course. We're constantly adding new methods that do similar things as the existing ones but with less code. Regards, Lukas 2014-11-24 12:33 GMT+01:00 Oskar Norrback <[email protected]>: > Hello guys! > > We are a new startup and decided to try jOOQ instead of JPA in the backend > of our first product and we haven’t regretted it so far. We wrote a blog > post summarizing our experiences and thoughts with jOOQ here > <http://teonos.com/blog/java/development/2014/11/10/experiences-with-jOOQ.html> > . > > The biggest minus with using jOOQ so far has been the “join deduplication > boilerplate” issue I mention in the blog post. > > Does my suggestion of adding some basic association support between the > generated Record types make sense to anyone else? E.g. using my blog > example: that a CustomerRecord can hold a list of OrderRecords? > > The next step would then be that I could automagically convert a Result of > a CUSTOMER.leftJoin(ORDER) query into CustomerRecords with the orders > populated. Currently I have to write this logic myself, repeatedly in > slight variations and it feels like it's boilerplate that could perhaps > come with the library. Looks like at least some of the Python ActiveRecord > implementations support associations to some extent (SQLObject > <http://sqlobject.org/SQLObject.html#one-to-many-relationships>, > SQLAlchemy <http://docs.sqlalchemy.org/en/rel_0_9/orm/loading.html>). I'm > not looking for automatic fetching of associations (and the can of worms > that comes with), just something that would simplify the step of converting > JOIN results into Records with associations. > > > Regards, > > Oskar Norrback > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
