jooq

Add all the fields of "joinable tables" programmatically

I have two tables:

CREATE TABLE [instrument].[InstrumentType] (
    [InstrumentTypeId] INT           NOT NULL IDENTITY (1, 1),
    [Name]             VARCHAR (255) NOT NULL,
    CONSTRAINT [PK_InstrumentType]   PRIMARY KEY CLUSTERED ([InstrumentTypeId] ASC)
);

CREATE TABLE [instrument].[Instrument] (
    [InstrumentId]     INT            NOT NULL IDENTITY (1, 1),
    [InstrumentTypeId] INT            NOT NULL,
    [Name]             VARCHAR (255)  NOT NULL,
    CONSTRAINT [PK_Instrument]                PRIMARY KEY CLUSTERED ([InstrumentId] ASC),
    CONSTRAINT [FK_Instrument_InstrumentType] FOREIGN KEY ([InstrumentTypeId]) REFERENCES [instrument].[InstrumentType] ([InstrumentTypeId])
);

I can simply fetch the instrument type without writing the join (fabulous feature):

create.select(INSTRUMENT.INSTRUMENTID, INSTRUMENT.NAME, INSTRUMENT.instrumenttype.NAME)
      .from(INSTRUMENT)
      .fetch()

I have many tables similar to this and I would like to create this query programmatically. I have tried using INSTRUMENT.references but the query fails because it refers to the IDENTIFIERTYPE table instead of using the INSTRUMENT.instrumenttype path. This is the query rendered by my fetch method:

select
  [instrument].[Instrument].[InstrumentId],
  [instrument].[Instrument].[Name],
  [instrument].[InstrumentType].[Name]
from [instrument].[Instrument]

This is what I have so far but I can't find the right way to go through the foreign key paths like in the manual query.

val T = INSTRUMENT

fun fetch(dsl: DSLContext): Any {
  return dsl
    .select(fieldList())
    .from(T)
    .fetch()
}

protected open fun fieldList(): Set<Field<*>> {
  val result = mutableSetOf<Field<*>>()
  val tables = fkTableList(T)
  for (t in tables) {
    val fk = t.references.flatMap { it.fields }.toSet()
    val include = t.fields().toSet()

    //Remove INSTRUMENTTYPEID
    val selectedFields = include - fk
    result.addAll(selectedFields)
  }
  return result
}

private fun fkTableList(table: Table<*>): List<Table<*>> {
  val result = mutableListOf(table)
  val fkTables = table.references.map { it.key.table }
  result.addAll(fkTables.flatMap { fkTableList(it) })
  return result
}
Solution

  • Note, there's this feature request to construct org.jooq.Path instances programmatically, which you might be interested in:

    It hasn't been implemented yet as of jOOQ 3.19. Without using Path types, you'll have to explicitly add the join tree yourself. You can't just add the projection alone. A relatively simple way to do this would be to use the ON KEY synthetic join syntax (which jOOQ's Path implementation also uses internally):

    .from(joinTree(T))

    And then:

    private fun joinTree(table: Table<*>): Table<*> {
  var result = table

  for (fk in table.references) {

    // Optionally, use inner join for non-nullable FKs
    result = result.leftJoin(joinTree(

      // Disambiguate tables that are joined many times through different paths
      fk.key.table.`as`(fk.name)
    )).onKey(fk)
  }

  return result
}

    This recursion style produces right-associative join trees (i.e. A JOIN (B JOIN C) instead of A JOIN B JOIN C), which will avoid ambiguities in case a child table has multiple paths towards the same parent / ancestor. This is what jOOQ's implicit joins also do for the same reason.

    Caveats