Unable to get correct values in Room by using @Relation-CodePudding

I have three tables in my Room database, my query should return a list of products that contains details about them.

All products are inserted based on a documentId so when the query is done I need to get in the list only the items for that documentId.

The DAO looks like this:

@Transaction
@Query("SELECT * FROM document_products dp LEFT JOIN products ON products.id = dp.productId LEFT JOIN products_barcodes ON products_barcodes.barcode = dp.productId WHERE dp.documentId = :documentId AND (products_barcodes.barcode = dp.productId OR products.id = dp.productId) ORDER BY timestamp ASC")
fun getProductsWithDetails(documentId: Long): Flow<List<DocumentProductWithDetails>>

And if I test the query in a table like this:

Where documentId is 5 the query returns the correct values:

But those values are incorrect in the application probably cause of @Relation in DocumentProductWithDetails but I'm unable to find the issue, in facts inside the application the data is shown as this:

So as the item with productId is saved three times it is showing the last item instead of the one related to documentId

The data class which contains @Relation annotation looks like this:

@JsonClass(generateAdapter = true)
data class DocumentProductWithDetails(
    @Relation(
        parentColumn = "id",
        entityColumn = "productId"
    )
    var product: DocumentProduct,
    @Embedded
    var details: ProductWithBarcodes?
)

Where DocumentProduct and ProductWithBarcodes:

data class DocumentProduct(
    @PrimaryKey(autoGenerate = true)
    var id: Long,
    var productId: String,
    var quantity: Float,
    var orderQuantity: Float,
    var purchase: Float,
    var documentId: Long,
    var labelType: String?,
    var timestamp: Long?
)

data class ProductWithBarcodes(
    @Embedded
    var product: Product,
    @Relation(
        parentColumn = "id",
        entityColumn = "productId"
    )
    var barcodes: List<Barcode>
)

CodePudding user response：

So as the item with productId is saved three times it is showing the last item instead of the one related to documentId

IF any columns of the query have the same column name, the values assigned may(will be) inconsistent in that the value of the last column, of the repeated name, will be the value assigned to all the other columns that have the same name (etc).

I would suggest that the fix is to use unique column names. e.g. instead of productId in the Barcode that you use a column name more indicative of the use of the value, it could be considered as a map to the product so perhaps barcode_productIdMap.

it is not the query that is at fault but how Room handles retrieving and assigning values.

Consider your second image (with some marking):-

The above is explained in more detail, with examples, in this answer

Which is the correct id, productId, quantity (Rhetorical).

How is Room supposed to know what goes where? (Rhetorical)

Consider that the following query extracts exactly the same data (but with the data columns in a different sequence):-

@Query("SELECT products.*,products_barcodes.*,document_products.* FROM document_products dp LEFT JOIN products ON products.id = ....

How is Room meant to cope with the different order (should the correct productId be the first or the second (Rhetorical)).

with unique column names the column order is irrelevant as Room then knows exactly what column is associated with what field, there is no ambiguity.
with unique columns tablename.columname can be reduced to just the column name in the SQL, so the SQL can be simplified.