How can I implemented a custom order for a enumeration in the orderBy with kotlin exposed?

Question

I have the following enumeration that is stored in the database as a String:

enum class Status {

    PENDING, ACCEPTED, REJECTED
}

And the following Table class:

object Examples : Table("examples") {

    val id = integer("id")
    val status = enumerationByName("status", 100, Status::class)

    override val primaryKey = PrimaryKey(id)
}

When I use the following orderBy in my exposed query it is sorted alphabetically (ACCEPTED first).

Examples.selectAll().orderBy(Table.status to SortOrder.ASC)

But I need a different order; PENDING should be first, then ACCEPTED, then REJECTED.

How can I implement this? I am using a mysql database.

I tried the following, but it's not compiling and I do not know what is wrong.

                .orderBy(
                    Case(Examples.status)
                        .When(Status.PENDING).Then(1)
                        .When(Status.ACCEPTED).Then(2)
                        .When(Status.REJECTED).Then(3)
                        .Else(3)
                )

Answer 1

Here's how you can sort in JetBrains/Exposed in general

val EmployeeTable = object : IntIdTable() {
    val employeeId = integer("employee_id")
    val name = text("name")
    val city = text("city")
    val salary = integer("salary")
}.also { SchemaUtils.drop(it);SchemaUtils.create(it) }

data class Employee(val employeeId: Int, val name: String, val city: String, val salary: Int)

val employees = listOf(
    Employee(1, "Shalaga44", "Ajman", 44),
    Employee(2, "Shalaga44", "Ajman", 44),
    Employee(3, "No One", "Ajman", 44),
    Employee(4, "No One", "No where", 44),
    Employee(5, "No One", "No where", 0),
)
EmployeeTable.batchInsert(employees.shuffled()) {
    this[EmployeeTable.employeeId] = it.employeeId
    this[EmployeeTable.name] = it.name
    this[EmployeeTable.city] = it.city
    this[EmployeeTable.salary] = it.salary
}

val conditions = listOf(
    Op.build { EmployeeTable.employeeId inList setOf(employees.first().employeeId) },
    Op.build { EmployeeTable.name ilike "%${employees.first().name}%" },
    Op.build { EmployeeTable.city eq employees.first().city },
    Op.build { EmployeeTable.salary greaterEq employees.first().salary }
)

class Plus<T : Number>(
    val expr0: Expression<T>,
    val expr1: Expression<T>
) : Expression<T>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit =
        queryBuilder { append("(", expr0, "+", expr1, ")") }
}


val matchScoreExpression = conditions
    .mapIndexed { index, condition ->
        val caseWhen = CaseWhen<Int>(null)
        caseWhen.When(
            cond = condition,
            result = intLiteral(1)
        )
        val caseWhenElse = caseWhen.Else(intLiteral(0))
        caseWhenElse

    }.reduce { acc, expression ->
        Plus(acc, expression)
    }


val query = EmployeeTable
    .slice(EmployeeTable.fields + matchScoreExpression)
    .select { conditions.reduce { acc, op -> acc or op } }
    .orderBy(matchScoreExpression, SortOrder.DESC)


val result = query.map {
    Employee(
        employeeId = it[EmployeeTable.employeeId],
        name = it[EmployeeTable.name],
        city = it[EmployeeTable.city],
        salary = it[EmployeeTable.salary],
    )
}

assertThat(employees.sortedBy { it.employeeId }.dropLast(1))
    .isEqualTo(result)

SchemaUtils.drop(EmployeeTable)