CODEWITHSUNDEEP
javamysqlsqljooq
Newbie
Newbie

Reputation: 1714

jOOQ join Select<?> object

I want to perform something like this in SQL:

SELECT 
  * 
FROM 
  tbl1 t1 
  INNER JOIN 
  (SELECT MAX(col1) as maxCol, col2 FROM tbl1 t1 GROUP BY t1.col2) subQ
  ON t1.col2 = subQ.col2
  AND t1.col1 = subQ.maxCol

In jOOQ, I store the subQ into Select<?> object: 

Select<?> subQ = myDSL.select(DSL.max(TBL1.COL1).as("maxCol"), TBL1.COL2)
                      .from(TBL1)
                      .groupBy(TBL1.COL2);

My question is, how to get the maxCol column from subQ and use it in join? My join is something like this:

select()
.from(TBL1)
.join(subQ.asTable())
.on(TBL1.COL1.eq(subQ.asTable().field("maxCol")));

I have error on .on()

The method eq(String) in the type Field is not applicable for the arguments (Field)

what should I do?

Upvotes: 5

Views: 3472

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220762

Using MySQL 8.0

If you're using MySQL 8.0+, don't use a subquery but a window function instead. This would be the equivalent query to yours:

SELECT *
FROM (
  SELECT t1.*, RANK() OVER (PARTITION BY t1.col2 ORDER BY col1 DESC) rk
  FROM tbl1 t1
) t
WHERE t.rk = 1

The advantage is that you only have a single tbl1 access, which is likely to run faster.

This is often also called a TOP-n query. In other databases, there are other ways to implement this, see this article here.

Using older MySQL versions

If you access a field by untyped name (String or org.jooq.Name) from any table, then the compiler does not have any type information to put on the resulting Field<?>, which is why your original code doesn't compile.

You can, however, use one of the following techniques:

Remember the original maxCol field reference

From your subquery, factor out the maxCol field reference and assign it to a local variable (assuming it is of type Integer, replace if needed):

Field<Integer> maxCol = DSL.max(TBL1.COL1).as("maxCol");
Select<?> subQ = myDSL.select(maxCol, TBL1.COL2).from(TBL1).groupBy(TBL1.COL2);

Now, you can also use this reference to extract a column from the subquery:

Field<Integer> subQMaxCol = subQ.field(maxCol);

Or inline that directly in your solution:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field(maxCol)));

Name your maxCol column after TBL1.COL1

In this particular use-case, it might make sense not to introduce any new names, but to re-use COL1 as a name:

Select<?> subQ = myDSL.select(DSL.max(TBL1.COL1).as(TBL1.COL1), TBL1.COL2)
                      .from(TBL1)
                      .groupBy(TBL1.COL2);

In this case (if there is no ambiguous COL1 column name), you can use that reference to extract the COL1 field again from the subquery:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field(TBL1.COL1)));

Use the TBL1.COL1 reference's data type

From your original solution, just add the data type when you extract the field from the subquery:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field("maxCol", TBL1.COL1.getDataType())));

Or, coerce it:

select().from(TBL1)
        .join(subQ.asTable())
        .on(TBL1.COL1.eq(subQ.field("maxCol").coerce(TBL1.COL1)));

Upvotes: 1

Newbie
Newbie

Reputation: 1714

This solve the problem:

select().from(TBL1).join(subQ.asTable()).on(TBL1.COL1.eq((Field<DataType>) subQ.field("maxCol")));

Upvotes: 0

Related Questions