How to extract used table from SelectConditionStep

Question

I'm extending on my last question I asked about jOOQ. In the Hibernate models the @Filter annotation gets used, and I want to apply this same 'default filter' to the jOOQ queries. As I'm passing a jOOQ query to the nativeQuery(org.jooq.Query query, Class<E> type) I was wondering if it's possible to extract the table (TableImpl<?,?>) used from the FROM clause in the jOOQ query (org.jooq.Query).

This is what I've tried:

private static <E> SelectConditionStep<Record> applyDefaultFilters(Class<E> type, SelectConditionStep<Record> query)
    {
      if (BaseOrganizationModel.class.isAssignableFrom(type)) {
        query
            .getQuery()
            .addConditions(
                query
                    .getQuery()
                    .asTable()
                    .field("organization_id", Long.class)
                    .eq(currentOrganization().id));
        if (SoftDeletableModel.class.isAssignableFrom(type)) {
          query
              .getQuery()
              .addConditions(query.getQuery().asTable().field("deleted", Boolean.class).eq(false));
        }
      }
      return query;
    }

The result is this SQL, which is not what I want. I want it to filter the corresponding table.

select distinct `EventGroup`.*
from `EventGroup`
where (
  ...
  and `alias_100341773`.`organization_id` = ?
  and `alias_17045196`.`deleted` = ?
)

I want this

select distinct `EventGroup`.*
from `EventGroup`
where (
  ...
  and `EventGroup`.`organization_id` = ?
  and `EventGroup`.`deleted` = ?
)

Is this possible at all? And if not, what possible other routes are there? (aside from the obvious passing the table to the function)

Answer 1

Using jOOQ 3.16 query object model API

jOOQ 3.16 introduced a new, experimental (as of 3.16) query object model API, which can be traversed.

On any Select, just call Select.$from() to access an unmodifiable view of the contained table list.

An alternative, dynamic SQL approach for the ad-hoc case

Every time you're trying to mutate an existing query, ask yourself, is there a more elegant way to do this using a more functional, immutable approach do dynamic SQL? Rather than appending your additional predicates to the query, why not produce predicates from a function?

private static Condition defaultFilters(Class<?> type, Table<?> table) {
    Condition result = noCondition();

    if (BaseOrganizationModel.class.isAssignableFrom(type)) {
        result = result.and(table.field("organization_id", Long.class)
                                 .eq(currentOrganization().id));

        if (SoftDeletableModel.class.isAssignableFrom(type))
            result = result.and(not(table.field("deleted", Boolean.class)))
    }

    return result;
}

And now, when you construct your query, you can add the filters:

ctx.select(T.A, T.B)
   .from(T)
   .where(T.X.eq(1))
   .and(defaultFilters(myType, T))
   .fetch();

A generic way to transform your SQL

If you really want to mutate your query (e.g. in a utility for all queries), then a transformation approach might be better suited. There are different ways to approach this.

Using views

Some RDBMS can access session variables in views. In Oracle, you'd be setting some SYS_CONTEXT variable to your organization_id inside of a view, and then query only the (possibly updatable) views instead of the tables directly. MySQL unfortunately can't do the equivalent thing, see Is there any equivalent to ORACLE SYS_CONTEXT('USERENV', 'OS_USER') in MYSQL?

I've described this approach here in this blog post. The advantage of this approach is that you will never forget to set the predicate (you can validate your view source code with CI/CD tests), and if you ever forget to set the session context variable, the view will just not return any data, so it's quite a secure approach.

Together with the WITH CHECK OPTION clause, you can even prevent insertions into wrong organization_id, which improves security.

Using a VisitListener in jOOQ

This is the most powerful approach to do this in jOOQ, and exactly what you want, but also quite a tricky one to get right for all edge cases. See this post about implementing row level security in jOOQ. Starting from jOOQ 3.16, there will be better ways to transform your SQL via https://github.com/jOOQ/jOOQ/issues/12425.

Note, it won't work for plain SQL templates that do not use any jOOQ query parts, nor for JDBC based queries or other queries that you may have in your system, so be careful with this approach as you might leak data from other organisations.

Of course, you could implement this step also on the JDBC layer, using jOOQ's ParsingConnection or ParsingDataSource, that way you can intercept also third party SQL and append your predicates.

This can work for all DML statements, including UPDATE, DELETE. It's a bit harder for INSERT, as you'd have to transform INSERT .. VALUES into INSERT .. SELECT, or throw an exception if someone wants to insert into the wrong organization_id.

Using a ExecuteListener in jOOQ

A bit more hackish than the above VisitListener approach, but generally easier to get right, just regex-replace the WHERE clause of all your statements by WHERE organization_id = ... AND in an ExecuteListener.

To play it safe, you could reject all queries without a WHERE clause, or do some additional trickery to add the WHERE clause at the right place in case there isn't already one.

Using jOOQ's equivalent of Hibernate's @Filter

jOOQ's equivalent of Hibernate's @Filter is the Table.where(Condition) clause. It's not an exact equivalent, you'd have to prevent direct access to T in your code base and make sure users access T only via a method that replaces T by T.where(defaultFilters(myType, T)) instead.

This approach currently loses type safety of the T table, see: https://github.com/jOOQ/jOOQ/issues/8012