Fetching data from DB and populate a partitioned List

I am confused about this both from front end point of view as well as querying the data from SQLite Database. If you have any idea how to solve either of these please do answer.

SQLite Database

I have a table likes this:

transactionId | productId | quantity
      1             2           1     
      2             4           0 
      3             1          null             
      4             3           1
      5             9           1             
      6             6           0
      7             1           1
      8             7           1
      9             8           1
     10             2           1
     11             0          null
     12             3           1
     13             5           1
     14             7           1
     15             1           0
     16             2           1
     17             9           1
     18             0          null
     19             2           1

Now I want to display this data in groups of 5 units(i.e. groups till 5 units are completed) in list in my flutter app.

So 1st group will have 8 items,

2nd will have 6 items,

and 3rd group will have 5 items (and is still incomplete since more items can be added till quantity for that group becomes 5)

Something like this:

Now my App can have multiple groups like this. Also, I don’t think Grid view builder can work here since for each group I’ll have to display some data for the group as well as accumulated data (which isn’t shown in the picture)

Questions:

1) How to query data from SQFLite database?

2) How to display the queried data in my Flutter App front end?

Answer

Unfortunately, this type of problem requires a recursive CTE (or other iterative processing).

Assuming that transactionId is consecutive with no gaps:

with recursive cte as (
      select transactionId, productId,
             coalesce(quantity, 0) as quantity,
             1 as bin
      from t
      where transactionId = 1
      union all
      select t.transactionId, t.productId,
             (case when cte.quantity > 5
                   then 0 else cte.quantity
              end)  + coalesce(t.quantity, 0) as quantity,
             (case when cte.quantity > 5 then 1 else 0 end) + cte.bin as bin
      from cte join
           t
           on t.transactionId = cte.transactionId + 1
     )
select *
from cte;

If transactionId has gaps or other issues, just use row_number() (in another CTE) to create an appropriate column for the where clauses.