Android Room - Query to only insert new entry if combination of two fileds do not exist

Question

I have an Room table, with ID, Year and Week.

I need a query, that a new entry is only added if the combination of Year and Week does not exist in the table.

How can I archive this? I created an initInsertWeek function. But I do not know how to archive this.

@Dao
interface WeeksDao {

    @Upsert
    suspend fun upsertWeek(week: Week)

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun initInsertWeek(week: Week)
}

Answer 1

You could create a query method that checks if one exists and then create a transaction method to combine the check and the insert.

Something like this

@Query(SELECT * FROM my_table WHERE year = :year AND week = :week")
fun doesEntryExist(year: String, week: String): List<Week>

@Transaction
fun insertIfNotExist(week: Week){
    val entries = doesEntryExist(week.year, week.week)
    if(entries.isEmpty()){
        initInsertWeek(week)
    }
}