JOOQ Derived Tables

Question

I'm trying to express the following SQL in JOOQ. However, I either have massive problems with types using derived tables or I get something that compiles but either fails at SQL level or even in JAVA. Can anyone give me an idea how to use derived tables correctly in this context?

SELECT
    id,
    ROUND(num_realized / num_requirements, 2) AS realized_percent,
    ROUND(num_requirements / max_req, 2) AS activity_percent
FROM (
    SELECT
        requirement.project_id AS id,
        COUNT(requirement.id) AS num_requirements,
        COUNT(requirement.realized) AS num_realized
    FROM
        requirement
    GROUP BY
        requirement.project_id) AS stats
    CROSS JOIN (
        SELECT
            MAX(num_requirements) AS max_req
        FROM (
            SELECT
                requirement.project_id AS id,
                COUNT(requirement.id) AS num_requirements,
                COUNT(requirement.realized) AS num_realized
            FROM
                requirement
            GROUP BY
                requirement.project_id) AS stats) AS req_max 

The statement works fine when applied in SQL but I can't get this expression into JOOQ.

My latest try was using

Table<Record3<Integer, Integer, Integer>> stats =
DSL.select(
    REQUIREMENT.PROJECT_ID.as("id"),
    DSL.count(REQUIREMENT.ID).as("num_requirements"),
    DSL.count(REQUIREMENT.REALIZED).as("num_realized")
).from(REQUIREMENT).groupBy(REQUIREMENT.PROJECT_ID).asTable("stats");

Table<Record2<Integer, Integer>> req_max =
    DSL.select(
        stats.field(0),
        DSL.min(stats.field(1))
    )
    .from(stats).asTable("req_max");

However I'm getting error: incompatible types:

Table<Record2<CAP#1,CAP#2>> cannot be converted to Table<Record2<Integer,Integer>>

I tried a bunch of different techniques, including defining the Datatype and using .field(String, Datatype) instead of using "Records" but whatever I'm doing, its either not compiling or fails while executing in an unknown error.

I'd be glad for any help.

Answer 1

Using window functions instead

In general, self-joins should be avoided whenever you can. In many cases, window functions can solve a problem much more elegantly than aggregations in nested queries. If you were using MySQL 8, your query could be re-written as:

SELECT
    requirement.project_id AS id,
    ROUND(COUNT(requirement.realized) / COUNT(requirement.id), 2) AS realized_percent,
    ROUND(COUNT(requirement.id) / MAX(COUNT(requirement.id)) OVER(), 2) AS activity_percent
FROM
    requirement
GROUP BY
    requirement.project_id    

Notice the MAX(..) OVER() window function, which can aggregate ordinary aggregation functions as explained here. I know you're using MySQL 5.7, which has no window function support yet, but for completeness' sake, this answer needs a window function based solution - maybe as a motivation to upgrade :-)

A lot of complex jOOQ queries can be made simpler by making the underlying SQL query simpler in the first place.

The derived table problem you ran into

The problem is your usage of stats.field(0) and stats.field(1). The method signature is

Field<?> field​(int index)

There's no way jOOQ could provide you with type safety when you access a table's columns by index, hence the return type is Field<?>, where the column type is a generic wild card. There are a few solutions here:

1. Avoid the type safety if you don't need it. You can always declare your table Table<?> req_max. From your example alone, I'm not sure if you need any type safety here

2. Extract your field references as local variables. Instead of embedding e.g. the id column in your stats table, why not:

   Field<Integer> id = REQUIREMENT.PROJECT_ID.as("id");
   Field<Integer> numRequirements = DSL.count(REQUIREMENT.ID).as("num_requirements");
   Field<Integer> numRealized = DSL.count(REQUIREMENT.REALIZED).as("num_realized");
   

   and then use it like this:

   var stats =
   DSL.select(id, numRequirements, numRealized)
      .from(REQUIREMENT)
      .groupBy(REQUIREMENT.PROJECT_ID)
      .asTable("stats");
   
   var reqMax =
   DSL.select(stats.field(id), DSL.max(stats.field(numRequirements)))
      .from(stats)
      .asTable(reqMax);