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 2016-01-14 20:26 GMT+01:00 Max Kremer <[email protected]>: > 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; > > > > On Tuesday, January 12, 2016 at 12:19:40 PM UTC-5, Lukas Eder wrote: >> >> Hi Max, >> >> 2016-01-12 16:47 GMT+01:00 Max Kremer <[email protected]>: >> >>> 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! >> > -- > 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.
