Paging library + room not fetching @relation

Question

In an app I'm working on, I need to very simply fetch data from a Room database. There are relations involved. Everything was working perfectly with simple queries returning LiveData, however, the size of the data that needs to be retrieved grew much larger than expected and it also contains blobs (images), which can make the query very slow. I've resolved to using the Paging library, implemented as follows, but for some reason the @relation annotation simply doesn't work anymore.

The entity fetched is a DTO, looks basically like this:

data class EntityOtherAnotherDTO(
        var id: Long? = null,
        var name: String? = null,
        ...,

        @Relation(parentColumn = "id", entityColumn = "entity_id", entity = OtherEntity::class)
        var others: List<OtherEntity>,

        @Relation(parentColumn = "id", entityColumn = "entity_id", entity = AnotherEntity::class)
        var anothers: List<AnotherEntity>
) 

The query:

 @Query("SELECT * FROM other
        JOIN entity ON entity.id = other.entity_id
        JOIN another ON entity.id = another.entity_id
        WHERE entity.deleted = 0
        ORDER BY
        CASE WHEN other.some_column IS NULL THEN 1 ELSE 0 END,
        other.some_column ASC,
        entity.some_other_column DESC")
    fun getAllEntityOtherAnotherDTOs(): DataSource.Factory<Int, EntityOtherAnotherDTO>

When the query was like this: fun getAllEntityOtherAnotherDTOs(): LiveData<List<EntityOtherAnotherDTO>> everything worked just fine. The results were ordered as required and all data was fetched, including the lists annotated with @relation. But after changing the return type to DataSource.Factory and of course implementing a paging adapter, the relations return empty.

The ordering still works perfectly, the query appears to be working exactly as before, the paging also works, but the data is simply missing. All the columns in the entity table are there (name, some_other_column etc.), the relations are the only but major problem.

(I can provide further details about the paging implementation, if that's of any relevance.)

Answer 1

Turns out this is an issue in Room which can happen even when you don't use the Paging library, given a large-ish (hundreds of results+) query.

There is no solution, but there is a workaround for 1:1 relations: using @embedded instead of @relation. That can however complicate things through the need of setting a prefix and enumerating all columns in the query and giving them aliases. That's pain but such is life. Room sucks.

Or if the joined entity doesn't have too many columns and there aren't any duplicate names, it works just as fine to copy those columns/properties in the DTO that is returned by the query.