I’m currently working on an Android project using Room for managing a large database with billions of records. I have two entities, Folder and File, each stored in separate tables. I’m using Room’s PagingSource to paginate these results, and I can retrieve them separately using the following queries:

@Query("""
    SELECT * FROM Folder 
    WHERE parentFolderId = :parentFolderId 
    ORDER BY 
    CASE 
        WHEN :sortBy = 'NAME' THEN name 
        WHEN :sortBy = 'TIME' THEN creationTime 
        WHEN :sortBy = 'SIZE' THEN size 
    END
""")
fun getFolders(parentFolderId: Long, sortBy: SortBy): PagingSource<Long, Folder>

@Query("""
    SELECT * FROM File 
    WHERE folderId = :parentFolderId 
    ORDER BY 
    CASE 
        WHEN :sortBy = 'NAME' THEN name 
        WHEN :sortBy = 'TIME' THEN creationTime 
        WHEN :sortBy = 'SIZE' THEN size
    END
""")
fun getFiles(parentFolderId: Long, sortBy: SortBy): PagingSource<Long, File>

Here are the Entities

@Serializable
@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Folder::class,
            parentColumns = ["folderId"],
            childColumns = ["parentFolderId"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Folder::class,
            parentColumns = ["folderId"],
            childColumns = ["folderId"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["folderId"]), Index(value = ["parentFolderId"])],
    primaryKeys = ["folderId", "parentFolderId"]
)
data class Folder(
    val folderId: Long = UUID.randomUUID().mostSignificantBits and Long.MAX_VALUE,
    val parentFolderId: Long? = null,
    val name: String,
    val size: Long,
    val userTag: UserTag? = null,
    val creationTime: Long,
    val lastOpenedTime: Long? = null,
    val userId: String
)

@Serializable
@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Folder::class,
            parentColumns = ["folderId"],
            childColumns = ["folderId"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index(value = ["fileId"]), Index(value = ["folderId"])],
    primaryKeys = ["fileId", "folderId"]
)
data class File(
    val fileId: Long = UUID.randomUUID().mostSignificantBits and Long.MAX_VALUE,
    val name: String,
    val size: Long,
    val fileType: FileType,
    val userTag: UserTag? = null,
    val creationTime: Long,
    val lastOpenedTime: Long? = null,
    val userId: String,
    val folderId: Long? = null,
    val chunkCount: Int,
    val fileHash: String
)

Problem:

These queries return Folder and File results separately as PagingSource. However, I need to combine these results into a single PagingSource and sort them together based on the selected criteria (e.g., name, creationTime, size).

Question:

How can I merge these two PagingSource queries into a single PagingSource so that I can paginate the combined results in Room? Is there an efficient way to handle this in Room, considering the scale of the data?

Any guidance or examples would be greatly appreciated!

Thanks in advance for your help!