Kotlin Exposed SQL query on Table with compound primary key and select all which are contained in a given List of DTO Objects

Question

considering the following pseudo code:

object EntityTable : Table("ENTITY") {
    val uid = uuid("uid")
    val idCluster = integer("id_cluster")
    val idDataSchema = integer("id_data_schema")
    val value = varchar("value", 1024)

    override val primaryKey = PrimaryKey(uid, idCluster, idDataSchema, name = "ENTITY_PK")
}

var toBeFound = listOf(
    EntityDTO(uid = UUID.fromString("4..9"), idCluster = 1, idDataSchema = 1),
    EntityDTO(uid = UUID.fromString("7..3"), idCluster = 1, idDataSchema = 2),
    EntityDTO(uid = UUID.fromString("6..2"), idCluster = 2, idDataSchema = 1)
)

fun selectManyEntity() : List<EntityDTO> {
    val entityDTOs = transaction {
        val queryResultRows = EntityTable.select {
            (EntityTable.uid, EntityTable.idCluster, EntityTable.idDataSchema) // <-- every row for which the compound key combination of all three
                inList
            toBeFound.map {
                (it.uid, it.idCluster, it.idDataSchema)                        // <-- has an element in 'toBeFound` list with the same compound key combination
            }
        }
        queryResultRows.map { resultRow -> Fillers().newEntityDTO(resultRow) }.toList()
    }
    return entityDTOs
}

how do I have to write the query that it selects

all rows of EntityTable for which the compound primary key of (id, idCluster, idDataSchema)
is also contained in the given List supposed that every EntityDTO in the List<>
also has fields id, idCluster, idDataSchema) ???

if it helps: EntityDTO has hash() and equals() overloaded for exactly these three fields.

Answer 1

The only way is to make a compound expression like:

fun EntityDTO.searchExpression() = Op.build { 
  (EntityTable.uid eq uid) and (EntityTable.idCluster eq idCluster) and (EntityTable.idDataSchema eq idDataSchema)
}

val fullSearchExpression = toBeFound.map { it.searchExpression() }.compoundOr()

val queryResultRows = EntityTable.select(fullSearchExpression)