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.
CodePudding user response：
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