How could I best query this child <-> parent...

# exposed

[Christiano]

How could I best query this child <-> parent relationship (see image)?
Every Category can have only 1 parent Category linked to it and each parent Category can have many child Categories.

I tried it doing like this

(CategoriesTable innerJoin CategoriesTable.alias("parent")).selectAll()

And then tried to map the ResultRows into an object like this:

fun ResultRow.toCategory(): Category = Category(
    id = this[CategoriesTable.id].value,
    name = this[CategoriesTable.name],
    category = this.toCategory(),
    createdAt = this[CategoriesTable.createdAt],
    updatedAt = this[CategoriesTable.updatedAt],
)

But this throws me an Exception and thus does not work.


I created my table like this:

object CategoriesTable : IntIdTable() {
    val name = varchar("name", 255)
    val categoryId = optReference("parent_category_id", CategoriesTable, onDelete = ReferenceOption.CASCADE) // As long as this foreign key references the primary key of the Poems table this is enough.
    val createdAt = varchar("created_at", 255).default(LocalDateTime.now().toDatabaseString())
    val updatedAt = varchar("updated_at", 255).default(LocalDateTime.now().toDatabaseString())
}

Is there a specific thing I'm missing regarding the relationship? Do I need to use another join type? Could someone give me a bit more info what exactly is going wrong and what I can do next 😅

[Johannes Gätjen]

are you just missing a constraint on your innerJoin for category_id == parent.id?

[Christiano]

AFAIK I already have that constraint/reference in the table itself.

val categoryId = optReference("parent_category_id", CategoriesTable, onDelete = ReferenceOption.CASCADE)

Or isn't that the same as what you mean? 🤔

[Johannes Gätjen]

what is the exception you are getting? This looks like you should be getting a stack overflow

category = this.toCategory()

[Christiano]

I specifically seem to get this exception

Cannot join with org.jetbrains.exposed.sql.Alias@bff5930e as there is no matching primary key/foreign key pair and constraint missing

Because of that the category = this.toCategory() can't even run yet...

[Johannes Gätjen]

You probably want something like this

CategoriesTable.join(
    CategoriesTable.alias("parent"),
    JoinType.INNER,
    CategoriesTable.category,
    CategoriesTable.id
)

[Christiano]

This seems to work, but it results in an empty list... 😅
Will have to debug to see what exactly is going on.

[Johannes Gätjen]

Maybe also easier to use the DAO instead of DSL? I have something like this.

var parent by CategoryDAO optionalReferencedOn CategoriesTable.parent
val children by CategoryDAO optionalReferrersOn CategoriesTable.parent

May be less performant though, depending on what you want to do.

[Christiano]

Yeah, I read somewhere that DAO is also a possibility, but I'm not sure how much work that might be to rework everything to work with the DAO as I already have everything set up for the DSL 😅