Android - sql query for group_by items

Question

I'm creating android app where user enters some Receipts and Logs. Relation is: Receipt has_many Logs.

Here are my receipt and log model.

@Table(name = "Logs")
public class Logs extends Model {
    @Column(name="PlateNumber")
    String plate_number;
    @Column(name="SortID")
    String sort_id;
    @Column(name="Grade")
    String grade;
    @Column(name = "Diametar")
    double diameter;
    @Column(name="Length")
    double length;
    @Column(name="CreatedAt")
    Date createdAt;
    @Column(name="Receipt")
    Receipt receipt;
    @Column(name = "Price")
    Price price;
}


@Table(name = "Receipt")
public class Receipt extends Model {
    @Column(name="Place")
    String place;
    @Column(name="ShippingNumber")
    String shippingNumber;
    @Column(name="Warehouse")
    String warehouse;
    @Column(name="Carrier")
    String carrier;
    @Column(name="LicencePlate")
    String licencePlate;
    @Column(name = "Driver")
    String driver;
    @Column(name = "Customer")
    String customer;
    @Column(name= "DestWarehouse")
    String destWarehouse;
    @Column(name = "Employee")
    String employee;
    @Column(name = "PriceType")
    String priceType;
    @Column(name = "PriceCorrection")
    Double priceCorrection;
    @Column(name = "PriceCorrection2")
    Double priceCorrection2;
    @Column(name = "Supplier")
    String supplier;
    @Column(name = "CreatedAt")
    Date createdAt;
}

Here is my activity where I'm trying to achieve this but unsuccessfully.

public class LogsRecapitulation extends AppCompatActivity {

    private ListView mainListView;
    private BaseAdapter listAdapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main_listview);
        mainListView = (ListView) findViewById(R.id.ListViewItem);

        //recieve RecepitID in displayLogs.activity
        final long forwardedId = (long) getIntent().getExtras().get(String.valueOf("recepitID"));
        List<Logs> logsList = new Select().from(Logs.class).where("Receipt = " + forwardedId).groupBy("SortID").execute(); // I grouped Logs by sortId in this line.

        listAdapter = new RecapitulationArrayAdapter(logsList);
        mainListView.setAdapter(listAdapter);
    }

    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;
        }
    }

}

So, what I've done so far is that I display logs by sortID in this BaseAdapter, but in each sort it show's me only last added log and it's data.

Now I need to display data like it is on image below - part where is REKAPITULAT. There I need group by sortID and then in each sort I need group them by grade.

For example if I have like on image sort 1 with 4 logs, 2 of each grade (A,B) then it need's to display it like on image.

I'm stuck here and I would be very grateful if someone knows how to do it and is willing to help.

Question: How to create sql query or fix this my code to display data like it is on image above?

Answer 1

Here's an example query with GROUP BY and JOIN using ActiveAndroid:

new Select("SUM(Logs.Price)")
   .from(Logs.class)
   .join(Receipt.class)
   .on("Receipt.Id = Logs.Receipt")
   .groupBy("Logs.sortID")
   .execute();

From this query, you'll get a Log instance, so you'll need to map the SUM(Logs.Price) field to a valid Log field, such as Price. For example:

new Select("SUM(Logs.Price) AS Logs.Price")

The query should be adapted for your use case.