I am working on a Project using Android Room and I'm kind of stuck with using a Many-To-Many-Relation. I have found this question kind of similiar to mine but I still can't get it to work.
So I basically follows the Documentation and did a Reciepe and Ingredient Relationship:
@Entity(tableName ="zutaten_table")
data class ZutatenData(
@PrimaryKey (autoGenerate = true) val zutatid : Int,
@ColumnInfo(name = "zutname") var zutname : String,
//and some more columns
)
@Entity(tableName = "rezepte_table")
data class RezepteData(
@PrimaryKey (autoGenerate = true) val rezeptid : Int,
@ColumnInfo(name = "rezname") var rezname : String,
@ColumnInfo(name = "bild") var bild:Int
)
@Entity(primaryKeys = ["zutatid", "rezeptid"])
data class RefZutatRezept(
val zutatid: Int,
val rezeptid: Int
)
data class ZutatenWithRezept(
@Embedded val zutat: ZutatenData,
@Relation(
parentColumn = "zutatid",
entity = RezepteData::class,
entityColumn = "rezeptid",
associateBy = Junction(value = RefZutatRezept::class,
parentColumn = "zutatid",
entityColumn = "rezeptid"
)
)
val rezepte: List<RezepteData>
)
data class RezeptWithZutat(
@Embedded val rezept: RezepteData,
@Relation(
parentColumn = "rezeptid",
entityColumn = "zutatid",
associateBy = Junction(RefZutatRezept::class)
)
val zutaten: List<ZutatenData>
)
I have a Dao for every Data type (not shure it is the right way to go...)
@Dao
interface ZutatDao{
@Query("SELECT * FROM zutaten_table ORDER BY zutname ASC")
fun getAlphabetizedZutaten(): LiveData<List<ZutatenData>>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(zutname: ZutatenData)
@Update
suspend fun update(zutname: ZutatenData)
@Query("DELETE FROM zutaten_table")
suspend fun deleteAll()
}
@Dao
interface RezeptDao{
@Query("SELECT * FROM rezepte_table ORDER BY rezname ASC")
fun getRezepte() : LiveData<List<RezepteData>>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(rezepte : RezepteData) :Long
@Query("DELETE FROM rezepte_table")
suspend fun deleteAll()
}
@Dao
interface ZutatwithRezeptDao{
@Transaction
@Query("SELECT * FROM zutaten_table ORDER BY zutname ASC")
suspend fun getZutatenWithRezept(): List<ZutatenWithRezept>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(join:RefZutatRezept)
}
@Dao
interface RezeptwithZutatDao {
@Transaction
@Query("SELECT * FROM rezepte_table ORDER BY rezname ASC")
suspend fun getRezeptwithZutat(): List<RezeptWithZutat>
@Insert(onConflict = OnConflictStrategy.IGNORE)
suspend fun insert(join: RefZutatRezept)
}
I want to insert a RezeptWithZutat now (I'm doing in the populateDatabase function at the moment.)
var ListZutatRez1 = listOf<ZutatenData>( z_kart, z_quark, z_lein)
var rez = RezepteData(0,"Kartoffeln mit Quark", R.drawable.kartoffelnquark)
var rezges =RezeptWithZutat(rez, ListZutatRez1)
rezzut.insert(rezges)
At the last line I get the error: "Type mismatch: inferred type is RezeptWithZutat but RefZutatRezept was expected" I tried some things but they all got me different errors.
So my question is: Which is the right way to get a RezeptWithZutat automatically inserted into the table (make an entry for RezeptData and entries for all ZutatData that do exist and linking everything in the RefZutatRezept table.)
CodePudding user response：
This isn't going to a simple one line fix.
First you need to be able to detect if a Zutaten exists and probably if a Rezepte exists (so a RezeptWithZutat for an existing Rezept does create a new one but adds to the existing one).
So first two new Dao's
- Note instead of an interface, the following code uses an abstract class so abstract fun is used instead of fun.
- Also all dao's have been combined into one abstract class namely AllDao.
- Note I don't do LiveData stuff so the code does not use LiveData and the code I've copied has had all LiveData removed to allow a working demonstration.
:-
@Query("SELECT zutatid FROM zutaten_table WHERE zutname=:zutname")
abstract fun getZutatenIdByName(zutname: String): Int
@Query("SELECT rezeptid FROM rezepte_table WHERE rezname=:rezname")
abstract fun getRezepteIDByName(rezname: String): Int
These will return the id or 0 if the zutaten/rezept doesn't exist.
Next comes a function, in AllDao (it uses the Dao's directly), to insert the RezeptWithZutat:-
fun insertZutatenWithRezept(rezeptWithZutat: RezeptWithZutat) {
var rid = getRezepteIDByName(rezeptWithZutat.rezept.rezname)
/* find or insert Rezepte according to name */
if (rid < 1) {
rid = insert(RezepteData(0,rezeptWithZutat.rezept.rezname,rezeptWithZutat.rezept.bild)).toInt()
}
if (rid < 1) {
/*
could not find existing Rezepte or insert a new one?????
should not happen but in case do something here
*/
} else {
for (z in rezeptWithZutat.zutaten) {
var zid = getZutatenIdByName(z.zutname)
if (zid < 1) {
zid = insert(ZutatenData(0, z.zutname)).toInt()
}
if (zid < 1) {
/*
could not find existing Zutaten or insert new one?????
should not happen but in case do something here
*/
} else {
insert(RefZutatRezept(zutatid = zid, rezeptid = rid))
}
}
}
}
- not the most efficient way as the non-existent Zutatens could be inserted together (but with more complicated code) and likewise the RefZutatRezept could be insert all together (again with more complicated code).
Demonstration (run on main thread for convenience/brevity)
using the above and your code/dao's (as changed above) then in an Activity :-
db = TheDatabase.getInstance(this)
dao = db.getAllDao()
val r1 = dao.insert(RezepteData(rezeptid = 0,rezname = "REZ1",bild = 1))
val r2 = dao.insert(RezepteData(rezeptid = 0, rezname = "REZ2", bild = 1))
val milk = dao.insert(ZutatenData(zutatid = 0,zutname = "Milk"))
val sugar = dao.insert(ZutatenData(zutatid = 0,zutname = "Sugar"))
val flour = dao.insert(ZutatenData(zutatid = 0,zutname = "Flour"))
val eggs = dao.insert(ZutatenData(zutatid = 0,zutname = "Eggs"))
dao.insert(RefZutatRezept(milk.toInt(),r1.toInt()))
dao.insert(RefZutatRezept(flour.toInt(),r1.toInt()))
dao.insert(RefZutatRezept(eggs.toInt(),r1.toInt()))
Log.d("STAGE1", "data before adding R-With-Z")
showAllRezeptWithZutatsInDatabase()
var newRWithZ = RezeptWithZutat(
RezepteData(0,"REZ3",1),zutaten = listOf(
ZutatenData(0,"salt"),
ZutatenData(0,"nutmeg"),
ZutatenData(0,"cornflour"),
ZutatenData(0,"corriander"),
ZutatenData(0,"Milk"), // exists
ZutatenData(0,"Sugar"), // exists
ZutatenData(0,"More Sugar")
)
)
dao.insertZutatenWithRezept(newRWithZ)
Log.d("STAGE2", "data after adding R-With-Z")
showAllRezeptWithZutatsInDatabase()
newRWithZ = RezeptWithZutat(
RezepteData(0,"REZ3",1),
zutaten = listOf(
ZutatenData(0,"leek"),
ZutatenData(0,"apple"),
ZutatenData(0,"banana"),
ZutatenData(0,"pineapple"),
ZutatenData(0,"Milk"), // exists
ZutatenData(0,"Sugar"),
ZutatenData(0,"More Sugar")
) //Rezepte also exists
)
dao.insertZutatenWithRezept(newRWithZ)
Log.d("STAGE3", "data after adding R-With-Z")
showAllRezeptWithZutatsInDatabase()
}
fun showAllRezeptWithZutatsInDatabase() {
for(rzr: RezeptWithZutat in dao.getRezeptwithZutat()) {
Log.d("DBINFO","Rezept is ${rzr.rezept.rezname} Bild is ${rzr.rezept.bild} ID is ${rzr.rezept.rezeptid}")
for(z: ZutatenData in rzr.zutaten) {
Log.d("DBINFO","\tZutaten is ${z.zutname} ID is ${z.zutatid}")
}
}
}
When run (once) then the log includes :-
2021-10-25 20:55:47.370 D/STAGE1: data before adding R-With-Z
2021-10-25 20:55:47.377 D/DBINFO: Rezept is REZ1 Bild is 1 ID is 1
2021-10-25 20:55:47.377 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.377 D/DBINFO: Zutaten is Flour ID is 3
2021-10-25 20:55:47.377 D/DBINFO: Zutaten is Eggs ID is 4
2021-10-25 20:55:47.377 D/DBINFO: Rezept is REZ2 Bild is 1 ID is 2
2021-10-25 20:55:47.425 D/STAGE2: data after adding R-With-Z
2021-10-25 20:55:47.428 D/DBINFO: Rezept is REZ1 Bild is 1 ID is 1
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is Flour ID is 3
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is Eggs ID is 4
2021-10-25 20:55:47.428 D/DBINFO: Rezept is REZ2 Bild is 1 ID is 2
2021-10-25 20:55:47.428 D/DBINFO: Rezept is REZ3 Bild is 1 ID is 3
2021-10-25 20:55:47.428 D/DBINFO: Zutaten is salt ID is 5
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is nutmeg ID is 6
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is cornflour ID is 7
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is corriander ID is 8
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is Sugar ID is 2
2021-10-25 20:55:47.429 D/DBINFO: Zutaten is More Sugar ID is 9
2021-10-25 20:55:47.459 D/STAGE3: data after adding R-With-Z
2021-10-25 20:55:47.461 D/DBINFO: Rezept is REZ1 Bild is 1 ID is 1
2021-10-25 20:55:47.461 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.461 D/DBINFO: Zutaten is Flour ID is 3
2021-10-25 20:55:47.461 D/DBINFO: Zutaten is Eggs ID is 4
2021-10-25 20:55:47.461 D/DBINFO: Rezept is REZ2 Bild is 1 ID is 2
2021-10-25 20:55:47.461 D/DBINFO: Rezept is REZ3 Bild is 1 ID is 3
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is salt ID is 5
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is nutmeg ID is 6
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is cornflour ID is 7
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is corriander ID is 8
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is Milk ID is 1
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is Sugar ID is 2
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is More Sugar ID is 9
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is leek ID is 10
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is apple ID is 11
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is banana ID is 12
2021-10-25 20:55:47.462 D/DBINFO: Zutaten is pineapple ID is 13
Suggestions
- index on the rezeptid column of the RefZutatRezept table.
- a unique index on rezname and zutname (currently you could have for example Milk with an id of 1 and Milk with an id of 2 and so on).
- foreign key constraints on the RefZutatRezept table to enforce referential integrity and to cascade deletions and updates
Cascade will apply relevant changes to a parent to the children.
e.g. if you deleted a Zutaten, then all the rows in the RefZutatRezept that reference the Zutaten would automatically be deleted
- without you may encounter issues as there would be references to a non existent Zutaten. Likewise for any Rezepte's
the onUpdate action isn't so important as it's highly unlikely you would change zutatid or rezptid.
So you may wish to consider the following :-
@Entity(
tableName ="zutaten_table",
indices = [ Index(value = ["zutname"],unique = true)]
)
data class ZutatenData(
@PrimaryKey(autoGenerate = true) val zutatid : Int,
@ColumnInfo(name = "zutname") var zutname : String
//and some more columns
)
and
@Entity(tableName = "rezepte_table",
indices = [Index(value = ["rezname"],unique = true)]
)
data class RezepteData(
@PrimaryKey(autoGenerate = true) val rezeptid : Int,
@ColumnInfo(name = "rezname") var rezname : String,
@ColumnInfo(name = "bild") var bild:Int
)
and
@Entity(
primaryKeys = ["zutatid", "rezeptid"],
indices = [Index(value = ["rezeptid"])],
foreignKeys = [
ForeignKey(
entity = ZutatenData::class,
parentColumns = ["zutatid"],
childColumns = ["zutatid"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
),
ForeignKey(
entity = RezepteData::class,
parentColumns = ["rezeptid"],
childColumns = ["rezeptid"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class RefZutatRezept(
val zutatid: Int,
val rezeptid: Int
)