How to store a list of integers in Room database so that it can be easily queried later?

Question

I'm storing podcast data in a Room database, where each podcast has a List<Int> called genreIds. I'd like to be able to store this in such a way that I can easily query it later by doing something like SELECT * FROM podcasts WHERE genreIds CONTAINS :genre, or whatever the command would be.

So what is the best way to store that list of Ints so that it can be easily queried later, and how would I do that using Room? I've used TypeConverters before, but that converts it to a string, which is difficult to query, so I'd like to be able to link it to another table or something that can be easily queried, I'm just not sure how to do that.

Thanks in advance.

AndrewBloom · Answer 1 · 2020-05-05T13:12:59.130

The data stored on a the db with Room, depends on the data class you use. If you specify a data class with an Int member, that will be an Int on the db.

Example:

data class TrackPlayedDB (
    @PrimaryKey(autoGenerate = true)
    val _id: Int = 0,
    val timesPlayed: Int,
    val artist: String,
    val trackTitle: String,
    val playDateTime: LocalDateTime
)

here timesPlayed will be an Int on the DB (as _id). You'll specify your data classes like the following, this will build the corresponding tables.

@Database(entities = [TrackPlayedDB::class], version = 1, exportSchema = false)
@TypeConverters(Converters::class)
abstract class MyRoomDatabase : BaseRoomDatabase() {

Edit: Following author's comment, I stand corrected i didn't get the question right.

Author actually asks how to store a List<Int> as field on a table. There are 2 solutions to do that: one, as Author suggests, is to store the List as String and use Like keyword to write queries with a clause like the following:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
   OR column1 LIKE '%word2%'
   OR column1 LIKE '%word3%'

as a simple search on SO would have shown: SQL SELECT WHERE field contains words

The Author says he used TypeConverters so i'll skip how to convert a List<Int> into a string

The other solution to this problem is to realise that nothing was understood about the theory of Transactional Databases. In fact, when you have a many-to-many relationship, as in the case of podcast and genre, theory dictates that you build a table that links the ids of podcasts and the ids of genres, as it is explained here: https://dzone.com/articles/how-to-handle-a-many-to-many-relationship-in-datab and other countless books, videos and blogs. This benefits the db with added clarity, performance and scalability. Bottom line, Author's db design is wrong.

I'm not sure you understood my question. I know how to make Room tables, and my data class has a val called genreIds that is of type List, but my question is about how to store that in the database without making it difficult to query (which is the case when using TypeConverters) — Cameron, May 04 '20 at 17:51
Sorry @cpgreen2, i didn't get what you asked cause it doesn't make sense on sql world. I'll edit my answer to answer you better. — AndrewBloom, May 05 '20 at 12:59
You basically just updated it to tell me that my design is wrong. I know that my design is wrong, which is why I asked the question, and my "simple search on SO" did return that answer, but as you said, I'm trying to avoid using TypeConverters, so it isn't helpful. This is also specific to Room database for android, as my title and tags show, not SQL. I will answer the question myself with other resources that I have since found helpful to my specific question. — Cameron, May 05 '20 at 13:29
Hi, I think you didn't understand my answer. As i said, there are 2 approaches to the problem. One is to convert the list to a big string. This can be done, and may work in specific situations. But that's wrong in the general case. Point is you missed to identify a 'many-to-many' relationship. SQL databases are not meant to store a List of data in a field in a row. Lists are meant to be vertical, stored in a table. One element each row. Not horizontal, as you're trying to do. You need to implement a table for Genres and a Table for indices coupling, then you can query with joins. — AndrewBloom, May 05 '20 at 14:28

score -2 · Accepted Answer · answered May 05 '20 at 13:34

I found [this article on Medium][1] that I found very helpful. What I'm trying to do is a many to many relationship, which in this case would be done something like the following:

Podcast class:
@Entity(tableName = "podcasts")
data class Podcast(
    @PrimaryKey
    @ColumnInfo(name = "podcast_id")
    val id: String,
    // other fields
}

Genre class:

@Entity(tableName = "genres")
data class Genre (
    @PrimaryKey
    @ColumnInfo(name = "genre_id")
    val id: Int,
    val name: String,
    val parent_id: Int
)

PodcastDetails class:

data class PodcastDetails (
    @Embedded
    val podcast: Podcast,
    @Relation(
        parentColumn = "podcast_id",
        entityColumn = "genre_id",
        associateBy = Junction(PodcastGenreCrossRef::class)
    )
    val genres: List<Genre>
)

PodcastGenreCrossRef:

@Entity(primaryKeys = ["podcast_id", "genre_id"])
data class PodcastGenreCrossRef (
    val podcast_id: Int,
    val genre_id: Int
)

And access it in the DAO like this:

@Transaction
@Query(SELECT * FROM podcasts)
fun getPodcastsWithGenre() : List<PodcastDetails>

  [1]: https://medium.com/androiddevelopers/database-relations-with-room-544ab95e4542

How to store a list of integers in Room database so that it can be easily queried later?

2 Answers2