Specify condition when eager loading using with()?

[dsidharta]

Is there a way to specify a condition of eager loading when using with()? For example, we have a field signifying a soft-deleted row: deleted; is there a way to specify that we don't want to eager-fetch rows whose deleted = 't'?

object XTable : LongIdTable("x") {
    val deleted: Column<Boolean> = bool("deleted").default(false)
}

class X(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<X>(XTable)
   
    var ys by Y via XYTable
    var deleted by table.deleted
}

object YTable : LongIdTable("y") {
    val deleted: Column<Boolean> = bool("deleted").default(false)
}

class Y(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<Y>(YTable)

    var deleted by table.deleted
}

object XYTable : LongIdTable("x_y") {
    val xId: Column<EntityID<Long>> = reference(name = "x_id", foreign = XTable)
    val yId: Column<EntityID<Long>> = reference(name = "y_id", foreign = YTable)
    val deleted: Column<Boolean> = bool("deleted").default(false)
}

class XY(id: EntityID<Long>) : LongEntity(id) {
    companion object : BaseLongEntityClass<XY>(XYTable)

    var x by X referencedOn XYTable.xId
    var y by Y referencedOn XYTable.yId
    val deleted: Column<Boolean> = bool("deleted").default(false)
}

Without any conditions, we can eager-load easily as follows:

val results = X.all {
    if (loadAllYs) {
        this.with(X::ys)
    }
}

But is there a way to specify that we want XY.deleted = 'f' and Y.deleted = 'f'? I've tried something like the following, but this doesn't eager-load the relationships, i.e. accessing each X.ys generates a select query.

val results = XTable.innerJoin(XYTable).innerJoin(YTable)
    .selectAll().map {
        Y.wrapRow(it)
        XY.wrapRow(it)
        X.wrapRow(it)
    }

[Tapac]

I'm not sure that it will be correct from the data correctness side. Now you can for example "delete/undelete" related Y entities or count them in your code like x.xy.forEach { it.deleted = false } but if you'll filter that relation you wont be able to do that.

Why don't delete relation in XY when mark X or Y as deleted?

[dsidharta]

Why don't delete relation in XY when mark X or Y as deleted?

Ideally, we want to preserve the relationships for various purposes, e.g. audit and various other use cases.

Now you can for example "delete/undelete" related Y entities or count them in your code like x.xy.forEach { it.deleted = false } but if you'll filter that relation you wont be able to do that.

Yep, valid thought, and thus my original question of the conditionality of the fetching because if we can conditionally fetch, I think this won't be a problem. Thanks!

[gel-hidden]

Don't know if this helps, but here is related functionality in another library. Would be nice to see the possibility to do something similar.

[ghost]

@dsidharta did you find any solution, please share with us

[dsidharta]

@mdsadiqueinam, no I didn't find any solutions, but I happened to be changing my model at the same time, and so this was no longer an issue for me.