Stylistic question on building queries with conditional components and still being fluent

[Max Kremer]

Hi,

  I searched around in the group but didn't find much in the way of a stylistic guide on using the DSL to write queries with conditional components. The best way to illustrate what I mean is an example:

Let's say may "base" query is 

SELECT * FROM A

But sometimes I need to join B

SELECT * FROM A JOIN B ON (A.id = B.id)

Let's also say that I need to add multiple predicates to the result... again sometimes

SELECT * FROM A JOIN B ON (A.id = B.id)

WHERE A.foo = '1'            -- sometimes we look for these foo values
      AND B.bar = '2'        -- sometimes we look for these bar values  

      AND A.baz = '3'        -- sometimes we look for these baz values

Let's say that the need to add each of the above predicates is dictated by some condition in my code. The problem is the nice clean fluent DSL starts to look messy and ugly because it gets broken up by a bunch of conditionals

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 );

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();

The once pretty fluent style becomes chopped up and hard to follow. The blog post here 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,

Thanks,

Max

[Lukas Eder]

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 <mkr...@trialfire.com>:

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 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

[Max Kremer]

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

[Lukas Eder]

Hi Max,

2016-01-12 16:47 GMT+01:00 Max Kremer <mkr...@trialfire.com>:

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). 

Sure, you're welcome :) Yes, that type system hierarchy may not appear obvious. But it is a good thing to remember that the Step types are only auxiliary types. Whenever you have a syntactially correct epression, that expression will extend the useful super type, e.g. 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.

Hmm, I see.

You should probably look at this problem from a graph theory perspective. Your join predicates are edges between your tables, which are the vertices. Perhaps, you could model your dynamic joins this way: As a graph. And when you "collect" the graph, it will be much easier to decide what tables and what predicates are needed.

I don't know your full requirements, but I'd be very interested in learning more. Perhaps there is something fundamental that could be implemented in jOOQ...?

 

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?

No, right now, you can no longer extract these things from the Query, unless you implement a VisitListener. Tracking objects yourself prior to adding them will be much simpler, though.

 

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...

Well, it is a jOOQ question, because perhaps, we can add a new, awesome feature in this area! 

[Max Kremer]

Lukas,

  Representing the tables as vertexes and joins as edges was a great idea and got me thinking about the problem.  If you could create a "join-graph" a topological sort could be used to traverse the graph and render out a join. I basically used that idea as a starting point but came up with a simpler approach where I "linearise" the joins based on our business logic. This way I only need to represent some of the edges. Some code to illustrate:

/*
Entity is an enum of my tables. This set matches every table to a predicate which returns true when that table is needed
*/

ImmutableSet<Tuple<Entity, Predicate<EventQuery>>> tablePredicates = ImmutableSet.of(
 new Tuple<Entity, Predicate<EventQuery>>(EVENT,    q -> ... return some boolean based on q .. )
 ,new Tuple<Entity, Predicate<EventQuery>>(SESSION, q -> ... return some boolean ..)
 ,new Tuple<Entity, Predicate<EventQuery>>(PERSON, q -> ... return some boolean .. )
 ,new Tuple<Entity, Predicate<EventQuery>>(ALIAS, q -> ... return some boolean
 );

Now I can compute a list of tables that need to be joined based on predicates for each table that operate on "EventQuery" a simple domain pojo that represent the query for our domain

/* Map where a tuple of tables is the keys to for joining the tables. It can be a thought of as a state machine
 * where the two tables in the tuple are a transition.
 * The function joins the second table in the tuple to the existing join.
 */
 ImmutableMap<Tuple<Entity, Entity>, Function<Table<?>,Table<?>>> joinClauses =
 ImmutableMap.of(
 new Tuple<Entity, Entity>(SESSION, PERSON),
 (t) -> t.join( PERSON.getTable()).on("S.person_id = P.id").and( field("P.last_seen", Timestamp.class).between(query.getStartDate(), query.getEndDate()))
 , new Tuple<Entity, Entity>(EVENT, SESSION),
 (t) -> t.join( SESSION.getTable()).on("O.session_id = S.session_id")
 , new Tuple<Entity, Entity>(EVENT, PERSON),
 (t) -> t.join( ALIAS.getTable()).on("O.person_alias = A.alias")
 .join( PERSON.getTable()).on("A.person_id = P.id").and( field("P.last_seen", Timestamp.class).between(query.getStartDate(), query.getEndDate()))
 , new Tuple<Entity, Entity>(EVENT, ALIAS),
 (t) -> t.join( tenantSql.jooqTable(ALIAS)).on("O.person_alias = A.alias")
 );
 

The above map may be hard to read ...it uses Guava's immutableMap.of construct for initializing a map 

ImmutableMap<K,V> myMap = ImmutableMap.of(
        "key1", "value1", 
        "key2", "value2", 
      etc...
        );

The map represent the edges of the graph (or states transitions in an FSM depending on which model you prefer).

Now we're ready to apply our table predicates to figure out which tables are needed 

/*
 * Run through the tablePredicates and get the list of entities that must participate in the join
 */
 List<Entity> joinables = tablePredicates.stream().filter(t -> t._2.test(query) )
 .map(t -> t._1)
 .collect(Collectors.toList());

Finally we use the above list of joinables to actually create the join

//Case for a single table (ie no join necessary) and the starting table
 Table<?> fromClause = tenantSql.jooqTable( joinables.get(0), requiresView(joinables.get(0), query));
 //Build the joining by looking up tuples in the join clause map.
 for (int i = 1; i < joinables.size(); i++){
 fromClause =  joinClauses.get( new Tuple<Entity, Entity>(joinables.get(i-1), joinables.get(i)))
                 .apply( fromClause,  requiresView(joinables.get(i), query) );
 }
 
 return fromClause;

[Lukas Eder]

Hi Max,

Wow, that is really sophisticated! Congrats to the nice ideas.

I'll digest this for a while - and perhaps there's something we can provide out of the box.

In any case, thanks a lot for sharing!

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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.