Relations of the EntitySequence

[pponec]

I get an error when I try to sort the EntitySequence by the value of the relational database table:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "DEPARTMENT.NAME" not found; ...

It looks like the EntitySequence class API does not support entity relations.
Is it possible to ask an object type of EntitySequence to sort by column of a relational table according to the following pattern?

val result = database
    .sequenceOf(Employees(), withReferences = true)
    .sortedBy { Departments.name.asc() }
    .toList()

[vincentlauvlwj]

See the doc https://www.ktorm.org/en/entity-finding.html#Get-Entities-by-Sequences

[pponec]

That's nice, thank you for explaining. I would like to ask about the appropriate solution for the Employee -> Supervisor cyclic binding? The following code throws a NullPointerException to me:

class Employees : Table<Employee>("employee") {
    val id = long("id").primaryKey().bindTo { it.id }
    val supervisorId = long("supervisor_id").bindTo { it.supervisor?.id }
    
    val supervisor = (supervisorId.referenceTable as Employees).aliased("supervisor") // (!)

[vincentlauvlwj]

See the doc https://www.ktorm.org/en/entities-and-column-binding.html#Column-Binding

[pponec]

I'll try to phrase the question better: how can I get a list of employees filtered by the name of their employer - using the EntitySequence? I would like to use the following query pattern:

val result2 = database.sequenceOf(Employees())
    .filter { (it.supervisorId.referenceTable as Employees).name eq "John" } // NPE !
    .toList()

The problem with this query is that the filter throws a NullPointerException.

[vincentlauvlwj]

Cyclic reference binding is not supported, but we have an alternative way to do this.

First, find the employee who's name is John:

val john = database.sequenceOf(Employees).find { it.name eq "John" }

Then, find his subordinates:

val subordinates = database.sequenceOf(Employees).filter { it.supervisorId eq john!!.id }.toList()