How to query a list inside Room database in android?

This is the class that i'm saving inside room database:

@Entity
data class Person(
    val name : String = "Bruno",
    val age : Int = 23,
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    val hobbies : ArrayList<String> = arrayListOf("Basquete","Academia","Musica","Anatomia")
)

I already added type converters so it is saving successfully. What i want is to query results by what the hobbies list has. E.g:

select * from person where hobbies in ("Basquete")

I wanted to select all person objects that has "Basquete" inside the hobbies list, but this query is returning empty. What am i doing wrong?

Solution

Their is no concept of a list in a row of a table, a column holds a single value.

Having a Type Converter will store the list of hobbies as a single value (column), as such IN will check the entire (the EXACT) value (the full list and whatever encoding is used, this dependant upon the Type Converter that converters the list to the single value).

As such it is likely that using IN, is not going to be of use.
As an example the TypeConverter may convert to something along the lines of ["Basquete","Academia","Musica"] (conversion to JSON string via com.google.code.gson dependcy)

To demonstrate using data loaded and using App Inspection then with

Now consider an adaptation of your query, it being SELECT *, hobbies in ("Basquete") AS TEST1, hobbies IN ('Basquete') AS TEST2, "Basquete" IN(hobbies) AS TEST3,'Basquete' IN (hobbies) AS TEST4 ,hobbies LIKE '%Basquete%' AS TEST5 FROM person WHERE person.id = 1;

Then via App Inspection, the result is

So you could use WHERE hobbies LIKE '%Basquete%'

NOTE the enclosing single quotes, which delineates sting values, (not double quotes).
note searches with the % char as the first character will result in an in-efficient scan for the data.
This assumes that the TypeConverter is converting the List of Hobbies to a String representation of the data. If the TypeConverter converted the list to a byte-stream (e.g. ByteArray) then the above would not work.

However, if you are looking for multiple hobbies, then the complexity increases. e.g. WHERE hobbies LIKE '%Basquete%' OR hobbies LIKE '%Academia%' (to find those with either, using AND instead of OR would return only those with both).

The more correct solution where IN could be utilised would be to have a table that contains the hobbies and as the relationship would be a many-many relationship (a person could have many hobbies and people could have the same hobbies) have a third mapping table for the many-many relationship.

Example of the More Correct way

All the @Entity annotated classes and also a POJO for getting a Person with their list of hobbies:-

@Entity
data class Person(
    val name : String = "Bruno",
    val age : Int = 23,
    @PrimaryKey(autoGenerate = true) val id: Int = 0,
    //val hobbies : ArrayList<String> = arrayListOf("Basquete","Academia","Musica","Anatomia") /*<<<<<<<<<< no need */
    /* Also no need for type converters */
)

/* The suggested (more correct) Hobby table */
@Entity(
    indices = [
        Index(value = ["hobbyName"], unique = true)
    ]
)
data class Hobby(
    @PrimaryKey
    var hobbyId: Long?=null,
    var hobbyName: String /* UNIQUE Index so no duplicated hobby names */
)
/* The Mapping Table
    Note also know as reference table, associative table and other names
 */

/* POJO for extracting a Person with thier list of Hobbies */
data class PersonWithHobbies(
    @Embedded
    var person: Person,
    @Relation(
        entity = Hobby::class,
        parentColumn = "id",
        entityColumn = "hobbyId",
        associateBy = Junction(
            value = PersonHobbyMap::class,
            parentColumn = "personIdMap",
            entityColumn = "hobbyIdMap"

        )
    )
    var hobbies: List<Hobby>
)

/* This is the Mapping Table that maps people to hobbies */
@Entity(
    primaryKeys = ["personIdMap","hobbyIdMap"],
    /* Option but suggested foreign key constraint definitions to enforce and maintain referential integrity
     */
    foreignKeys = [
        /* For the reference to the Person */
        ForeignKey(
            entity = Person::class, /* The parent @Entity annotated class */
            parentColumns = ["id"], /* The column in the parent that is referenced */
            childColumns = ["personIdMap"], /* the column in this table that holds the reference to the parent */
            onDelete = ForeignKey.CASCADE, /* will delete rows in the table if the parent is deleted */
            onUpdate = ForeignKey.CASCADE /* will update the value, if the value (id) in the parent is changed */
        ),
        /* For the reference to the Hobby */
        ForeignKey(
            entity = Hobby::class,
            parentColumns = ["hobbyId"],
            childColumns = ["hobbyIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class PersonHobbyMap(
    var personIdMap: Long,
    @ColumnInfo(index = true) /* more efficient to have index on the 2nd column (first is indexed as first part of the Primary key) */
    var hobbyIdMap: Long
)

refer to the comments

An @Dao annotated interface with functions to insert data and also to extract persons (with and without their hobbies) if they have any of the hobbies passed (a query for using the hobby id's and another for using the hobby names)

note that the way that Room works ALL hobbies are retrieved per person (for the first 2 queries) that is extracted.

@Dao
interface TheDaos {

    /* Inserts */
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(person: Person): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(hobby: Hobby): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(personHobbyMap: PersonHobbyMap): Long

    /* Query for retrieving the Person and their hobbies if they have hobbies according to the provided list of hobbyId's */
    @Transaction
    @Query("SELECT DISTINCT person.* FROM person JOIN personHobbyMap ON person.id = personHobbyMap.personIdMap JOIN hobby ON personHobbyMap.hobbyIdMap = hobby.hobbyId WHERE hobbyId IN(:hobbyIdList);")
    fun getPersonsWithHobbiesIfHobbiesInListOfHobbyIds(hobbyIdList: List<Long>): List<PersonWithHobbies>
    /* Query for retrieving the Person and their hobbies if they have hobbies according to the provided list of hobby names's */
    @Transaction
    @Query("SELECT DISTINCT person.* FROM person JOIN personHobbyMap ON person.id = personHobbyMap.personIdMap JOIN hobby ON personHobbyMap.hobbyIdMap = hobby.hobbyId WHERE hobbyName IN(:hobbyNameList);")
    fun getPersonsWithHobbiesIfHobbiesInListOfHobbyNames(hobbyNameList: List<String>): List<PersonWithHobbies>

    /* The equivalent of the above 2 queries BUT only gets the Person (without Hobbies) */
    @Query("SELECT DISTINCT person.* FROM person JOIN personHobbyMap ON person.id = personHobbyMap.personIdMap JOIN hobby ON personHobbyMap.hobbyIdMap = hobby.hobbyId WHERE hobbyId IN(:hobbyIdList);")
    fun getPersonsIfHobbiesInListOfHobbyIds(hobbyIdList: List<Long>): List<Person>
    @Query("SELECT DISTINCT person.* FROM person JOIN personHobbyMap ON person.id = personHobbyMap.personIdMap JOIN hobby ON personHobbyMap.hobbyIdMap = hobby.hobbyId WHERE hobbyName IN(:hobbyNameList);")
    fun getPersonsIfHobbiesInListOfHobbyNames(hobbyNameList: List<String>): List<Person>
    
    /* NOTE
        without DISTINCT or without only selecting the columns for the Person only,
         if a Person has multiple matches then that person would be extracted multiple times.
     */

}

The @Database annotated class (note .allowMainThreadQueries used for brevity and convenience):-

@Database(entities = [Person::class,Hobby::class,PersonHobbyMap::class], exportSchema =  false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getTheDaos(): TheDaos

    companion object {
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Finally activity code that puts it all together, adding some data and then querying the data selecting only the Person (with and then without their list of hobbies) :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: TheDaos
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getTheDaos()

        val h1 = dao.insert(Hobby(hobbyName = "Basquete"))
        val h2 = dao.insert(Hobby(hobbyName = "Academia"))
        val h3 = dao.insert(Hobby(hobbyName = "Musica"))
        val h4 = dao.insert(Hobby(hobbyName = "Anatomia"))
        val h5 = dao.insert(Hobby(hobbyName = "other"))
        val h6 = dao.insert(Hobby(hobbyName = "another"))
        val h7 = dao.insert(Hobby(hobbyName = "yet another"))

        val p1 = dao.insert(Person(name = "Bruno", age = 23))
        val p2 = dao.insert(Person(name = "Sarah", age = 21))
        val p3 = dao.insert(Person(name = "Tom", age = 22))
        val p4 = dao.insert(Person(name = "Mary", age = 20))
        val p5 = dao.insert(Person(name = "Freda", age = 19))

        /* Bruno has hobbies Basquete, Academia, Musica and Anatomia */
        dao.insert(PersonHobbyMap(p1,h1))
        dao.insert(PersonHobbyMap(p1,h2))
        dao.insert(PersonHobbyMap(p1,h3))
        dao.insert(PersonHobbyMap(p1,h4))
        /* Sarah has hobbies Academia, Anatomia and another */
        dao.insert(PersonHobbyMap(p2,h2))
        dao.insert(PersonHobbyMap(p2,h4))
        dao.insert(PersonHobbyMap(p2,h6))
        /* Tom has hobbies Basquete, Musica, other and yet another */
        dao.insert(PersonHobbyMap(p3,h1))
        dao.insert(PersonHobbyMap(p3,h3))
        dao.insert(PersonHobbyMap(p3,h5))
        dao.insert(PersonHobbyMap(p4,h7))
        /* Mary has hobbies other, another and yet another */
        dao.insert(PersonHobbyMap(p4,h5))
        dao.insert(PersonHobbyMap(p4,h6))
        dao.insert(PersonHobbyMap(p4,h7))
        /* Freda has no Hobbies */

        val sb: StringBuilder = java.lang.StringBuilder()


        /* Persons and their hobbies for those that have Basquete or Academia in their list of hobbies (hobbies to include via list of hobbyId's)*/
        /* i.e. Bruno (both) and Sarah (Academia) and Tom (both) */
        for(pwh in dao.getPersonsWithHobbiesIfHobbiesInListOfHobbyIds(listOf(h1,h2))) {
            sb.clear()
            for (h in pwh.hobbies) {
                sb.append("\n\t${h.hobbyName}")
            }
            Log.d("DBINFO_TEST1","Person is ${pwh.person.name} and has ${pwh.hobbies.size} hobbies. They are:- ${sb}")
        }

        /* Persons and their hobbies for those that have Basquete or Musica in their list of hobbies (hobbies to include via list of Hobby names)*/
        /* i.e. Bruno (both) and Tom (Musica) */
        for(pwh in dao.getPersonsWithHobbiesIfHobbiesInListOfHobbyNames(listOf("Basquete","Musica"))) {
            sb.clear()
            for (h in pwh.hobbies) {
                sb.append("\n\t${h.hobbyName}")
            }
            Log.d("DBINFO_TEST2","Person is ${pwh.person.name} and has ${pwh.hobbies.size} hobbies. They are:- ${sb}")
        }
    }
}

Result of running the above (i.e. output to the log) :-

2022-07-28 09:35:36.954 D/DBINFO_TEST1: Person is Bruno and has 4 hobbies. They are:- 
        Basquete
        Academia
        Musica
        Anatomia
2022-07-28 09:35:36.954 D/DBINFO_TEST1: Person is Tom and has 3 hobbies. They are:- 
        Basquete
        Musica
        other
2022-07-28 09:35:36.954 D/DBINFO_TEST1: Person is Sarah and has 3 hobbies. They are:- 
        Academia
        Anatomia
        another
        
        
2022-07-28 09:35:36.958 D/DBINFO_TEST2: Person is Bruno and has 4 hobbies. They are:- 
        Basquete
        Academia
        Musica
        Anatomia
2022-07-28 09:35:36.959 D/DBINFO_TEST2: Person is Tom and has 3 hobbies. They are:- 
        Basquete
        Musica
        other

In addition to utilising the IN expression/clause, this recommended way of storing the data, although a little more complex, offers advantages (at least from a relational database perspective) such as:-

the reduction of bloat (the space taken up by the additional data on converting to/from JSON strings such as delimiters)
The data is normalised from a data redundancy aspect e.g. instead of storing Basquete n times it is stored just once and referenced.
- if referenced by rowid (in short and integer primary key, as per the example) then SQLite accessing such data via the index is up to twice as fast.
  - might be unnoticeable for a small amount of data
there will likely be increased efficiency due to the reduced space usage (more data can be buffered)
storage space taken up will be less or will contain more data for the same size (SQLite stores chunks of data which may contain free space)