Android Room: Looking for DAO that limits the number of elements in @Embedded List

Question

I am developing an Android podcast application, that stores data in a Room database. The database stores Podcasts and Epidodes. Here are the respective classes used:

Episode.kt

@Entity(tableName = "episodes", indices = arrayOf(Index(value = ["media_id"], unique = true)))
data class Episode (

    @PrimaryKey
    @ColumnInfo (name = "media_id")
    val mediaId: String,

    @ColumnInfo (name = "title")
    val title: String,

    @ColumnInfo (name = "publication_date")
    val publicationDate: Date,
    
    // used to define relation between episode and podcast
    @ColumnInfo (name = "episode_remote_podcast_feed_location")
    val episodeRemotePodcastFeedLocation: String,

    ...)
{...}

Podcast.kt

@Entity(tableName = "podcasts", indices = arrayOf(Index(value = ["remote_podcast_feed_location"], unique = true)))
data class Podcast(

    @PrimaryKey
    @ColumnInfo (name = "remote_podcast_feed_location") val remotePodcastFeedLocation: String,

    @ColumnInfo (name = "name") val name: String
    ...)
{ ... }

I additionally created a wrapper class, that defines the relation between Podcasts and Episodes.

PodcastWrapper.kt

data class PodcastWrapper(
    @Embedded
    val data: Podcast,

    @Relation(parentColumn = "remote_podcast_feed_location", entityColumn = "episode_remote_podcast_feed_location")
    val episodes: List<Episode>) 
{ ... }

To get ALL Podcasts with grouped together with ALL their Episode I use the following DAO, that returns a List of PodcastWrapper as LiveData. That works fine.

PodcastDao.kt

@Dao
interface PodcastDao {

    @Transaction
    @Query("SELECT * FROM podcasts")
    fun getAllPodcastsLiveData(): LiveData<List<PodcastWrapper>>

}

MY PROBLEM

A podcast often has 100+ episodes. I only need to observe the last 10 episodes (using LiveData). I need a DAO that returns ALL Podcasts grouped together with the 10 most recent Episodes - ideally as LiveData<List<PodcastWrapper>>. I cannot figure out how that would be possible. Any ideas?

Answer 1

The solution sergiy tikhonov suggested worked great. For the sake of completeness, here the code I used to implement the suggestion:

EpisodeMostRecentView.kt (new)

@DatabaseView("SELECT * FROM episodes e WHERE ( SELECT count(*) from episodes e1 WHERE e1.episode_remote_podcast_feed_location = e.episode_remote_podcast_feed_location AND e1.publication_date >= e.publication_date ) <= 5")
data class EpisodeMostRecentView (
    @Embedded
    val data: Episode)
{ ... }

The query used to create the above DatabaseView returns a (smaller) list of Episodes - only the most recent five for each Podcast (identified by episode_remote_podcast_feed_location).

SELECT * 
FROM episodes e 
WHERE ( 
    SELECT count(*) 
    from episodes e1 
    WHERE 
        e1.episode_remote_podcast_feed_location = e.episode_remote_podcast_feed_location 
        AND e1.publication_date >= e.publication_date 
) <= 5

PodcastDatabase.kt (changed)

added views = arrayOf(EpisodeMostRecentView::class)

@Database(entities = arrayOf(Podcast::class, Episode::class), views = arrayOf(EpisodeMostRecentView::class),version = 1)
abstract class PodcastDatabase : RoomDatabase() {

    abstract fun podcastDao(): PodcastDao
    ...
}

PodcastWithRecentEpisodesWrapper.kt (new)

uses List<EpisodeMostRecentView> while PodcastWrapper uses List<Episode>

data class PodcastWithRecentEpisodesWrapper(
    @Embedded
    val data: Podcast,

    @Relation(parentColumn = "remote_podcast_feed_location", entityColumn = "episode_remote_podcast_feed_location")
    val episodes: List<EpisodeMostRecentView>) 
{ ... }

PodcastDao.kt (changed)

LiveData<List<PodcastWithRecentEpisodesWrapper>> instead of LiveData<List<PodcastWrapper>>

@Dao
interface PodcastDao {

    @Transaction
    @Query("SELECT * FROM podcasts")
    fun getFiveMostRecentPodcastsLiveData(): LiveData<List<PodcastWithRecentEpisodesWrapper>>

}