CODEWITHSUNDEEP
sqljoinaliasjooq
Dmytro Kryvenko
Dmytro Kryvenko

Reputation: 343

Use alias for JOIN using JOOQ's SelectQuery

I need to write query with nested select and join using JOOQ's SelectQuery:

    SELECT *
    FROM (select * from 
    "public"."parent" 
    order by "public"."parent"."setup_time" 
    desc limit 10 offset 0) as "parent1" 
    join "public"."child" 
    on "public"."child"."parent_id" = "parent1"."id" 
    where "public"."child"."name" = 'test'

So, I've written something like this:

SelectQuery<ParentRecord> subSelectQuery = context.selectQuery(PARENT);
selectQuery.addJoinOnKey(CHILD, JoinType.JOIN, CHILD.PARENT_ID);

But it generates sql code like

join "public"."child" on "public"."child"."parent_id" =  "public"."parent"."id

How can I use alias parent1 instead of full table name "public"."child"."parent_id"?

Upvotes: 0

Views: 1630

Answers (1)

Dmytro Kryvenko
Dmytro Kryvenko

Reputation: 343

I've found solution.

SelectQuery<ParentRecord> selectQuery = context.selectQuery(subSelectQuery.asTable(PARENT.getName()));
selectQuery.addJoin(CHILD, JoinType.JOIN, CHILD.PARENT_ID.eq(PARENT.as(PARENT.getName()).ID));

Upvotes: 1

Related Questions