How to select field data as a list?

Question

My database stores data in Receipt and File entities. These entities have a one-to-many relationship (one Receipt can have several Files). I need to select Receipts with Files but not all fields from these tables.

I want to select id, description and createdAt from the Receipt entity and imageUrl from the File entity. These entities contain other fields that I don't need, I want to create a list that displays the basic information.

I tried this but get an error:

Cannot figure out how to read this field from a cursor

@Dao
abstract class ReceiptDao  {
    @Query(
        """
        SELECT id, description, createdAt, (SELECT imageUrl FROM files WHERE receiptId == :id) as listOfImageUrls
        FROM receipts
        WHERE id == ":id"
        ORDER BY createdAt DESC
        """ )
    abstract fun findReceiptsWithFilesById(id: Long): List<ReceiptWithFiles>
}

class ReceiptWithFiles(
    val id: Long,
    val description: String? = null,
    val createdAt: LocalDateTime? = null,
    val listOfImageUrls : List<String> = listOf(),
)

Answer 1

Typically you would utilise a combination of @Embedded and @Relation in a POJO.

• @Embedded to embed the parent object (the Receipts object) and @Relaion for the child objects ( List<Files> )

so:-

class ReceiptWithFiles(
    @Embedded
    val receipts: Receipt,
    @Relation(entity = Files::class, parentColumn= "id", entityColumn="receiptId")
    val files: List<Files>
)

You would invoke the above using

@Transaction
@Query("SELECT * FROM receipts WHERE id =:id ORDER BY createdAt DESC")
abstract fun findReceiptsWithFilesById(id: Long): List<ReceiptWithFiles>

This doesn't exactly return just the said fields (id, description and createdAt) but the entire Receipts object, which includes the said fields and any other fields of the Receipts object and the List of related Files objects.

• Note the above is in-principle code and has not been coded, compiled or run.
• Room knows how to obtained the List based upon the @Relation annotation.
  • entity=Files:class could be omitted as the child table can be ascertained via the class of the files field.
  • @Transaction places all executions in a single transaction and subdues the warning that would otherwise be issued when the project is compiled.

However, as you say you only want the specific fields, then you could use:-

class ReceiptWithFiles(
    val id: Long,
    val description: String? = null,
    val createdAt: LocalDateTime? = null,
    @Relation(entity = Files::class, parentColumn= "id", entityColumn="receiptId")
    val listOfImageUrls : List<String> = listOf()
)

• i.e. the Receipts object is not embedded, but just the respective fields are extracted and used to set the respective values along with the related Files.

The Query could be the same or it could be.

@Transaction
@Query("SELECT id, description, createdAt FROM receipts WHERE id =:id ORDER BY createdAt DESC")
abstract fun findReceiptsWithFilesById(id: Long): List<ReceiptWithFiles>

• the former, SELECT * .... would issue warnings about unused/unnecessary columns

Additional - The issue(s) with what was tried

With your Query and the sub query to get the imageUrls from the Files. The sub query will return/output a single value from the list of related File rows.

If the group_concat function were used then the single value would still be a single value but consisting of all the related imageUrl values separated by a comma (the default separator, another if one is passed to the function). It would not be a list of separate Strings per imageUrl.

If instead of a sub query a JOIN were used then the result would be the cartesian product i.e. a row for each combination of Receipt and File. The resultant output would be a ReceiptWithFile object per such a combination (a suitable Map could be used to combine multiple rows of the same key).

The following SQL (used in an SQLite tool) demonstrates the above:-

DROP TABLE IF EXISTS files;
DROP TABLE IF EXISTS receipts;
CREATE TABLE IF NOT EXISTS receipts (id INTEGER PRIMARY KEY, description TEXT, createdat TEXT, otherNotWanted TEXT);
CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY, receiptId INTEGER REFERENCES receipts(id), imageUrl TEXT, blah TEXT );

INSERT OR IGNORE INTO receipts VALUES
    (1,'R1',datetime('now'),'R1 blah blah blah'),
    (2,'R2',datetime('now','+10 minutes'),'R2 blah blah blah'),
    (null,'R3',datetime('now','+20 minutes'),'R3 blah blah blah'),
    (100,'R100',datetime('now','+30 minutes'),'R3 blah blah blah')
;
INSERT OR IGNORE INTO files VALUES
    (null,1,'U1','F1 blah'),
    (null,1,'U2','F2 blah'),
    (null,1,'U3','F3 blah'),
    (null,2,'U4','F4 blah'),
    (null,2,'U5','F5 blah'),
    (null,3,'U6','F6 blah'),
    (null,100,'U7','F7 blah'),
    (null,100,'U8','F8 blah'),
    (null,100,'U9','F9 blah')
;
/* Original Query but with example of group_concat */
SELECT id, description, createdAt, (SELECT imageUrl FROM files WHERE receiptId == receipts.id ) as listOfImageUrls,
    (SELECT group_concat(imageUrl) FROM files WHERE receiptId == receipts.id ) as otherlistOfImageUrls
        FROM receipts
        WHERE  1 /*id == ":id"*/
        ORDER BY createdAt DESC;
/* Join version aka cartesian product*/ 
SELECT receipts.id,description, createdAt, imageurl FROM receipts JOIN files ON receipts.id = files.receiptId;
DROP TABLE IF EXISTS files;
DROP TABLE IF EXISTS receipts;

The two results from the SELECTs being:-

1. the listOfImageUrls column showing the result as per the question.
2. the otherlistOfImageUrls column showing the result if the group_concat function were used (i.e. the CSV of imageUrls)

and:-

1. The cartesian product i.e. a row per combination of the related data.