How to get list of items and sum of values of a column from a single query in room database in android

Question

I would like to show date wise item list in my app. The list will have a header with date and sum of a value of a column.

24.12.2021	25
Book1	10
Book2	05
Book3	10

This is the entity:

@Entity(tableName = "books")
public class Books {
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    private int id;

    @ColumnInfo(name = "date")
    @TypeConverters({TimestampConverter.class})
    private long mDate;

    @ColumnInfo(name = "quantity")
    private double mQuantity;

    @ColumnInfo(name = "name")
    private String mName;

I am trying to use nested recyclerview and have created a Parent class :

public class ParentEntry {
    private double totalBooks;
 
    private long dateToday;

    public  List<Books> books;
}

This is the dao:

 @Query("SELECT SUM(quantity) as totalBooks, date as dateToday, *  FROM books GROUP BY date ORDER 
  BY date Asc ")
    LiveData<List<ParentEntry>> getAllParentEntries();

But I am getting the following errors: error: Cannot figure out how to read this field from a cursor. warning: The query returns some columns [id, date, quantity, name] which are not used. You can use @ColumnInfo annotation on the fields to specify the mapping. You can annotate the method with @RewriteQueriesToDropUnusedColumns to direct Room to rewrite your query to avoid fetching unused columns. ParentEntry has some fields [books] which are not returned by the query. If they are not supposed to be read from the result, you can mark them with @Ignore annotation. You can suppress this warning by annotating the method with @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH). Columns returned by the query:totalBooks, dateToday, id, date, quantity, name. Fields in ParentEntry: totalBooks, dateToday, books. LiveData<List> getAllParentEntries();

How can I query both the list and sum of quantity column and date in a single query ? Any help is highly appreciated.

Answer 1

For room to build a List within a POJO you need an @Relation.

So the ParentEntity needs to be something like :-

class ParentEntry {
    long dateToday;
    double totalBooks;
    @Relation(entity = Books.class,
    parentColumn = "dateToday",
    entityColumn = "date")
    List<Books> booksList;

}

So you need a query that then retrieves the non-list values (the dateToday and the totalBooks) with column names that match the member variable names, which could be :-

@Transaction
@Query("SELECT date AS dateToday, total(quantity) AS totalBooks FROM books GROUP BY date ORDER BY date ASC;")
abstract List<ParentEntryV2> getParentEntries();

• note total rather than sum has been used as total is guaranteed to return a floating point value, sum can return null and can also throw an integer overflow exception. see https://sqlite.org/lang_aggfunc.html#sumunc

e.g. from a books table :-

Using:-

    for(ParentEntryV2 pev2: dao.getParentEntries()) {
        Log.d("PEINFO_V2","Date is " + pev2.dateToday + " TotalBooks is " + pev2.totalBooks + " Number of Books in list is " + pev2.booksList.size());
        for (Books b: pev2.booksList) {
            Log.d("PEINFO_V2","\tBook is " + b.getmName() + " quantity is " + b.getmQuantity());
        }
    }

results in :-

D/PEINFO_V2: Date is 1640124943 TotalBooks is 30.0 Number of Books in list is 3
D/PEINFO_V2:    Book is Book1 quantity is 10.0
D/PEINFO_V2:    Book is Book2 quantity is 10.0
D/PEINFO_V2:    Book is Book3 quantity is 10.0
D/PEINFO_V2: Date is 1640211343 TotalBooks is 30.0 Number of Books in list is 3
D/PEINFO_V2:    Book is Book4 quantity is 10.0
D/PEINFO_V2:    Book is Book5 quantity is 10.0
D/PEINFO_V2:    Book is Book6 quantity is 10.0
D/PEINFO_V2: Date is 1640297743 TotalBooks is 30.0 Number of Books in list is 3
D/PEINFO_V2:    Book is Book7 quantity is 10.0
D/PEINFO_V2:    Book is Book8 quantity is 10.0
D/PEINFO_V2:    Book is Book9 quantity is 10.0

Additional

regarding the date issues (i.e. you only want the date part from a date/time) then you could use the following BUT it requires 2 queries and an abstract class rather then an interface and additional function to invoke both queries in a single transaction the Dao's. The Dao's being :-

@Transaction
@Query("SELECT date AS dateToday, total(quantity) AS totalBooks FROM books GROUP BY date(date/:timefactor,'unixepoch') ORDER BY date ASC;")
abstract List<ParentEntryV2> getParentEntries(long timefactor);

@Query("SELECT * FROM books WHERE date(date/:timefactor,'unixepoch')=date(:date/:timefactor,'unixepoch')")
abstract List<Books> getBooksPerDate(long date,long timefactor);

@Transaction
@Query("")
List<ParentEntryV2> getParentEntriesV2() {
    List<ParentEntryV2> parentEntryV2List = getParentEntries(1 /* 1000 if precision to millisecs */);
    for(ParentEntryV2 pe: parentEntryV2List) {
        pe.booksList = getBooksPerDate(pe.dateToday,1 /* 1000 if precision to millisecs */);

    }
    return parentEntryV2List;
}

• note that timefactor allows 1 or 1000 for switching between precision to 1 second (1) and precision to millis (1000).
• Although the first query is basically identical bar the GROUP BY, the second overwrites the booklist (books with exact date/time match) with the required booklist (books that match the date).