Because I get sometimes confused.
I figured that out aswell just now before reading your comment :-)
But how do I select only some of the columns from the lateral join?
var fetch = db.select()
.from(o,
lateral(DSL.select(h.COMMENT)
.from(h)
.where(h.OBJECT_ID.eq(o.ID))
.orderBy(h.MAJOR_VERSION.desc(),
h.MINOR_VERSION.desc())
.limit(1)).as("l")
)
.orderBy(o.CHANGED_AT.desc())
.limit(10)
.fetch();
On Tuesday, 3 October 2023 at 10:09:53 UTC+2 lukas...@gmail.com wrote:
> Why put your LATERAL derived table in SELECT with jOOQ, when you don't do
> that with SQL?
>
> On Tue, Oct 3, 2023 at 10:04 AM Stefan Sator <sator.s...@gmail.com> wrote:
>
>> Hello Lukas,
>>
>> how do I write this query correctly?
>>
>> Wanted:
>> SELECT o.name, l.comment
>> FROM customerobjectdb_object AS o,
>> LATERAL (SELECT * FROM customerobjectdb_history AS h WHERE h.object_id =
>> o.id
>> order by h.major_version desc, h.minor_version desc limit 1
>> ) AS l
>> ORDER BY o.id;
>>
>> Jooq(not correct)
>> var fetch = db.select(o.NAME,
>> //o.STATUS.convertFrom(x -> ObjectSafetyStatus.from(x)),
>> //o.CHANGED_AT,
>> //o.VERSION,
>> lateral(DSL.select(h.COMMENT)
>> .from(h)
>> .where(h.OBJECT_ID.eq(o.ID))
>> .orderBy(h.MAJOR_VERSION.desc(),
>> h.MINOR_VERSION.desc())
>> .limit(1)).as("l")
>> )
>> .from(o)
>> .orderBy(o.CHANGED_AT.desc())
>> .limit(10)
>> .fetch();
>>
>> SQL
>> select "o"."name", row ("l"."comment") as "l"
>> from "public"."customerobjectdb_object" as "o"
>> order by "o"."changed_at" desc fetch next ? rows only
>>
>>
>>
>> --
>> 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 jooq-user+...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/jooq-user/38f093bf-1ac6-4317-8e19-032eea82685bn%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/jooq-user/38f093bf-1ac6-4317-8e19-032eea82685bn%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>
--
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 jooq-user+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/c86873ae-f3a9-41b5-8ad2-f91fd2432630n%40googlegroups.com.
