How to group items from database and display them - Android

Question

I have receipt and logs model in android app. Receipt hasMany logs.

I made query for group_by logs by sortID and grade.

//recieve RecepitID and query to group logs final long forwardedId = (long) getIntent().getExtras().get(String.valueOf("recepitID")); List<Logs> logsList = new Select().from(Logs.class).where("Receipt = " + forwardedId).groupBy("SortID, Grade").execute(); 

This grouping works fine. Problem is next. I need to have output like this (part in red circle): but I get it like this:

And this is part of code how I done this.

public class LogsRecapitulation extends AppCompatActivity {      private ListView mainListView;     private BaseAdapter listAdapter;     private TextView logsCount;      @Override     protected void onCreate(Bundle savedInstanceState) {         super.onCreate(savedInstanceState);         setContentView(R.layout.recapitulation_listview);         mainListView = (ListView) findViewById(R.id.ListViewItem);          //recieve RecepitID and query to group logs         final long forwardedId = (long) getIntent().getExtras().get(String.valueOf("recepitID"));         List<Logs> logsList = new Select().from(Logs.class).where("Receipt = " + forwardedId).groupBy("SortID, Grade").execute();          TextView result = (TextView) findViewById(R.id.LogMassResult);         double sum = 0.0;         for (int i = 0; i < logsList.size(); i++) {             sum += logsList.get(i).getM3();         }         result.setText(String.format("%.2f m3", sum));          for (int i = 0; i < logsList.size(); i++) {             if (logsList.get(i).receipt.priceType.equals("Na panju")) {                 TextView stumpPriceKN = (TextView) findViewById(R.id.sumPriceKN);                 double sumPricekn = 0.0;                  for (int j = 0; j < logsList.size(); j++) {                     sumPricekn += logsList.get(j).price.stumpPrice_kn * logsList.get(j).getM3();                 }                 stumpPriceKN.setText(String.format("%.2f KN", sumPricekn));              } else {                 TextView roadKN = (TextView) findViewById(R.id.sumPriceKN);                 double roadPrKn = 0.0;                 for (int j = 0; j < logsList.size(); j++) {                     roadPrKn += logsList.get(j).price.roadPrice_kn * logsList.get(j).getM3();                 }                 roadKN.setText(String.format("%.2f KN", roadPrKn));             }         }          for (int i = 0; i < logsList.size(); i++) {             if (logsList.get(i).receipt.priceCorrection > 0 && logsList.get(i).receipt.priceType.equals("Na panju")) {                 TextView corecctionPriceKn = (TextView) findViewById(R.id.correctionPriceKN);                 double correcSumKN = 0.0;                 for (int j = 0; j < logsList.size(); j++) {                     correcSumKN += (logsList.get(j).price.stumpPrice_kn * logsList.get(j).getM3()) + ((logsList.get(j).price.stumpPrice_kn * logsList.get(j).getM3()) * logsList.get(j).receipt.priceCorrection / 100);                 }                 corecctionPriceKn.setText(String.format("%.2f KN", correcSumKN));             } else if (logsList.get(i).receipt.priceCorrection > 0 && logsList.get(i).receipt.priceType.equals("Šumska cesta")) {                 TextView corecctionPriceKn = (TextView) findViewById(R.id.correctionPriceKN);                 double correcSumKN = 0.0;                 for (int j = 0; j < logsList.size(); j++) {                     correcSumKN += (logsList.get(j).price.roadPrice_kn * logsList.get(j).getM3()) + ((logsList.get(j).price.roadPrice_kn * logsList.get(j).getM3()) * logsList.get(j).receipt.priceCorrection / 100);                 }                 corecctionPriceKn.setText(String.format("%.2f KN", correcSumKN));             } else {                 TextView priceHolder = (TextView) findViewById(R.id.KorekcijaCijene);                 TextView corecctionPriceKn = (TextView) findViewById(R.id.correctionPriceKN);                 priceHolder.setText("");                 corecctionPriceKn.setText("");             }         }          listAdapter = new RecapitulationArrayAdapter(logsList);         mainListView.setAdapter(listAdapter);          //display logs count         logsCount = (TextView) findViewById(R.id.logsCount);         logsCount.setText(String.valueOf(logsList.size()));     }      private class RecapitulationArrayAdapter extends BaseAdapter {         private LayoutInflater inflater;         private List<Logs> logsList;          public RecapitulationArrayAdapter(List<Logs> logsList) {             inflater = LayoutInflater.from(LogsRecapitulation.this);             this.logsList = logsList;         }          @Override         public int getCount() {             return logsList.size();         }          @Override         public Object getItem(int position) {             return logsList.get(position);         }          @Override         public long getItemId(int position) {             return logsList.get(position).getId();         }          @Override         public View getView(int position, View convertView, ViewGroup parent) {             if (convertView == null) {                 convertView = inflater.inflate(R.layout.logs_recapitulation, parent, false);             }             Logs log = logsList.get(position);             ((TextView) convertView.findViewById(R.id.rec_log_sort)).setText(log.sort_id);             ((TextView) convertView.findViewById(R.id.rec_log_class)).setText(log.grade);             ((TextView) convertView.findViewById(R.id.rec_log_count)).setText(String.valueOf(logsList.size()));             ((TextView) convertView.findViewById(R.id.rec_logs_mass)).setText(String.format("%.2f m3", log.getM3()));              if (log.receipt.priceType.equals("Na panju")) {                 ((TextView) convertView.findViewById(R.id.rec_log_price_default)).setText(String.valueOf(log.price.stumpPrice_kn));             } else {                 ((TextView) convertView.findViewById(R.id.rec_log_price_default)).setText(String.valueOf(log.price.roadPrice_kn));             }              if (log.receipt.priceType.equals("Na panju")) {                 ((TextView) convertView.findViewById(R.id.rec_calculated_price)).setText(String.format("%.2f KN", log.price.stumpPrice_kn * log.getM3()));             } else {                 ((TextView) convertView.findViewById(R.id.rec_calculated_price)).setText(String.format("%.2f KN", log.price.roadPrice_kn * log.getM3()));             }              return convertView;         }     }  } 

I'm using ActiveAndroid and displaying this in listview.

PROBLEM is with displaying these grouped items. I’m displaying them in BaseAdapter listView and it shows me only one item per group but it needs to show me multiple items (because it has more than 4 items in each group).

Can anyone give me advice what should I do to get output like on first image?

Answer 1

It looks like you are trying to get results similar to the following SQL:

select sort, grade, count(grade), sum(mass), price from logs where receiptid = forwardedId group by sort, grade; 

You show the displayed count computed as logList.size(). Instead, if you are going to have the database group the records, you will want to get the count from the database as well.

A couple of things may also help. 1. Verify the SQL query directly against the database to verify you are starting with the intended result set. You can use the sqlite3 client to test assuming you are using an SQLite db. 2. Enable ActiveAndroid logging in ActiveAndroid.initialize

Also you might double check the parameter spelling to make sure you are referencing the intended intent parameter.

Once you verify that the query results are correct, you can focus on whether you are rendering it in the list as you wish.

Finally be wary of doing work on the UI thread.

Answer 2

User 'user650881' is right (I cannot vote since repo < 50).

Additionally to achieve the summary for the total table you may execute the following SQL query:

select /*sort, grade,*/ count(grade), sum(mass), price from logs where receiptid = forwardedId /*group by sort, grade*/; 

if you would like to have the items grouped by different fields you may also try widow functions i.e.:

select count(grade) over(partition by fieldName) 

and group by is eventually not necessary