Thanks Lukas!
On Thursday, 5 October 2023 at 09:50:26 UTC+2 lukas...@gmail.com wrote:
> You probably mean the declaration of aliased tables up front, instead of
> embedded in the query, like in SQL? There's no other way to declare
> variables in Java. Java can't follow SQL's syntax where stuff is referenced
> before it is declared (lexically).
>
> On Wed, Oct 4, 2023 at 7:25 PM Stefan Sator <sator.s...@gmail.com> wrote:
>
>> At first I was unsure it there really is not other way to write:
>> CustomerobjectdbHistory h = CustomerobjectdbHistory.
>> CUSTOMEROBJECTDB_HISTORY.as("h");
>> CustomerobjectdbHistory l = CustomerobjectdbHistory.
>> CUSTOMEROBJECTDB_HISTORY.as("l");
>>
>> But now I think it makes sense and is the only way to get both type
>> safety and to be able to rename a table at the same time.
>>
>> On Wednesday, 4 October 2023 at 15:48:50 UTC+2 lukas...@gmail.com wrote:
>>
>>> Let me rephrase my previous question:
>>>
>>> What is your specific question? Why do you have doubts? What kind of
>>> answer are you expecting?
>>>
>>> On Wed, Oct 4, 2023 at 3:17 PM Stefan Sator <sator.s...@gmail.com>
>>> wrote:
>>>
>>>> After lots of try and error this works, but is it the "correct" way?
>>>>
>>>> CustomerobjectdbObject o = CustomerobjectdbObject.
>>>> CUSTOMEROBJECTDB_OBJECT.as("o");
>>>> CustomerobjectdbHistory h = CustomerobjectdbHistory.
>>>> CUSTOMEROBJECTDB_HISTORY.as("h");
>>>> CustomerobjectdbHistory l = CustomerobjectdbHistory.
>>>> CUSTOMEROBJECTDB_HISTORY.as("l");
>>>>
>>>> Condition c = getTenantCondition(o);
>>>>
>>>> //@formatter:off
>>>> var fetch = db.select(o.NAME,
>>>> o.STATUS.convertFrom(x -> ObjectSafetyStatus.from(x)),
>>>> o.CHANGED_AT,
>>>> l.MAJOR_VERSION,
>>>> l.MINOR_VERSION,
>>>> l.COMMENT)
>>>> .from(o,
>>>> lateral(DSL.select(h.MAJOR_VERSION,
>>>> h.MINOR_VERSION,
>>>> h.COMMENT)
>>>> .from(h)
>>>> .where(h.OBJECT_ID.eq(o.ID))
>>>> .orderBy(h.MAJOR_VERSION.desc(),
>>>> h.MINOR_VERSION.desc())
>>>> .limit(1)).as(l)
>>>> )
>>>> .where(c)
>>>> .orderBy(o.CHANGED_AT.desc())
>>>> .limit(10)
>>>> .fetch(mapping((r1, r2, r3, r4, r5, r6) -> new
>>>> HomeObjectTableUIModel(r1, r2, r3, r4, r5, r6)));
>>>> //@formatter:on
>>>>
>>>> return fetch;
>>>>
>>>> On Wednesday, 4 October 2023 at 14:43:45 UTC+2 lukas...@gmail.com
>>>> wrote:
>>>>
>>>>> Stefan,
>>>>>
>>>>> What is your specific question? What have you tried? What didn't work?
>>>>>
>>>>> On Wed, Oct 4, 2023 at 2:32 PM Stefan Sator <sator.s...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi Lukas,
>>>>>> I dont get it to work for just some fields of the lateral join.
>>>>>>
>>>>>> How do I do this?
>>>>>> public List<HomeShopTableUIModel> getShopPage() {
>>>>>> ShopdbObject o = ShopdbObject.SHOPDB_OBJECT;
>>>>>> ShopdbHistorie h = ShopdbHistorie.SHOPDB_HISTORIE;
>>>>>> ShopdbHistorie l = ShopdbHistorie.SHOPDB_HISTORIE;
>>>>>>
>>>>>> Condition c = getTenantCondition(o);
>>>>>>
>>>>>> //@formatter:off
>>>>>> var fetch = db.select(o.NAME)
>>>>>> .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")
>>>>>> )
>>>>>> .where(c)
>>>>>> .orderBy(o.CHANGED_AT.desc())
>>>>>> .limit(10)
>>>>>> .fetch();
>>>>>> //@formatter:on
>>>>>>
>>>>>> On Tuesday, 3 October 2023 at 10:49:33 UTC+2 Stefan Sator wrote:
>>>>>>
>>>>>>> And even better:
>>>>>>>
>>>>>>> CustomerobjectdbObject o = CustomerobjectdbObject.
>>>>>>> CUSTOMEROBJECTDB_OBJECT.as("o");
>>>>>>> CustomerobjectdbHistory h = CustomerobjectdbHistory.
>>>>>>> CUSTOMEROBJECTDB_HISTORY.as("h");
>>>>>>> CustomerobjectdbHistory l = CustomerobjectdbHistory.
>>>>>>> CUSTOMEROBJECTDB_HISTORY.as("l");
>>>>>>>
>>>>>>> //@formatter:off
>>>>>>> var fetch = db.select(o.NAME, l.COMMENT)
>>>>>>>
>>>>>>> .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:47:12 UTC+2 Stefan Sator wrote:
>>>>>>>
>>>>>>>> Final solution:
>>>>>>>>
>>>>>>>> CustomerobjectdbObject o = CustomerobjectdbObject.
>>>>>>>> CUSTOMEROBJECTDB_OBJECT.as("o");
>>>>>>>> CustomerobjectdbHistory h = CustomerobjectdbHistory.
>>>>>>>> CUSTOMEROBJECTDB_HISTORY.as("h");
>>>>>>>> CustomerobjectdbHistory l = CustomerobjectdbHistory.
>>>>>>>> CUSTOMEROBJECTDB_HISTORY.as("l");
>>>>>>>>
>>>>>>>> //@formatter:off
>>>>>>>> var fetch = db.select(o.NAME, l.field("comment"))
>>>>>>>> .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:44:43 UTC+2 Stefan Sator wrote:
>>>>>>>>
>>>>>>>>> This works, but it doesnt look elegant.
>>>>>>>>>
>>>>>>>>> Is there a better way?
>>>>>>>>>
>>>>>>>>> CustomerobjectdbObject o = CustomerobjectdbObject.
>>>>>>>>> CUSTOMEROBJECTDB_OBJECT.as("o");
>>>>>>>>> CustomerobjectdbHistory h = CustomerobjectdbHistory.
>>>>>>>>> CUSTOMEROBJECTDB_HISTORY.as("h");
>>>>>>>>> CustomerobjectdbHistory l = CustomerobjectdbHistory.
>>>>>>>>> CUSTOMEROBJECTDB_HISTORY.as("l");
>>>>>>>>>
>>>>>>>>> //@formatter:off
>>>>>>>>> var fetch = db.select(o.NAME, l.field("comment"))
>>>>>>>>>
>>>>>>>>> .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:28:05 UTC+2 lukas...@gmail.com
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> See:
>>>>>>>>>> https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/table-columns/dereferenced-table-columns/
>>>>>>>>>>
>>>>>>>>>> On Tue, Oct 3, 2023 at 10:18 AM Stefan Sator <
>>>>>>>>>> sator.s...@gmail.com> wrote:
>>>>>>>>>>
>>>>>>>>>>> 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+...@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
>>>>>>>>>>>
>>>>>>>>>>> <https://groups.google.com/d/msgid/jooq-user/c86873ae-f3a9-41b5-8ad2-f91fd2432630n%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+...@googlegroups.com.
>>>>>>
>>>>> To view this discussion on the web visit
>>>>>> https://groups.google.com/d/msgid/jooq-user/9e361778-6115-4541-90c5-8068dd03e0bdn%40googlegroups.com
>>>>>>
>>>>>> <https://groups.google.com/d/msgid/jooq-user/9e361778-6115-4541-90c5-8068dd03e0bdn%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+...@googlegroups.com.
>>>>
>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msgid/jooq-user/2a2edeb2-6321-4942-acb2-4349d1280cd9n%40googlegroups.com
>>>>
>>>> <https://groups.google.com/d/msgid/jooq-user/2a2edeb2-6321-4942-acb2-4349d1280cd9n%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+...@googlegroups.com.
>>
> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/jooq-user/74aef0bd-8ee2-4a51-93d4-6f4f15d03148n%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/jooq-user/74aef0bd-8ee2-4a51-93d4-6f4f15d03148n%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/efc49fff-4911-48af-a942-2a660205c7dfn%40googlegroups.com.
