An automated jOOQ-query-builder based on input using JSON in Kotlin

Question

In my current project, I have to build a criteria builder which users can use to find "contacts" (the definition of which is not relevant). For this, I have created the front-end in Elm, which will then create a JSON-request to the back-end. The back-end has to deserialize this JSON and create a jOOQ query which will then fetch a list of contacts matching the given criteria.

The JSON will look something like this:

{
  "conditionGroup":{
    "type":"conditionGroup",
    "groupType":"any",
    "criteria":[
      {
        "type":"condition",
        "field":"phoneNumber",
        "operator":"stringIs",
        "fieldType":"string",
        "value":"2424"
      },
      {
        "type":"conditionGroup",
        "groupType":"all",
        "criteria":[
          {
            "type":"condition",
            "field":"firstName",
            "operator":"stringIs",
            "fieldType":"string",
            "value":"adsd"
          },
          {
            "type":"condition",
            "field":"phoneNumber",
            "operator":"stringIsNot",
            "fieldType":"string",
            "value":"2456"
          },
          {
            "type":"condition",
            "field":"ticketsNumberBought",
            "operator":"intGt",
            "fieldType":"int",
            "value":"3"
          }
        ]
      }
    ]
  }
}

As you can see, it will be possible to create ConditionGroup's. These groups can contain either Condition's or other ConditionGroup's.

To deserialize this JSON, I have create the following classes in Kotlin using Jackson:

data class Condition(
  val field: String,
  val operator: String,
  val fieldType: String,
  val value: String
) : Criterion()

data class ConditionGroup(
  val groupType: String,
  val criteria: List<Criterion>
) : Criterion()


@JsonTypeInfo(
  use = JsonTypeInfo.Id.NAME,
  include = JsonTypeInfo.As.PROPERTY,
  property = "type"
)
@JsonSubTypes(
  value = [
    JsonSubTypes.Type(value = Condition::class, name = "condition"),
    JsonSubTypes.Type(value = ConditionGroup::class, name = "conditionGroup")
  ]
)
open class Criterion

This code will create one ConditionGroup containing all other Condition's and ConditionGroup's (and so will ConditionGroup's have their own list of Condition's and ConditionGroup's).

Next step is to create a jOOQ-query. Basically, I have split the query is two separate parts: one part to list the table to join, and one part to create the condition for the "where"-clause. But before doing these, I have create an enum for each possible search field to define which information should be added to the query based on the fields the user is searching on. The enum is looking like this (with only three enums for now):

enum class FieldKey(val fieldKey: String, val jooqJoin: List<Pair<Table<*>, org.jooq.Condition>>, val column: TableField<*, *>) {
  FIRST_NAME("firstName", listOf(), c.FIRST_NAME),
  PHONE_NUMBER("phoneNumber", listOf(Pair(pn, pn.CONTACT_ID.eq(c.ID))), pn.PHONE_NUMBER_),
  AMOUNT_OF_TICKETS("ticketsNumberBought", listOf(Pair(o, o.CONTACT_ID.eq(c.ID)), Pair(t, t.ORDER_ID.eq(o.ID))), o.ID);
}

Tables to join To create a correct join statement for the resulting query, I wrote this code:

private fun getJointable(
  conditionGroup: ConditionGroup
): Table<*> {
  val tableList = getJoinList(conditionGroup, mutableListOf())
  var table: Table<*> = c
  tableList.forEach { joinPair ->
    table = table.leftJoin(joinPair.first).on(joinPair.second)
  }
  return table
}

// Recursive method to find the tables to join for this group
private fun getJoinList(
  conditionGroup: ConditionGroup,
  tableList: MutableList<Pair<Table<*>, org.jooq.Condition>>
): List<Pair<Table<*>, org.jooq.Condition>> {
  conditionGroup.criteria.forEach { criterion ->
    if (criterion is ConditionGroup) getJoinList(criterion, tableList)
    else {
      val condition = criterion as Condition
      val conditionEnum = FieldKey.values().firstOrNull { it.fieldKey == condition.field }
      conditionEnum?.jooqJoin?.forEach { join ->
        if (!tableList.contains(join)) {
          tableList.add(join)
        }
      }
    }
  }
  return tableList
}

This code is looping through all Condition's to collect the necessary tables to join. It will firstly create a list with unique joins to prevent duplicates. Then, it will loop through all collected joins to create a table. This table is starting with the Contact-table and join all collected joins to it using the Pair-values in the enum class. This will be used later.

All tables are leftJoined. In my opinion, this means that all contacts will still be considered after joining all tables (for example, contacts without phone number will still be used to search on). Please let me know if this idea is wrong.

Where-clauses Secondly, for creating an jOOQ-condition for the where clause, I also created a recursive method:

private fun getWhereCondition(
  conditionGroup: ConditionGroup
): org.jooq.Condition {
  var conditionForCurrentGroup: org.jooq.Condition = DSL.noCondition()
  conditionGroup.criteria.forEach { criterion ->
    if (criterion is ConditionGroup) {
      conditionForCurrentGroup = if (conditionGroup.groupType == "all") {
        conditionForCurrentGroup.and(getWhereCondition(criterion))
      } else {
        conditionForCurrentGroup.or(getWhereCondition(criterion))
      }
    }
    else {
      val condition = criterion as Condition
      val conditionEnum = FieldKey.values().firstOrNull { it.fieldKey == condition.field }
      val newJooqCondition = conditionEnum?.let { createJooqCondition(condition, it) }
      conditionForCurrentGroup = if (conditionGroup.groupType == "all") {
        conditionForCurrentGroup.and(newJooqCondition)
      } else {
        conditionForCurrentGroup.or(newJooqCondition)
      }
    }
  }
  return conditionForCurrentGroup
}

// TODO: Add the right operator(Enum) to this method to search correctly
private fun createJooqCondition(condition: Condition, fieldKey: FieldKey): org.jooq.Condition {
  return fieldKey.column.like("%${condition.value}%")
}

This code is also looping through all groups and conditions, eventually creating one condition. The and's and or's are based on the groupType in ConditionGroup. Each Conditiongroup will be handled recursively.

Creating query The final step is to build the jOOQ-query. This is done by the following (simple) code using previous mentioned code:

private fun buildQuery(conditionGroup: ConditionGroup): List<Contact> {
  return jooq()
    .select(c.asterisk())
    .from(getJointable(conditionGroup))
    .where(getWhereCondition(conditionGroup))
    .fetchInto(Contact::class.java)
}

I have done some testing and this all seem to work. As example, this is the query based on the JSON mentioned earlier:

I would appreciate to retrieve valuable feedback on my idea and implementation of this code. Is there something that I could do better? Is there a library (from jOOQ) that I can use to improve this implementation? Or maybe this is the best way to implement my intentions?