android android-room relationship android-room-relation

Android Room belongsTo (one to one) relationship

Let's say I have an Article table like this:

Article {
   id;
   title;
   content;
   main_reference_id;
   secondary_reference_id;
}

And there is a Reference table somewhat like this:

Reference {
   id;
   other_reference_related_columns...;
}

Now I want to fetch the Article while also fetching main reference, secondary reference with another POJO like this:

data class ArticleFull(
    @Embedded
    val article: article,
    @Relation(parentColumn = "main_reference_id", entityColumn = "id")
    val main_reference: Reference,
    @Relation(parentColumn = "secondary_reference_id", entityColumn = "id")
    val other_reference: Reference
)

But I'm not sure what I wrote is the right usage of @Relation annotation or not.

N.B.: I'm from the Laravel/Eloquent background, So I'm more familiar with these belongsTo, hasOne, hasMany, belongsToMany, and so on relationship types.

Thanks.

Solution

But I'm not sure what I wrote is the right usage of @Relation annotation or not.

Yes that is fine.

Here's a working example. That shows the use of the ArticleFull POJO:-

First the Entities (Tables):-

Reference :-

@Entity
data class Reference(
    @PrimaryKey
    val id: Long? = null,
    val other_data: String
)

Article :-

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Reference::class,
            parentColumns = ["id"],
            childColumns = ["main_reference_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Reference::class,
            parentColumns = ["id"],
            childColumns = ["secondary_reference_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Article(
    @PrimaryKey
    val id: Long? = null,
    val title: String,
    val content: String,
    @ColumnInfo(index = true)
    val main_reference_id: Long,
    @ColumnInfo(index = true)
    val secondary_reference_id: Long
)

Foreign Key constraints added, they help to enforce referential integrity, they are optional. The onDelete and onUpdate are optional within the Foreign Key.

An @Dao class (abstract class rather than interface, abstract class is more versatile) ArticleAndReferenceDao :-

@Dao
abstract class ArticleAndReferenceDao {
    @Insert
    abstract fun insert(reference: Reference): Long
    @Insert
    abstract fun insert(article: Article): Long
    @Transaction
    @Query("SELECT * FROM article")
    abstract fun getAllArticleFull(): List<ArticleFull>
    @Transaction@Query("SELECT * FROM article WHERE id=:articleId")
    abstract fun getArticleFullByArticleId(articleId: Long): List<ArticleFull>
}

An @Database class ArticleDatabase :-

@Database(entities = [Reference::class,Article::class],version = 1)
abstract class ArticleDatabase: RoomDatabase() {
    abstract fun getArticleAndReferenceDao(): ArticleAndReferenceDao

    companion object {

        @Volatile
        private var instance: ArticleDatabase? = null

        fun getArticleDatabaseInstance(context: Context): ArticleDatabase {
            if(instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    ArticleDatabase::class.java,
                    "article.db"
                )
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as ArticleDatabase
        }
    }
}

Finally some Activity code , noting that for convenience and brevity .allowMainThreadQueries has been used allow the code to be run on the main thread :-

class MainActivity : AppCompatActivity() {

    lateinit var articleDatabase: ArticleDatabase
    lateinit var articleAndReferenceDao: ArticleAndReferenceDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        articleDatabase = ArticleDatabase.getArticleDatabaseInstance(this)
        articleAndReferenceDao = articleDatabase.getArticleAndReferenceDao()

        val ref1 = articleAndReferenceDao.insert(Reference(other_data = "Reference1"))
        val ref2 = articleAndReferenceDao.insert(Reference(other_data = "Reference2"))
        val ref3 = articleAndReferenceDao.insert(Reference(other_data = "Reference3"))
        val ref4 = articleAndReferenceDao.insert(Reference(other_data = "Reference4"))

        articleAndReferenceDao.insert(Article(title = "Article1",main_reference_id = ref1,secondary_reference_id = ref2, content = "Content for Article1"))
        articleAndReferenceDao.insert(Article(title = "Article2", main_reference_id = ref3, secondary_reference_id = ref4,content = "Content for Article2"))

        // AND/OR

        articleAndReferenceDao.insert(
            Article(
            title = "Article3",
                content = "Content for Article 3",
                main_reference_id = articleAndReferenceDao.insert(Reference(other_data = "Reference5")),
                secondary_reference_id = articleAndReferenceDao.insert(Reference(other_data = "reference6"))
            )
        )

        for(d: ArticleFull in articleAndReferenceDao.getAllArticleFull()) {
            Log.d("ARTICLEINFO"," Article is ${d.article.content} ID is ${d.article.id} " +
                    "\n\tMain Reference is ${d.main_reference.other_data} ID is ${d.main_reference.id}" +
                    "\n\tSecondary  Reference is ${d.other_reference.other_data} ID is ${d.other_reference.id}")
        }  
    }
}

Running the above results in the log containing :-

D/ARTICLEINFO:  Article is Content for Article1 ID is 1 
        Main Reference is Reference1 ID is 1
        Secondary  Reference is Reference2 ID is 2
D/ARTICLEINFO:  Article is Content for Article2 ID is 2 
        Main Reference is Reference3 ID is 3
        Secondary  Reference is Reference4 ID is 4
D/ARTICLEINFO:  Article is Content for Article 3 ID is 3 
        Main Reference is Reference5 ID is 5
        Secondary  Reference is reference6 ID is 6

Additional

You can also use @Embedded for all three parts.

The advantages are :-

more flexible filtering i.e. you can filter on the children (with @Relationship although you can, you need to defines the JOIN's)
Instead of multiple underlying queries for an @Relationship a single query retrieves all data

The disadvantages are:-

more complex query
requirement to use @ColumnInfo's prefix = annotation if column names are not unique to disambiguate them and thus more complex query to name the output columns accordingly.

So you could have :-

data class ArticleFullAlternative(

    @Embedded
    val article: Article,
    @Embedded(prefix = "main_")
    val main_reference: Reference,
    @Embedded(prefix = "other_")
    val other_reference: Reference
)

Along with an @Query such as :-

@Query("SELECT article.*, " +
        /* as prefix = "main_" has been used then rename output columns accordingly */
        "m.id AS main_id, m.other_data AS main_other_data, " +
        /* as prefix = "other_" has been used then rename output columns accordingly */
        "o.id AS other_id, o.other_data AS other_other_data " +
        "FROM article " +
        "JOIN reference AS m /*<<<<< to disambiguate column names */ ON main_reference_id = m.id " +
        "JOIN reference AS o /*<<<<< to disambiguate column names */ ON main_reference_id = o.id ")
abstract fun getArticleFullAlternative(): List<ArticleFullAlternative>

An example use in an Activity could be :-

    for(afa: ArticleFullAlternative in articleAndReferenceDao.getArticleFullAlternative()) {
        Log.d("ALTARTICLEINFO"," Article is ${afa.article.content} ID is ${afa.article.id} " +
                "\n\tMain Reference is ${afa.main_reference.other_data} ID is ${afa.main_reference.id}" +
                "\n\tSecondary  Reference is ${afa.other_reference.other_data} ID is ${afa.other_reference.id}")
    }

This produces exactly the same output