How to build and query a Room DB to return a list of objects of multiple classes?

Question

Bear with me, it's a tricky question and what resources I've found around don't really help me resolve my problem.

I'm trying to build a real estate-oriented app on Kotlin. It must show at some point a RecyclerView with multiple object classes (say: houses, flats, plots, buildings, etc.)

I've seen multiple examples of RVs designed to accept multiple classes, but I'm struggling to put together a DB and the intermediary classes translating between tables and POJOs.

So far I've figured the following:

• I must have a Properties table that stores the unique ID for every object, along with another identifier for its type and a series of values common to every property (say, address, price, etc.)
• I must have a table for each entity type that can be independently listed as a real estate item (say, a house, a flat, a plot of land, a building, what have you). Each row on those tables will have a primary foreign key referencing its equivalent on the Properties table.

Now for the unexpected habanero. I decided to start sketching out my project on the basis of the RecyclerView Kotlin codelabs Google put together for newbies like me. Therein data is retrieved from the DB in this fashion:

this.plots = Transformations.map(database.RealtorDao.getPlots()) { it.asDomainModel() }

This works smoothly enough when the objects on the list the DB spits at you are all of one single kind, but what happens if you need them to be of different classes so that the adapter can tell them apart?

Or the only way around is just to build a gigantic table with about a hundred columns that will have nulls everywhere, and sort out objects ONLY AFTER they've been parsed in the previously described fashion?

Answer 1

I smashed my head against this wall until I got tired of hearing the squishing sound. I could not get a Room DB to return a list of objects of multiple classes, so I had to adopt a dirtier approach.

If I had worked just with the database classes then probably I could have hacked it, but trying to translate objects of such classes into POJOs to use instead complicated things somewhat.

The workaround I found was to make a master real estate class and accept that it would have lots and lots of null fields on the database. While a far cry from ideal, it works.

Database object classes:

open class DatabaseProperty
{
    @ColumnInfo(name = COL_TYPE)
    @SerializedName(COL_TYPE)
    @Expose
    var type: String? = null

    @ColumnInfo(name = COL_ADDRESS)
    @SerializedName(COL_ADDRESS)
    @Expose
    var address: String? = null

    @ColumnInfo(name = COL_OWNER)
    @SerializedName(COL_OWNER)
    @Expose
    var owner: String? = null

    @ColumnInfo(name = COL_PRICE_FINAL)
    @SerializedName(COL_PRICE_FINAL)
    @Expose
    var priceFinal: Long? = null

    @ColumnInfo(name = COL_PRICE_QUOTED)
    @SerializedName(COL_PRICE_QUOTED)
    @Expose
    var priceQuoted: Long? = null

    /**
     * No args constructor for use in serialization
     */
    constructor()

    @Ignore
    constructor
    (
        type: String,
        address: String,
        owner: String,
        priceFinal: Long,
        priceQuoted: Long
    ) : super() {
        this.type = type
        this.address = address
        this.owner = owner
        this.priceFinal = priceFinal
        this.priceQuoted = priceQuoted
    }
}

@Entity
(
    tableName = TABLE_RE,
    indices =
    [
        Index(value = [COL_RE_ID], unique = true)
    ],
    foreignKeys =
    [
        ForeignKey
        (
            entity = DatabaseRealEstate::class,
            parentColumns = arrayOf(COL_RE_ID),
            childColumns = arrayOf(COL_PARENT_ID),
            onDelete = ForeignKey.NO_ACTION
        )
    ]
)
data class DatabaseRealEstate
(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COL_RE_ID)
    var id: Int? = null,

    @ColumnInfo(name = COL_PARENT_ID)
    var parent_id: Int? = null,

    @Embedded(prefix = RE)
    var property: DatabaseProperty? = null,

    @ColumnInfo(name = COL_PARCEL_FRONT)        // Plot front
    @SerializedName(COL_PARCEL_FRONT)
    @Expose
    var front: Float? = null,

    @ColumnInfo(name = COL_PARCEL_SIDE)         // Plot side
    @SerializedName(COL_PARCEL_SIDE)
    @Expose
    var side: Float? = null,

    @ColumnInfo(name = COL_AREA)                // Plot area
    @SerializedName(COL_AREA)
    @Expose
    var area: Float? = null,

    @ColumnInfo(name = COL_CATASTER)
    @SerializedName(COL_CATASTER)
    @Expose
    var cataster: String? = null,

    @ColumnInfo(name = COL_ZONIFICATION)
    @SerializedName(COL_ZONIFICATION)
    @Expose
    var zonification: String? = null,
)

data class RealEstateWithSubunits
(
    @Embedded
    val re: DatabaseRealEstate? = null,

    @Relation
    (
        parentColumn = COL_RE_ID,
        entityColumn = COL_PARENT_ID,
        entity = DatabaseRealEstate::class
    )
    var subunits: List<DatabaseRealEstate>? = null,

    @Relation
    (
        parentColumn = COL_RE_ID,
        entityColumn = COL_PARENT_ID,
        entity = DatabaseChamber::class
    )
    var chambers: List<DatabaseChamber>? = null
)

fun List<RealEstateWithSubunits>.asRESUBDomainModel() : List<RealEstate>
{
    return map { obj ->
        RealEstate(
            id = obj.re!!.id!!,
            type = obj.re.property!!.type!!,
            address = obj.re.property!!.address!!,
            owner = obj.re.property!!.owner!!,
            priceFinal = obj.re.property!!.priceFinal!!,
            priceQuoted = obj.re.property!!.priceQuoted!!,
            parent_id = obj.re.parent_id,
            front = obj.re.front,
            side = obj.re.side,
            area = obj.re.area,
            cataster = obj.re.cataster,
            zonification = obj.re.zonification,
            chambers = obj.chambers!!.asChamberDomainModel(),
            subunits = obj.subunits!!.asREDomainModel()
        )
    }
}

fun List<DatabaseChamber>.asChamberDomainModel(): List<Chamber>
{
    return map {
        Chamber(
            id = it.id,
            parent_id = it.parent_id,
            front = it.front,
            side = it.side,
            area = it.area
        )
    }
}

fun List<DatabaseRealEstate>.asREDomainModel(): List<RealEstate>
{
    return map { obj ->
        RealEstate(
            id = obj.id!!,
            type = obj.property!!.type!!,
            address = obj.property!!.address!!,
            owner = obj.property!!.owner!!,
            priceFinal = obj.property!!.priceFinal!!,
            priceQuoted = obj.property!!.priceQuoted!!,
            parent_id = obj.parent_id,
            front = obj.front,
            side = obj.side,
            area = obj.area,
            cataster = obj.cataster,
            zonification = obj.zonification,
            chambers = ArrayList(),
            subunits = ArrayList()
        )
    }
}

Model object classes:

interface BaseProperty {
    var id: Int
    var type: String
    var address: String
    var owner: String
    var priceFinal: Long
    var priceQuoted: Long
}

data class RealEstate(
    override var id: Int = -1,
    override var type: String = "",
    override var address: String = "",
    override var owner: String = "",
    override var priceFinal: Long = 0,
    override var priceQuoted: Long = 0,
    var parent_id: Int?,
    var front: Float?,
    var side: Float?,
    var area: Float?,
    var cataster: String?,
    var zonification: String?,
    var subunits: List<RealEstate>? = null,
    var chambers: List<Chamber>? = null
) : BaseProperty
{
    fun hasParent() : Boolean
    {
        if (parent_id == null)
        {
            return false
        }
        return true
    }
}

I haven't yet found a better approach, so if someone does, I'm welcoming it with open arms.