Inspecting an ad-hoc query to determine which tables have been joined-in already

[eli...@thirdchannel.com]

Hello jOOQ users,

My goal is to allow a user to filter and order returned data based on a variety of fields. For this, I am using jOOQ to construct an ad-hoc query. For optimal performance, I don't want to join any tables unless they are necessary for the user-specified filtering and ordering configuration. However, if I put redundant joins to the same table, I will get a BadSqlGrammarException ('table name "address" specified more than once'). How can I inspect the query object to determine which tables have already been joined?

To clarify what I'm trying to do, I have included a simplified example below. How should I write the commented-out "address table not yet joined?" condition?

// I start with a base query
Select query = dsl.select(field('name')).from(table('person'));

// ... later, I join in tables needed for my 'where' clause
if(filter.city != ""){
  query = query.join(table('address')).on(field('person.address_id').equal(field('address.id')));
}

// ... elsewhere, I join in tables needed for my 'order by' clause
if(order.byCity && /* address table not yet joined? */ ){
  query = query.join(table('address')).on(field('person.address_id').equal(field('address.id')));
}

// ... and finally, I will construct the actual clauses
if(filter.city != ""){
  query = query.where(field('city').equal(filter.city));
}
if(order.byCity){
  query = query.orderBy(field('city').asc());
}

[Lukas Eder]

We'll be adding more query introspection capabilities for these use-cases in jOOQ 4.0, when we re-implement the DSL API / model API separation. Unfortunately, until then, you'll have to keep track of already joined tables yourself, e.g. using a HashSet.

Another way to solve this in jOOQ 3.8 (hopefully) would be by using implicit joins. If we find a performant solution to this problem, we'll support filtering on things like person.address.city, in a similar way that HQL / JPQL allow this. However, this feature will obviously depend on foreign key information to be available, so it won't work with plain SQL usage.

I hope this helps,

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.