Hi Lukas, As always thank you for the prompt and detailed reply. Looking at the JOOQ type system is becomes clear why the need for various local types are not needed (everything is a subtype of Table).
Your example with generating conditions is very similar to how I do it now... However its the JOIN stuff that's giving me grief right now. Joins are more complicated because of the many permutations of tables that can participate in the join, and depending on which tables participate I need to perform the join differently. Let's say for example you have 3 tables Events, Sessions and People. There are 2^3 - 1 = 7 valid combinations of these 3 tables. (minus one for zero tables). Once a table is joined in the query I don't know if there is a good way to get at that information after the fact (getQuery?). For example, if I'm joining Session it would be good to know if Person has already been joined because then I can do *Session.personid = Person.id*, but if it is only Event that exists in the join then I need to do *Event.sessionid = Sessions.id *and if no tables exists so far then there is join and I'm simply querying the Session table. Is there a way to get a list of the tables in the join or do I need to wrap the query generation logic with something that keeps track oft this for me? I'm trying to avoid building a giant case statement that accounts for all the permutations. I suppose the questions is less of a JOOQ questions and more of a general design question... note: Part of this is due to performance considerations (I'm using Redshift). Thanks, Max On Tuesday, January 12, 2016 at 5:08:46 AM UTC-5, Lukas Eder wrote: > > Hi Max > > Thank you for your request. I do see that this is not yet properly > documented in the manual, although it does deserve a full section. I've > registered a feature request for this: > https://github.com/jOOQ/jOOQ/issues/4931 > > In general, you should never really need to have any local variables of a > XXXStep type. For example, your dynamic JOIN clause: > > > 2016-01-11 19:55 GMT+01:00 Max Kremer <[email protected] <javascript:>> > : > >> >> >> TableOnConditionStep fromClause = table("B") >> .join(table("B")).on("A.id = B.id"); >> >> >> if (joinTableC == true){ >> fromClause = fromClause.join(table("C")).on("B.id = C.d"); >> } >> >> >> dsl.select( ... ) >> .from( fromClause ); >> >> >> > Instead, write: > > Table<?> table = table("A")... > > > This isn't much of an immprovement in terms of lines of code. But by using > the Table<?> type, you indicate much more clearly what the type really > means, than if you were using the TableOnConditionStep type. > > >> Another example >> >> SelectOnConditionStep statement = select(Temperatures.VALUE, >> Temperatures.TIMESTAMP) >> .from(Temperatures) >> .where(Temperatures.VALUE.greaterOrEqual(0)); >> >> >> if (filter.hasMaxValue()) >> statement.and(Temperatures.VALUE.lessOrEqual(filter.getMaxValue()); >> >> >> if (filter.hasLocation()) >> statement.and(Temperatures.LOCATION.equal(filter.getLocation()); >> >> >> // And so on with the rest of filters to end with: >> statement.orderBy(Temperature.TIMESTAMP) >> .fetch(); >> >> >> > Here, the problem is more obvious. Why add predicates dynamically to the > weird SelectOnConditionStep, when you can construct a predicate rather > easily as follows: > > Condition condition = Temperatures.VALUE.ge(0); > > if (filter.hasMaxValue()) > condition = condition.and(Temperatures.VALUE.le(filter.getMaxValue()); > > if (filter.hasLocation()) > condition = > condition.and(Temperatures.LOCATION.eq(filter.getLocation()); > > // Only now construct the query: > select(Temperatures.VALUE, Temperatures.TIMESTAMP) > .from(Temperatures) > .where(condition) > .orderBy(Temperatures.TIMESTAMP) > .fetch(); > > > Of course, you're completely free how and where to construct your query > parts. For instance, you could be doing this instead: > > > public Condition condition(Filter filter) { > Condition condition = Temperatures.VALUE.ge(0); > > if (filter.hasMaxValue()) > condition = > condition.and(Temperatures.VALUE.le(filter.getMaxValue()); > > if (filter.hasLocation()) > condition = > condition.and(Temperatures.LOCATION.eq(filter.getLocation()); > > return condition; > } > > // Only now construct the query: > select(Temperatures.VALUE, Temperatures.TIMESTAMP) > .from(Temperatures) > .where(condition(filter)) > .orderBy(Temperatures.TIMESTAMP) > .fetch(); > > > The once pretty fluent style becomes chopped up and hard to follow. The >> blog post here >> <http://www.programania.net/diseno-de-software/functional-trick-to-compose-conditions-in-jooq/> >> >> discusses this in more detail and presents an elegant solution. I'd like to >> hear how other people are handling this... Also wondering if future >> versions of JOOQ will have this in mind, >> > > The blog post you linked shows a nice functional approach, which is > roughly equivalent to the procedural approach that I've shown. We do have a > new feature for jOOQ 3.8, where we support the "Query by Example" pattern: > https://github.com/jOOQ/jOOQ/issues/4735 > > This will allow to construct a Condition from a Record via > DSL.condition(record). It will work only to some extent for you, because > Query by Example can only build Field.equal() predicates. > > Another approach would be to allow for jOOQ API to "leak" into your UI. In > the end, you might not be gaining that much from duplicating everything you > already have in your database schema into a Filter object that is > essentially just the same as a jOOQ Condition. That would certainly save > you some work, although I do appreciate that some people cargo cult the > clean three-tier layering approach, in case of which my suggestion would be > a heresy :-) > > I hope this helps. If you see any possible improvements for jOOQ, just let > us know. There's always an improvement to be made! > Lukas > -- 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.
