Hello, There's hardly any SQL that cannot be expressed with jOOQ, in principle. But in certain cases (aliased tables, derived tables), jOOQ will be limited by the expressivity of the Java language, and the things that are possible to express with an internal domain-specific language in Java. For details, the manual contains a section about SQL to DSL mapping rules:
- http://www.jooq.org/doc/latest/manual/reference/dsl-mapping-rules/ In your specific use-case, you would write the following in jOOQ: // These static imports are assumed to be there: import static com.example.generated.Tables.*; import static org.jooq.impl.DSL.*; Archive a = ARCHIVE.as("a"); ArchiveFile af = ARCHIVE_FILE.as("af"); Field<Timestamp> MaxDateTime = max(af.CREATED).as("MaxDateTime"); Table<?> gaf = select( af.ARCHIVE_ID, maxDateTime ) .from(af) .groupBy(af.ARCHIVE_ID) .asTable("gaf"); Table<?> af2 = select(af.fields()) .from(af) .join(gaf) .on(af.ARCHIVE_ID.eq(gaf.field(af.ARCHIVE_ID))) .and(af.CREATED.eq(gaf.field(MaxDateTime))) .asTable("af"); DSL.using(configuration) .selectFrom(a) .where(a.ID.in( select(af2.field(af.ARCHIVE_ID)) .from(af2) .where(af2.field(af.EXPIRE).gt(Date.valueOf("2014-06-01"))) .and(af2.field(af.EXPIRE).lt(Date.valueOf("2014-07-01"))) )) .fetch(); As you can see, while it is possible to express the original query in jOOQ, SQL composition is a bit more verbose because unlike in SQL, all table expressions have to be declared before they can be used / referenced. Hope this helps, Lukas 2014-06-07 15:47 GMT+02:00 <[email protected]>: > Hello, > I described my project structure and the problem in this link > <http://stackoverflow.com/questions/23961205/complex-jpql-query-to-list-last-created-file-from-all-archives>: > > > http://stackoverflow.com/questions/23961205/complex-jpql-query-to-list-last-created-file-from-all-archives > > With that in mind and knowing that is possible to solve the problem with > the above native query: > > SELECT * FROM archive AS a WHERE a.id IN( > SELECT af.archive_id FROM( > SELECT af.* > FROM archive_file af > INNER JOIN > (SELECT archive_id, MAX(created) AS MaxDateTime > FROM archive_file > GROUP BY archive_id) gaf > ON af.archive_id = gaf.archive_id > AND af.created = gaf.MaxDateTime) as af > WHERE af.expire > '2014-06-01' AND af.expire < '2014-07-01') > > I want to know if is possible in JOOQ to make a equivalent query to get > the same result? > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
