How to join with the same table two times in jOOQ?

user_19338016 0
Viewed 98

I have table which have one-to-one relation with another, but there are two of them, src and dst, how to join it right? I did something like that, but not sure this is best practce:

 val route = tbl.`as`("route")
 val srcPlace = Tables.PLACE.`as`("srcPlace")
 val dstPlace = Tables.PLACE.`as`("dstPlace")

 val records = dsl
        .select(route.asterisk())
        .select(srcPlace.asterisk())
        .select(dstPlace.asterisk())
        .from(route)
        .join(srcPlace).on(route.SRC_ID.eq(srcPlace.ID))
        .join(dstPlace).on(route.DST_ID.eq(dstPlace.ID))
        .limit(pageable.pageSize)
        .offset(pageable.offset)
        .fetch {
            val r = it.into(route).into(RouteEntity::class.java)
            val sP = it.into(srcPlace).into(PlaceEntity::class.java)
            val dP = it.into(dstPlace).into(PlateEntity::class.java)
            r.srcPlace = sP
            r.dstPlace = dP
            r
        }

How to do it better?

jooq spring-boot kotlin
1 Answers

The easiest way (starting from jOOQ 3.17 and the ability of projecting table expressions as SelectField) is to just project the tables themselves, e.g.:

dsl.select(route, srcPlace, dstPlace)
   . // query
   .fetch {
       val (r, sP, dP) = it
       // ...
   }

In your particular case, you can even simplify things further by using implicit joins

// Assuming you named your foreign keys "src" and "dst"
dsl.select(route, route.src, route.dst)
   .from(route)
   .limit(pageable.pageSize)
   .offset(pageable.offset)
   .fetch {
       val (r, sP, dP) = it
       // ...
   }

Or even, without the it destructuring:

// Assuming you named your foreign keys "src" and "dst"
dsl.select(route, route.src, route.dst)
   .from(route)
   .limit(pageable.pageSize)
   .offset(pageable.offset)
   .fetch { (r, sP, dP) ->
       // ...
   }
user_5217990
Related