Room One to Many relationship Kotlin

Question

I have created Two Tables One Relationship.. I am not getting output as structured

Product Table :

    @Entity(tableName = "Product")
    data class Products (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int = 0,

    @ColumnInfo(name = "name")
    var name  : String? = null,

    @ColumnInfo(name = "variants")
    var variants : MutableList<Variants> = mutableListOf()
)

Variant Table :

    @Entity(tableName = "Variant")
    data class Variants (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int  = 0,

    @ColumnInfo(name = "product_id", index = true)
    var product_id : Int?  = null,

    @ColumnInfo(name = "measurement")
    var measurement : String?  = null,

    @ColumnInfo(name = "discounted_price")
    var discounted_price : String?  = null,

    @ColumnInfo(name = "cart_count")
    var cart_count : Int?  = null
)

Relationship Class :

data class ProductWithVariants(
    @Embedded val product: Products,
    @Relation(
        parentColumn = "id",
        entityColumn = "product_id"
    )
    val variants: Variants
)

Get ProductWithVariants Query in DAO :

@Transaction
@Query("SELECT * FROM Product WHERE subcategory_id=:subcat")
fun getProductWithVariants(subcat:Int): Flow<MutableList<ProductWithVariants>>

Am I getting unstructed output like this ,

  [
   ProductWithVariants(product=Products(id=4085,
   row_order=61,
   "name=Vedan Thoothuvalai Powder Pouch",
   tax_id=9,
   "slug=Vedan Thoothuvalai Powder Pouch",
   category_id=31,
   subcategory_id=218,
   indicator=1,
   "manufacturer=null",
   "made_in=Vedan Thoothuvalai Powder Pouch",
   return_status=1,
   cancelable_status=1,
   "till_status=received",
   "image=http":,
   "other_images="[
      
   ],
   "description=Thoothuvalai is widely used in the treatment of tuberculosis",
   "bronchial asthma",
   "difficulty in breathing",
   "sinus",
   "chest congestion and other respiratory problems. Thoothuvalai with its anti-inflammatory properties and anti-microbial activity helps asthma patients with a fresh breath of life.",
   status=1,
   popular=0,
   "date_added=2022-05-19 07":"50":21,
   return_max_days=0,
   tax=0,
   price=40,
   "tax_title=GST",
   tax_percentage=5,
   "is_favorite=false",
   "is_notify_me=false",
   "variants="[
      Variants(id=7250,
      product_id=4085,
      "type=packet",
      measurement=50,
      measurement_unit_id=2,
      price=40,
      discounted_price=35,
      "serve_for=Available",
      stock=2,
      stock_unit_id=6,
      "image=upload/default-image/default-image.png",
      moq=5,
      varient_status=1,
      "measurement_unit_name=gm",
      "stock_unit_name=pcs",
      cart_count=0,
      "is_notify_me=false)"
   ]")",
   variants=Variants(id=7250,
   product_id=4085,
   "type=packet",
   measurement=50,
   measurement_unit_id=2,
   price=40,
   discounted_price=35,
   "serve_for=Available",
   stock=2,
   stock_unit_id=6,
   "image=upload/default-image/default-image.png",
   moq=5,
   varient_status=1,
   "measurement_unit_name=gm",
   "stock_unit_name=pcs",
   cart_count=0,
   "is_notify_me=false))",
   ProductWithVariants(product=Products(id=4086,
   row_order=59,
   "name=Vedan Thippili Powder Pouch",
   tax_id=9,
   "slug=Vedan Thippili Powder Pouch",
   category_id=31,
   subcategory_id=218,
   indicator=1,
   "manufacturer=null",
   "made_in=Vedan Thippili Powder Pouch",
   return_status=1,
   cancelable_status=1,
   "till_status=received",
   "image=http":,
   "other_images="[
      
   ],
   "description=How to eat Thippili Or Pippali? Its powdered form can be taken when mixed with honey or milk. Thippili powder or Pippali powder is sufficient for adults and a pinch is just enough for children. Thippili Rasam or Pippali Rasam is another easy form to consumer thippil",
   status=1,
   popular=0,
   "date_added=2022-05-19 07":"50":21,
   return_max_days=0,
   tax=0,
   price=60,
   "tax_title=GST",
   tax_percentage=5,
   "is_favorite=false",
   "is_notify_me=false",
   "variants="[
      Variants(id=7249,
      product_id=4086,
      "type=packet",
      measurement=50,
      measurement_unit_id=2,
      price=60,
      discounted_price=55,
      "serve_for=Available",
      stock=5,
      stock_unit_id=6,
      "image=upload/default-image/default-image.png",
      moq=5,
      varient_status=1,
      "measurement_unit_name=gm",
      "stock_unit_name=pcs",
      cart_count=0,
      "is_notify_me=false)"
   ]")",
   variants=Variants(id=7249,
   product_id=4086,
   "type=packet",
   measurement=50,
   measurement_unit_id=2,
   price=60,
   discounted_price=55,
   "serve_for=Available",
   stock=5,
   stock_unit_id=6,
   "image=upload/default-image/default-image.png",
   moq=5,
   varient_status=1,
   "measurement_unit_name=gm",
   "stock_unit_name=pcs",
   cart_count=0,
   "is_notify_me=false))"
]

But I need Output like this (Expected Output) :

 {
    "error": false,
    "total": "20",
    "total_page_no": 1,
    "current_page_no": 1,
    "data": [
          {
            "id": "4241",
            "row_order": "0",
            "name": "Shivaji Ponni Rice",
            "price": "1500",
            "variants": [
                {
                    "id": "7477",
                    "product_id": "4241",
                    "measurement": "26",
                    "price": "1500",
                    "discounted_price": "1350",
                    "cart_count": "0",
                }
            ]
        },
        {
            "id": "4243",
            "row_order": "0",
            "name": "Semmoli Kichadi Ponni",
            "tax_id": "1",
            "slug": "Semmoli Kichadi Ponni",
            "category_id": "52",
            "subcategory_id": "196",
            "return_max_days": "0",
            "price": "1600",
            "variants": [
                {
                    "id": "7474",
                    "product_id": "4243",
                    "type": "packet",
                    "measurement": "26",
                    "measurement_unit_id": "1",
                    "price": "1600",
                    "discounted_price": "1420",
                    "cart_count": "0",
                }
             ]
          }
        ]
     }

What i need to change in table and in relationship class to get expected structure as output

Matt Grier · Answer 1 · 2022-09-09T12:36:15.877

I think you can restructure these tables, and relationship like this: (Product)

    @Entity(tableName = "Product")
    data class Product ( 

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "productId")
    var productId : Int = 0,

    @ColumnInfo(name = "name")
    var name  : String? = null
)

(Variant)

    @Entity(tableName = "Variant")
    data class Variants (   
    
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "variantId")
    var variantId : Int  = 0,
    
    @ColumnInfo(name = "parentProductId")
    var parentProductId : Int = 0,
    
    @ColumnInfo(name = "measurement")
    var measurement : String?  = null,
    
    @ColumnInfo(name = "discounted_price")
    var discounted_price : String?  = null,
    
    @ColumnInfo(name = "cart_count")
    var cart_count : Int?  = null
)

(Relationship)

data class ProductWithVariants(
    
       @Embedded val product: Product,

       @Relation(
           parentColumn = "productId",
           entityColumn = "parentProductId",
       )
       val variants: List<Variant>
)

The query looks pretty much the same (in the same dao you have now):

@Transaction
@Query("SELECT * FROM Product)
fun getProductsWithVariants() : List<ProductWithVariants>

Or as a flow:

@Transaction
@Query("SELECT * FROM Product)
fun getProductsWithVariants() : Flow<List<ProductWithVariants>>

@SasidharanIOS I have edited the answer to include query examples, you can query for relationships from the parent table just like a regular query for those parent entities, just change the return type and add the "@Transaction" annotation. — Matt Grier, Sep 09 '22 at 12:37

Room One to Many relationship Kotlin

1 Answers1