Android Room Database relationships

Question

I have created a database in room android with three entities

    @Entity(tableName = "categories")
data class Category(

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int,

    @ColumnInfo(name = "category_name")
    val categoryName: String,

    @ColumnInfo(name = "category_id")
    val categoryId: Int
)

@Entity(tableName = "quotes")
data class Quote(

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int,

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

    @ColumnInfo(name = "category_id")
    val categoryId: Int
)

@Entity(tableName = "favourites")
data class Favourite(

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    val id: Int,

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

    @ColumnInfo(name = "category_name")
    val categoryName: String,

//    @ColumnInfo(name = "date")
//    val date: Long
)

Now my question is how do i create relationship between two tables in this example i would like to create relationship between Category and Quote so that i can order quotes by categoryId. Like this sql statement

SELECT * FROM quotes INNER JOIN categories ON quotes.category_id = categories.id WHERE categories.id = :categoryId

I went through the official documentation but it went over my head , i couldn't figure out how to do it. I don't expect anyone to write code for me what i want is the simplest explanation possible.

P.s It is my first time with Room database

Answer 1

For a 1 to many (a single category can have many quotes) you have a POJO where the single is a field embedded using the @Embedded annotation and the many is a field that is a List/Array using the @Relation annotation.

Room generates the equivalent of the JOIN query (equivalent) and is fine if you want ALL the related children.

So you want something like:-

data class CategoryWithItsQuotes(
    @Embedded
    val category: Category,
    @Relation(
        entity = Quote::class,
        parentColumn = "category_id",
        entityColumn = "category_id"
    )
    val quoteList: List<Quote>
)

Along with a function in an @Dao annotated interface such as:-

@Transaction
@Query("SELECT * FROM categories WHERE category_id=:category_id")
fun getCategoryWithItsQuotes(category_id: Int): List<CategoryWithItsQuotes>

i.e. there is no need for the JOIN as Room builds the method to get the related Quotes, for the Category(s) via queries that it builds and executes based upon the @Relation annotation and hence why the @Transaction.

If a many-many relationship (as you probably want for the Category/Favourite) then you have a table (associative/reference/mapping and other names table) that has two columns, one for associating/referencing/mapping a unique row in one of the tables and the other column for associating/referencing/mapping a unique row in the other table. The 2 columns would both make up the primary key (a composite primary key). With Room you then @Embed one table and @Relation the other table using the associateBy parameter to specify the Junction (the table and respective columns).

Room (again with it's limitation of retrieving ALL children) allows hierarchical use of such POJO's e.g. you could have POJO:-

data class CategoryWithFavourites(
    @Embedded
    val category: Category,
    @Relation(
        entity = Favourite::class,
        parentColumn = "category_name",
        entityColumn = "category_name"
    )
    val favouriteList: List<Favourite>
)

and then :-

data class QuoteWithCategoryAndFavourite(
    @Embedded
    val quote: Quote,
    @Relation(
        entity = Category::class,
        parentColumn = "category_id",
        entityColumn = "category_id"
    )
    val category: CategoryWithFavourites
)

And then have something like:-

@Transaction
@Query("SELECT * FROM quotes WHERE id=:id")
fun getSingleQuoteWithCategoryAndFavourites(id: Int): List<QuoteWithCategoryAndFavourite>

• Note this is not the suggested many-many (which you probably want so that a category can have many favourites and a favourite can have of many categories).
  • as such this would cope with a favourite only ever being associated with a single category.

HOWEVER

There is a flaw with your Entities that may well result in unexpected results, that is you are allowing for multiple category_id's that are the same (the id column is the only column that uniquely identifies a category see demo)

DEMO

To demonstrate the above (POJO's and function) consider the additional functions in @Dao annotated interface AllDAOs (one for convenience/brevity):-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(category: Category): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(quote: Quote): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(favourite: Favourite): Long

The following @Database annotated abstract class:-

@Database(entities = [Category::class,Quote::class,Favourite::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs

    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() /* for brevity of demo */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

And the following activity code:-

class MainActivity : AppCompatActivity() {

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

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

        /* Add some categories */
        val c1CId = 100;
        val c1Id = dao.insert(Category(categoryName = "CAT1", categoryId = c1CId,id =0))
        val c2CId = 900
        val c2Id = dao.insert(Category(id=0, categoryName = "CAT2", categoryId = c2CId))
        val c3CId=900 /* ouch!!!!!!!!!  same category_id to demonstrate flaw */
        val c3Id = dao.insert(Category(id=0, categoryName = "CAT3", categoryId = c3CId))

        dao.insert(Quote(id=0, quote = "Quote001",c1CId))
        dao.insert(Quote(id=0, quote = "Quote002",c2CId))
        dao.insert(Quote(id=0, quote = "Quote003",c3CId))
        dao.insert(Quote(id=0, quote = "Quote004", categoryId = c1CId))
        dao.insert(Quote(id=0, quote = "Quote005", categoryId = c1CId))
        dao.insert(Quote(id=0, quote = "Quote006", categoryId = c1CId))
        dao.insert(Quote(id=0, quote = "Quote007", categoryId = c2CId))
        dao.insert(Quote(id=0, quote = "Quote008", categoryId = c2CId))
        dao.insert(Quote(id=0, quote = "Quote009", categoryId = c3CId))

        logCatWithQuotes(c1CId)
        logCatWithQuotes(c2CId)
        logCatWithQuotes(c3CId)

    }

    private fun logCatWithQuotes(catid: Int) {
        for(cwq in dao.getCategoryWithItsQuotes(catid)) {
            val sb = StringBuilder()
            for (q in cwq.quoteList) {
                sb.append("\n\tQuote is ${q.quote} ID is ${q.id} CATID is ${q.categoryId}")
            }
            Log.d(
                "DBINFO_CAT${catid}",
                "CAT is ${cwq.category.categoryName} ID is ${cwq.category.id} CATID is ${cwq.category.categoryId}. It has ${cwq.quoteList.size} quotes. They are:-${sb}")
        }
    }
}

When run the result output to the log is:-

2023-04-03 11:16:43.016 D/DBINFO_CAT100: CAT is CAT1 ID is 1 CATID is 100. It has 4 quotes. They are:-
        Quote is Quote001 ID is 1 CATID is 100
        Quote is Quote004 ID is 4 CATID is 100
        Quote is Quote005 ID is 5 CATID is 100
        Quote is Quote006 ID is 6 CATID is 100
2023-04-03 11:16:43.020 D/DBINFO_CAT900: CAT is CAT2 ID is 2 CATID is 900. It has 5 quotes. They are:-
        Quote is Quote002 ID is 2 CATID is 900
        Quote is Quote003 ID is 3 CATID is 900
        Quote is Quote007 ID is 7 CATID is 900
        Quote is Quote008 ID is 8 CATID is 900
        Quote is Quote009 ID is 9 CATID is 900
2023-04-03 11:16:43.020 D/DBINFO_CAT900: CAT is CAT3 ID is 3 CATID is 900. It has 5 quotes. They are:-
        Quote is Quote002 ID is 2 CATID is 900
        Quote is Quote003 ID is 3 CATID is 900
        Quote is Quote007 ID is 7 CATID is 900
        Quote is Quote008 ID is 8 CATID is 900
        Quote is Quote009 ID is 9 CATID is 900
2023-04-03 11:16:43.025 D/DBINFO_CAT900: CAT is CAT2 ID is 2 CATID is 900. It has 5 quotes. They are:-
        Quote is Quote002 ID is 2 CATID is 900
        Quote is Quote003 ID is 3 CATID is 900
        Quote is Quote007 ID is 7 CATID is 900
        Quote is Quote008 ID is 8 CATID is 900
        Quote is Quote009 ID is 9 CATID is 900
2023-04-03 11:16:43.025 D/DBINFO_CAT900: CAT is CAT3 ID is 3 CATID is 900. It has 5 quotes. They are:-
        Quote is Quote002 ID is 2 CATID is 900
        Quote is Quote003 ID is 3 CATID is 900
        Quote is Quote007 ID is 7 CATID is 900
        Quote is Quote008 ID is 8 CATID is 900
        Quote is Quote009 ID is 9 CATID is 900

First thing is that it appears to be working and certainly demonstrates the answer to the question in regard to handling the relationship (albeit open to misuse).

i.e. CAT1 is as expected it has the 4 quotes assigned.

However, CAT2 and CAT3 have too many quotes and are also repeated, that is each is the sum of both due to them having the same non-unique category_id 900. So:-

• a search for CAT2 via the category_id finds CAT2 and CAT3 and vice-versa and of course the quotes for each will have the same reference and thus reference both.

In short the id field/column could do all that is required of the category_id field/column and would be unique and thus not result in such an issue. Furthermore there would be a space saving and perhaps an efficiency gain as the category_id column is not needed and in fact a hinderance as has been shown.