How to Insert data to Many to many relationship tables and retrieving last id In cases where the method accepts two parameters?

Room does return the inserted id when the input has only one parameter

  @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertWithDrinks(item: DrinkFavourite): Long

How can i retrieve the ID when method looks like this

 @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertWithDrinks(item: DrinkFavourite, List<Drink>)

Or rather, How do you handle many-to-many relationship Insert? I have a relationship and a cross ref table like this


data class DrinkFavouritesWithDrinks(
    @Embedded
    val item: DrinkFavourite,
    @Relation(
        parentColumn = "item_id",
        entityColumn = "drink_id",
        associateBy = Junction(DrinkFavouriteAndDrinksCrossRef::class)
    )
    val drinks: List<Drink> = emptyList()
)

my cross ref

@Entity(
    primaryKeys = ["item_id", "drink_id"]
)
data class DrinkFavouriteAndDrinksCrossRef(
    @ColumnInfo(name = "item_id")
    val itemId: Long,
    @ColumnInfo(name = "drink_id")
    val drinkId: Int
)

Solution

How can i retrieve the ID when method looks like this

When you insert multiple rows you get the resultant id's in an Array<Long>

However, for a basic cross reference/mapping table you would rarely, it ever, use the rowid column (which is the value returned).

rowid is returned even though the column is implicitly defined. That is with the exception of rarely used WITHOUT ROWID tables, all tables have a hidden column named rowid.
- When you use INTEGER PRIMARY KEY to define a column, the column is an alias of the rowid column. In room @PrimaryKey for a Long or Int, with or without autoGenerate = true/false (without = false) equates to INTEGER PRIMARY KEY or if autoGenerate = true to INTEGER PRIMARY KEY AUTOINCREMENT.

How do you handle many-to-many relationship Insert?

Here is an example, based upon the available code, with other code created.

So in addition to your DrinkFavouriteAndDrinksCrossRef Entity.

Entities for DrinkFavourite and Item are

@Entity
 data class DrinkFavourite(
    @PrimaryKey
    val drink_id: Int? = null,
    val drink_name: String = ""
)
@Entity

data class Drink( @PrimaryKey var itemId :Long?, var itemName: String )

The Dao AllDao is :-

@Dao
interface AllDao {

    @Insert
    fun insertDrinkFavourite(drinkFavourite: DrinkFavourite) :Long

    @Insert
    fun insertManyDrinkFavourite(drinkFavourites: List<DrinkFavourite>) :Array<Long>

    @Insert
    fun insertItem(drink: Drink) :Long

    @Insert
    fun insertManyItems(drinks: List<Drink>) :Array<Long>

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertDrinkFavouriteAndDrinksCrossRef(drinkFavouriteAndDrinksCrossRef: DrinkFavouriteAndDrinksCrossRef) :Long

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertManyDrinkFavouriteAndDrinksCrossRef(drinkFavouriteAndDrinksCrossRefs: List<DrinkFavouriteAndDrinksCrossRef>) :Array<Long>
}

This caters for inserting a single Entity/row and many Entity/rows for each of the 3 Entities.

The following utilises the 3 many insertions defined in the Dao to insert 3 Drinks, 4 DrinkFavourites and to then insert the 12 DrinkFavouriteAndDrinksCrossRefs (3 Drink * 4 DrinkFavorite) permutations, i.e. all possible permutations, that can be crossed referenced based upon the id's inserted.

class MainActivity : AppCompatActivity() {

    lateinit var database: Database

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        database = Room.databaseBuilder(this, Database::class.java, "drinksdb")
            .allowMainThreadQueries()
            .build()
        //val firsItemtid = database.allDao().insertItem( Item(0,"X"))
        val manyItemIds = database.allDao().insertManyItems(
            listOf(
                Drink(null,"A"),
                Drink(null,"B"),
                Drink(null,"C")
            )
        )
        val manyDrinkFavouriteIds = database.allDao().insertManyDrinkFavourite(
            listOf(
                DrinkFavourite(null,"DrinkA"),
                DrinkFavourite(null,"DrinkB"),
                DrinkFavourite(null,"DrinkC"),
                DrinkFavourite(null,"DrinkD")
            )
        )

        var xrefcombos = ArrayList<DrinkFavouriteAndDrinksCrossRef>()
        for(itemId: Long in manyItemIds) {
            for (dfId: Long in manyDrinkFavouriteIds) {
                xrefcombos.add( DrinkFavouriteAndDrinksCrossRef(itemId,dfId.toInt()))
            }
        }
        val drinkFavouriteAndDrinksCrossRefIdList = database.allDao().insertManyDrinkFavouriteAndDrinksCrossRef(xrefcombos)
        for (dfadcrId in drinkFavouriteAndDrinksCrossRefIdList) {
            Log.d("DRKFAVDRNKXREF","Id = " + dfadcrId)
        }
    }
}

The output is :-

2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 1
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 2
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 3
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 4
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 5
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 6
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 7
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 8
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 9
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 10
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 11
2020-01-17 07:46:40.621 D/DRKFAVDRNKXREF: Id = 12

i.e the 12 permutations have been added.

I don't believe that you can effectively use @Insert to do other than insertions according to the Entity/Object being inserted. To do more complex insertions based upon other values, you would use @Query with suitable SQL.

Additional

With regard to :-

data class DrinkFavouritesWithDrinks(
    @Embedded
    val item: DrinkFavourite,
    @Relation(
        parentColumn = "item_id",
        entityColumn = "drink_id",
        associateBy = Junction(DrinkFavouriteAndDrinksCrossRef::class)
    )

I believe that this should be :-

data class DrinkFavouriteWithDrinks (

    @Embedded
    val drinkFavourite: DrinkFavourite,
    @Relation(
        entity =  Drink::class,
        entityColumn = "itemId",
        parentColumn = "drink_id",
        associateBy = Junction(
            DrinkFavouriteAndDrinksCrossRef::class,entityColumn = "item_id",parentColumn = "drink_id"))
    val drinks: List<Drink> = emptyList()
    )

You get all the Drinks per DrinkFavourite, so singular rather than plural is a better name.
the association needs to know the columns to associate by.

You could than have a Dao method such as :-

@Query("SELECT * FROM drinkfavourite")
fun getAllDrinkFavouritesWithDrinks() :List<DrinkFavouriteWithDrinks>

Adding to the following to the MainActivity above :-

    val drinkFavouriteWithDrinksList = database.allDao().getAllDrinkFavouritesWithDrinks()
    var sb = StringBuilder()
    for (dfwd: DrinkFavouriteWithDrinks in drinkFavouriteWithDrinksList) {
        sb.clear().append("DrinkFavourite = ").append(dfwd.drinkFavourite)
        for (d: Drink in dfwd.drinks) {
            sb.append("\n\tDrink = ").append(d.itemName)
        }
        Log.d("DRINKFAVINFO", sb.toString())
    }

Results in :-

2020-01-17 11:39:52.762 D/DRINKFAVINFO: DrinkFavourite = DrinkFavourite(drink_id=1, drinkFavouriteName=DrinkA)
      Drink = A
      Drink = B
      Drink = C
2020-01-17 11:39:52.763 D/DRINKFAVINFO: DrinkFavourite = DrinkFavourite(drink_id=2, drinkFavouriteName=DrinkB)
      Drink = A
      Drink = B
      Drink = C
2020-01-17 11:39:52.763 D/DRINKFAVINFO: DrinkFavourite = DrinkFavourite(drink_id=3, drinkFavouriteName=DrinkC)
      Drink = A
      Drink = B
      Drink = C
2020-01-17 11:39:52.763 D/DRINKFAVINFO: DrinkFavourite = DrinkFavourite(drink_id=4, drinkFavouriteName=DrinkD)
      Drink = A
      Drink = B
      Drink = C

Not the best example as every DrinkFavourite has all three drinks