Fetching data from DB and populate a partitioned List

Question

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?

You should have a look to this tutorial about persistence and sqlite. — Augustin R
– Augustin R, Commented Jun 26, 2019 at 9:21
@AugustinR I am already using sqflite. Kindly read the question carefully. I want to know how to query the data in such a way so that i can populate the list in the way mentioned above. — Aman Kataria
– Aman Kataria, Commented Jun 26, 2019 at 11:00

Aman Kataria · Accepted Answer · 2019-06-28 17:08:05Z

1

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.

edited Jun 28, 2019 at 17:08

Aman Kataria

6162 gold badges14 silver badges26 bronze badges

answered Jun 26, 2019 at 10:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aman Kataria Over a year ago

transactionId is Primary Key for the table. If a row from the table is deleted, then there might be gaps in transactionids, since the deleted transactionId is not repeated by SQLite. will this create a problem?

Collectives™ on Stack Overflow

Fetching data from DB and populate a partitioned List

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related