I have a class, Category that's a POJO, but it throws an error,
error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: itemCategoryId)
The Category class is
data class Category(
@Embedded
val category: CategoryTable,
@Relation(
parentColumn = "category_id",
entityColumn = "itemCategoryId"
)
val inventoryItems: List<InventoryItem>
) : InventoryAccess {
override val id: Long
get() = category.categoryId
}
The InventoryItem is database view defined as:
@DatabaseView(
"""
SELECT
i.item_id AS itemId,
i.name as name,
i.price as price,
i.item_code as itemCode,
i.quantity as quantity,
i.description as description,
shelf.units_remaining + sum(w.units_in_si_unit) as unitsRemaining,
image.uri as uri
from item_table i
left join item_image_table image on image.image_item_id = i.item_id and image.is_feature_image = 1
inner join shelf_table shelf on shelf.shelf_item_id = i.item_id
left join ware_house_table w on w.ware_house_item_id = i.item_id
group by item_id
order by i.name asc
"""
)
@Keep
data class InventoryItem(
override val itemId: Long,
override val name: String,
override val price: Double,
override val quantity: ImmutableQuantity,
override val uri: Uri?,
override val unitsRemaining: Double?,
val itemCategoryId: Long,
val itemCode: String?,
val description: String?,
) : ItemStructure, InventoryAccess {
override val id: Long get() = itemId
}
What is the issue with the inventoryItems field definition.
I was trying to create a one to many relationship between the category and the inventory item which is a database view. However when I use the POJO with @Relation annotation, I get the error as described earlier.
When you use @Relation Room generates the underlying extraction (query) of the @Relation object for each @Embedded object extracted. As such it will, I believe, use the columns as per the output/select used for the database view.
In your case there is no column output that is named itemCategoryId the only output columns are:-
Now if you had, for example:-
SELECT
i.item_id AS itemId,
i.name as name,
i.price as price,
i.item_code as itemCode,
i.quantity as quantity,
i.description as description,
shelf.units_remaining + sum(w.units_in_si_unit) as unitsRemaining,
image.uri as uri,
1 AS itemcategoryid /*<<<<<<<<<< ADDED */
Then I believe that the column will then exist. Obviously the value of 1 would very likely not be what is wanted.
Can the data type returned by a field annotated with @Relation be a DatabaseView
Yes. To all intents and purposes, the View is like a Table. In fact if you look at the class named the same as the @Database annotated class and find the createAlTables method in generated java then you will see the CREATE VIEW sql. e.g. for the demo below:-
db.execSQL("CREATE VIEW `TestView` AS SELECT id AS idother, thecolumn AS theColumnother, id AS another, 12 as yetanother FROM thetable");
Perhaps consider this demo that uses the following Entities/POJOs/Dao and Database:-
@Entity
data class TheTable(
@PrimaryKey
val id: Long?=null,
val theColumn: String
)
@DatabaseView("SELECT id AS idother, thecolumn AS theColumnother, id AS another, 12 as yetanother FROM thetable")
data class TestView(
val idother: Long,
val theColumnother: String,
val another: Long,
val yetanother: Long
)
data class POJOOneToOneTest(
@Embedded
val theTable: TheTable,
@Relation(
entity = TestView::class,
parentColumn = "id",
entityColumn = "another"
)
val testViews: List<TestView>
)
@Dao
interface AllDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(theTable: TheTable): Long
@Transaction
@Query("SELECT * FROM thetable")
fun getPOJOOneToOneTests(): List<POJOOneToOneTest>
}
@Database(entities = [TheTable::class], views = [TestView::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAOs(): AllDAOs
companion object {
private var instance: TheDatabase?=null
fun getInstance(context: Context): TheDatabase {
if (instance==null) {
instance= Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
.allowMainThreadQueries() /* for brevity of the demo */
.build()
}
return instance as TheDatabase
}
}
}
Noting that to ensure a relationship that the referenced child value (another) is set to the parent value.
And then with the following activity code:-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db= TheDatabase.getInstance(this)
dao=db.getAllDAOs()
dao.insert(TheTable(theColumn = "Apple"))
dao.insert(TheTable(theColumn = "Banana"))
dao.insert(TheTable(theColumn = "Cherry"))
dao.insert(TheTable(theColumn = "Damson"))
for (potot in dao.getPOJOOneToOneTests()) {
val sb = StringBuilder()
for (tv in potot.testViews) {
sb.append("\n\t TVIDOther is ${tv.idother} TVAnother is ${tv.another} TVColumnOther is ${tv.theColumnother} TV Yetanother is ${tv.yetanother}")
}
Log.d("DBINFO","TheTable ID is ${potot.theTable.id} TheColumn is ${potot.theTable.theColumn} it has ${potot.testViews.size} TestViews. They are:-${sb}")
}
}
}
The result is:-
D/DBINFO: TheTable ID is 1 TheColumn is Apple it has 1 TestViews. They are:-
TVIDOther is 1 TVAnother is 1 TVColumnOther is Apple TV Yetanother is 12
D/DBINFO: TheTable ID is 2 TheColumn is Banana it has 1 TestViews. They are:-
TVIDOther is 2 TVAnother is 2 TVColumnOther is Banana TV Yetanother is 12
D/DBINFO: TheTable ID is 3 TheColumn is Cherry it has 1 TestViews. They are:-
TVIDOther is 3 TVAnother is 3 TVColumnOther is Cherry TV Yetanother is 12
D/DBINFO: TheTable ID is 4 TheColumn is Damson it has 1 TestViews. They are:-
TVIDOther is 4 TVAnother is 4 TVColumnOther is Damson TV Yetanother is 12
So the single related TestView is retrieved from the View (@DatabaseView in Room).
Obviously the example has been created just to demonstrate the possibility of using a View within an @Relation, it is not intended to reflect your situation.