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>>

}

Answer 2

There is no easy way, Relations in Room don't support what you want out-of-the-box. Nevertheless I can suggest you to try some way (I'm not sure it will works but you might try):

1. Add DatabaseView (you can read what is it in link), let's say TopEpisodesDataView. Its carcass should be like that:

@DatabaseView("select ...") <-- There you should put query that returns only last 10 episodes for each podcast
data class TopEpisodesDataView(
    @Embedded
    val episode: Episode
)

2. In your Relation you should replace Episode table with TopEpisodesDataView :

data class PodcastWrapper(
    @Embedded
    val data: Podcast,

    @Relation(parentColumn = "remote_podcast_feed_location", entityColumn = "episode_remote_podcast_feed_location")
    val episodes: List<TopEpisodesDataView>) 

So it seems the only problem is how in step 1 to get the needed query. For that try to follow next Stackoverflow's links - link1 or link2 (or similar).

But... even if it works I'm not sure that episodes in the result would be sorted chronologically, since Room's Relation doesn't guarantee the order of child tables' rows (so you can get last 10 episodes, but first will be episode23, then - episode 21, then episode 29 and so on). So maybe your choice is to do all the stuff manually (to join tables, to loop result and to make it work as you want).

Answer 3

You have to implement the query by using query terms like ORDER BY or LIMIT.

@Transaction
@Query("SELECT * FROM "+ Constants.TABLE_NAME_TRANSLATION+" ORDER BY id DESC LIMIT :limit  ")
 LiveData<List<TranslationHistory>> getLimitList(int limit);