CODEWITHSUNDEEP
javasqljooq
TiemenSchut
TiemenSchut

Reputation: 266

jooq nested select and type safety

I'm trying to write the following sql in jooq:

SELECT SUM(`sum`) AS `total`
FROM(
        SELECT
        MIN(`num_views`) AS `sum`
        FROM `fact_wishlist`
        GROUP BY `id`
) AS t

I've found something that works with this:

Table<Record2<String, Integer>> nested = 
    table(create.select(MYTABLE.ID,
    min(MYTABLE.NUM_VIEWS)
            .as("views"))
    .from(MYTABLE)
    .groupBy(MYTABLE.ID));

return create.select(sum((Field<Integer>) nested.field("views")))
    .from(nested)
    .fetchOne().value1().longValue();

However, somehow I've lost the type safety. I feel I'm doing something obvious wrong, but I'm not seeing it. Any ideas on how this could be improved?

Upvotes: 3

Views: 596

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

Unfortunately, you cannot achieve 100% type safety with derived tables in jOOQ, as the Java compiler cannot really prove that a derived table contains a column. But your usage can definitely be improved by reusing the "views" field. Just assign it to a local variable.

Field<Integer> views = min(MYTABLE.NUM_VIEWS).as("views");

Table<Record2<String, Integer>> nested = 
    table(create.select(MYTABLE.ID, min(views))
    .from(MYTABLE)
    .groupBy(MYTABLE.ID));

return create.select(sum(nested.field(views)))
    .from(nested)
    .fetchOne().value1().longValue();

Notice how the Table.field(Field<T>) method allows for maintaining the <T> type reference assuming that the field you pass in really exists in the derived table, by its given name, and that it matches the type of your argument field.

Alternative using window functions

Notice that your query could be rewritten using window functions as follows.

SELECT SUM(MIN(num_views)) OVER ()
FROM fact_wishlist
GROUP BY id
LIMIT 1

Or in jOOQ:

return
create.select(sum(min(MYTABLE.NUM_VIEWS)).over())
      .from(MYTABLE)
      .groupBy(MYTABLE.ID)
      .limit(inline(1))
      .fetchSingle()
      .value1().longValue();

This of course depends on whether your database supports window functions, and it might be faster/slower than your double aggregation solution, also depending on the vendor.

Upvotes: 2

Related Questions