Different DAO question: Is there a way to have a ...

# exposed

[Daniel Pitts]

Different DAO question: Is there a way to have a one-to-many, where the many side doesn't have a unique identifier, only a reference to the one side?

[Oleg Babichev]

As far as I know, for DAO level both sides of one-to-many must be Entities (what means that they should have a unique identifier), it's related to the cache mechanism and how entities are stored in the memory.

But probably i loose something. If it's related to DAO, how the entity of the "many" side is specified (or how in general the tables and entities are described now)?

@Chantal Loncle probably you've already seen such a case and have something to add.

[Chantal Loncle]

@Daniel Pitts If choosing to use the DAO approach, any table that needs to associate with an Entity must have a unique identifier, for the reasons mentioned above. You can set up the tables in the database using DSL any way you want, but you wouldn't be able to take advantage of DAO entity references if the many side doesn't have an entity:

object Provinces : IntIdTable("provinces") { // one
    val name = varchar("name", 32)
}
object Cities : Table("cities") { // many
    val name = varchar("name", 32)
    val province = reference("province_id", Provinces.id)
}
class Province(id: EntityID<Int>) : IntEntity(id) {
    var name by Provinces.name
    val cities by ??? referrersOn Cities.province
    companion object : IntEntityClass<Province>(Provinces)
}

There's nothing technically stopping the reference itself from being used as the unique identifier, if that is what you need:

object Provinces : IntIdTable("provinces") { // one
    val name = varchar("name", 32)
}
object Cities : IdTable<Int>("cities") { // many
    override val id = reference("province_id", Provinces)
    val name = varchar("name", 32)
    init {
        // need to explicitly set id here
        // since reference is being used instead of unique constraint
        addIdColumn(id)
    }
}
class Province(id: EntityID<Int>) : IntEntity(id) {
    var name by Provinces.name
    val cities by City referrersOn Cities.id
    companion object : IntEntityClass<Province>(Provinces)
}
class City(id: EntityID<Int>) : IntEntity(id) {
    var name by Cities.name
    val province by Province referencedOn Cities.id
    companion object : IntEntityClass<City>(Cities)
}

This may cause problems when caching City entities, however, since caching relies on each entity instance having a unique id.

As requested above, any details about how both tables in the relation, and the foreign key, are defined in the database could give a better idea about how the entity(ies) could be mapped.

[Daniel Pitts]

Makes sense. Is it possible to use a composite key where one of the columns is a foreign key to the “one” side of the one-to-many?

[Chantal Loncle]

Yup, using CompositeIdTable with the same example above:

object Provinces : IntIdTable("provinces") { // one
    val name = varchar("name", 32)
}
object Cities : CompositeIdTable("cities") { // many
    val cityId = integer("city_id").entityId()
    val provinceId = reference("province_id", Provinces)
    val name = varchar("name", 32)
    override val primaryKey = PrimaryKey(cityId, provinceId)
    init {
        // no need to add cityId here, as .entityId() does this automatically
        addIdColumn(provinceId)
    }
}
class Province(id: EntityID<Int>) : IntEntity(id) {
    var name by Provinces.name
    val cities by City referrersOn Cities // composite key referrersOn
    companion object : IntEntityClass<Province>(Provinces)
}
class City(id: EntityID<CompositeID>) : CompositeEntity(id) {
    var name by Cities.name
    companion object : CompositeEntityClass<City>(Cities)
}

More details on configuring and using composite key tables can be found in DAO docs.

[maxmello]

Sorry I just found this thread, do I understand correctly that one should not call entityId() on reference column definitions, instead using addIdColumn in the constructor? So if I have a composite table with 3 foreign key references that combined are the primary key, I would just use addIdColumn three times, not using entityId() at all?

And it is correct to explicitly not include composite key columns in the Entity, even for references, instead accessing them via the id property?