I'm trying hard but I get no result, I want to get all the information about the product besides the name of the category, and the name of the unit because I want to show all of this information inside my adapter what is the best practice to do it, please check my codes.
How to write a query to access data from multiple tables. And which type of class do I need to define to cover all of this data together.
@Entity(tableName = "product")
data class Product(
@PrimaryKey(autoGenerate = true)
val product_id: Int,
val name: String,
val sellPrice: Double,
val barcode: String?,
val buyPrice: Double?,
val quantity: Double?,
val packing: Double?,
val reducePrice: Double?,
val description: String?,
val fk_category: Int?,
val fk_unit: Int?
)
@Entity(tableName = "category")
data class Category(
@PrimaryKey(autoGenerate = true)
val category_id: Int,
val name: String
)
@Entity(tableName = "unit")
data class Unit(
@PrimaryKey(autoGenerate = true)
val unit_id: Int,
val name: String
)
data class ProductAndCategory(
@Embedded
val category: Category,
@Relation(
parentColumn = "category_id",
entityColumn = "fk_category"
)
val product: Product
)
data class ProductAndUnit(
@Embedded
val unit: Unit,
@Relation(
parentColumn = "unit_id",
entityColumn = "fk_unit"
)
val product: Product
)
@Dao
interface ProductDao {
@Query("SELECT * FROM product")
fun getAllProducts(): LiveData<List<Product>>
@Transaction
@Query("SELECT * FROM product INNER JOIN category on product.fk_category = category.category_id")
fun getAllProductAndCategory(): LiveData<List<ProductAndCategory>>
@Query("SELECT * FROM product WHERE product_id = :productID")
suspend fun getProduct(productID: Int): List<Product>
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertProduct(product: Product)
@Update
suspend fun updateProduct(product: Product)
@Delete
suspend fun deleteProduct(product: Product)
@Query("DELETE FROM product")
suspend fun deleteAllProducts()
@Transaction
@Query("SELECT * FROM product INNER JOIN category on product.fk_category = category.category_id WHERE product_id= :productID")
suspend fun getProductAndCategory(productID: Int): List<ProductAndCategory>
@Transaction
@Query("SELECT * FROM product INNER JOIN unit on product.fk_unit = unit.unit_id WHERE product_id= :productID")
suspend fun getProductAndUnit(productID: Int): List<ProductAndUnit>
}
You can do this two ways.
You can Embed product and have an @Relation for Category and for Unit (2 @Realtion) or you can Embed all three.
So you could have :-
data class ProductAndCategoryAndUnit(
@Embedded
val product: Product,
@Relation(entity = Category::class, parentColumn = "fk_category",entityColumn = "category_id")
val category: Category,
@Relation(entity = Unit::class, parentColumn = "fk_unit",entityColumn = "unit_id")
val unit: Unit
)
with something like :-
@Query("SELECT * FROM product")
@Transaction
fun getProductAndCategoryAndUnit(): List<ProductAndCategoryAndUnit>
or alternately (BUT READ THE NOTES) :-
data class ProductCategoryUnit (
@Embedded
val product: Product,
@Embedded
val category: Category,
@Embedded
val unit: Unit
)
with something like :-
@Query("SELECT * FROM product JOIN category ON product.fk_category = category.category_id JOIN unit ON product.fk_unit = unit.unit_id")
fun getProductCategoryUnit(): List<ProductCategoryUnit>
Notes re the 2nd
This alternative would have an issue with the 3 columns all named name (they would be ambiguous). You could disambiguate using the @Embedded(prefix = "?") however this then requires the columns to be aliased in the SQL. A simpler way would be to ensure that the column names will not be ambiguous. This can easily be achieved using the @ColumnInfo's name argument as an example for Unit (similar would be required for Category) :-
@Entity(tableName = "unit")
data class Unit(
@PrimaryKey(autoGenerate = true)
val unit_id: Long,
@ColumnInfo(name = "unit_name") // ADDED to disambiguate for joins
val name: String
)
Working Example of Both
Based upon your code the following demonstrates both, noting that for convenience and brevity the demo is run on the main thread so LiveData and suspended functions have been altered accordingly. Long's have also been used instead of Int's for id's.
The Product data class used :-
@Entity(tableName = "product")
data class Product(
@PrimaryKey(autoGenerate = true)
val product_id: Long,
val name: String,
val sellPrice: Double,
val barcode: String?,
val buyPrice: Double?,
val quantity: Double?,
val packing: Double?,
val reducePrice: Double?,
val description: String?,
val fk_category: Long?,
val fk_unit: Long?
)
The Category data class used :-
@Entity(tableName = "category")
data class Category(
@PrimaryKey(autoGenerate = true)
val category_id: Long,
@ColumnInfo(name = "category_name") // ADDED to disambiguate for joins
val name: String
)
The data class Unit is as above as are the POJO's.
The ProductDao used for the demo :-
@Dao
interface ProductDao {
@Query("SELECT * FROM product")
fun getAllProducts(): /*LiveData<*/List<Product>/*>*/
/*
@Transaction
@Query("SELECT * FROM product INNER JOIN category on product.fk_category = category.category_id")
fun getAllProductAndCategory(): LiveData<List<ProductAndCategory>>
*/
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(unit: Unit): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(category: Category): Long
@Query("SELECT * FROM product WHERE product_id = :productID")
suspend fun getProduct(productID: Int): List<Product>
@Insert(onConflict = OnConflictStrategy.REPLACE)
/*suspend*/ fun insertProduct(product: Product)
@Update
suspend fun updateProduct(product: Product)
@Delete
suspend fun deleteProduct(product: Product)
@Query("DELETE FROM product")
suspend fun deleteAllProducts()
/*
@Transaction
@Query("SELECT * FROM product INNER JOIN category on product.fk_category = category.category_id WHERE product_id= :productID")
suspend fun getProductAndCategory(productID: Int): List<ProductAndCategory>
*/
/*
@Transaction
@Query("SELECT * FROM product INNER JOIN unit on product.fk_unit = unit.unit_id WHERE product_id= :productID")
suspend fun getProductAndUnit(productID: Int): List<ProductAndUnit>
*/
@Query("SELECT * FROM product")
@Transaction
fun getProductAndCategoryAndUnit(): List<ProductAndCategoryAndUnit>
@Query("SELECT * FROM product WHERE product_id=:productID")
fun getProductAndCategoryAndUnitById(productID: Long): List<ProductAndCategoryAndUnit>
@Query("SELECT * FROM product JOIN category ON product.fk_category = category.category_id JOIN unit ON product.fk_unit = unit.unit_id")
fun getProductCategoryUnit(): List<ProductCategoryUnit>
}
The TheDatabase class is a pretty stock @Database so is not included.
Lastly is an Activity as per :-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: ProductDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getProductDao()
var u1 = dao.insert(Unit(0L, "Unit1"))
var u2 = dao.insert(Unit(0L, "Unit2"))
var c1 = dao.insert(Category(0L,"Cat1"))
var c2 = dao.insert(Category(0L,"Cat2"))
dao.insertProduct(Product(0L,"Produuct1",1.1,"Barcode001",2.2,10.5,0.0,0.0,"Desc4Product1",c2,u1))
dao.insertProduct(Product(0L,"Product2",1.1,"Barcode002",2.2,10.5,0.0,0.0,"Desc4product2",c1,u2))
// Inserts Catergory,Unit and Product (utilising the id returned by the inserts for the category and Unit)
dao.insertProduct(
Product(
0L,"" +
"Product3",
1.1,"" +
"Barcode003",
2.2,
10.5,
0.0,
0.0,
"Desc4Product3",
dao.insert(Category(0L,"Cat4")),
dao.insert(Unit(0L,"Unit4"))
)
)
// Extract and Log via the @Embedded and 2 @Realtion's
for(pcu: ProductAndCategoryAndUnit in dao.getProductAndCategoryAndUnit()) {
Log.d("PRODUCTINFO","Product is ${pcu.product.name} .... Category is ${pcu.category.name} Unit is ${pcu.unit.name}")
}
// Extract and Log via the 3 @Embedded's
for(pcu: ProductCategoryUnit in dao.getProductCategoryUnit()) {
Log.d("PRODUCTINFO","Product is ${pcu.product.name} .... Category is ${pcu.category.name} Unit is ${pcu.unit.name}")
}
}
}
The result in the Log :-
D/PRODUCTINFO: Product is Produuct1 .... Category is Cat2 Unit is Unit1
D/PRODUCTINFO: Product is Product2 .... Category is Cat1 Unit is Unit2
D/PRODUCTINFO: Product is Product3 .... Category is Cat4 Unit is Unit4
D/PRODUCTINFO: Product is Produuct1 .... Category is Cat2 Unit is Unit1
D/PRODUCTINFO: Product is Product2 .... Category is Cat1 Unit is Unit2
D/PRODUCTINFO: Product is Product3 .... Category is Cat4 Unit is Unit4
i.e. the results of both methods are identical.