How to GROUP BY when using a Content Provider?

Question

I have made a content provider for my application. For the sake of simplicity, I shall use an example. I have a table 'OrderDetails'.

1. I have a column for the OrderId.
2. I have a column for the quantity of the different product types that was purchased in that OrderId.
3. I have an OrderDetailId that is the primary key for the table.

Here is the table:

I want to write the query: SELECT OrderID, sum(quantity) FROM OrderDetails GROUP BY OrderID Which would return this:

However, I have tried to insert my GROUP BY clause in my URI for my content Provider but It will not work, so the resulting query becomes: SELECT OrderID, sum(quantity) FROM OrderDetails that returns this (the entire quanity of everything and the last OrderId):

Here is the method to get the cursor and simply print out the result that I have just made:

private void findQuantityByOrder(){
    Uri uri = DatabaseContract.CONTENT_URI_ORDER_DETAILS;
    String[] selection = new String[] {DatabaseContract.DatabaseOrderDetails.ORDER_ID,
            "sum("+ DatabaseContract.DatabaseOrderDetails.QUANTITY + ")"
                    +"GROUP BY(" + DatabaseContract.DatabaseOrderDetails.ORDER_ID + ")"};
    String projection = null;
    String sortBy = null;
    String[] args = null;

    Cursor cursor = getContentResolver().query(
            uri,
            selection,
            projection,
            args,
            sortBy);

    for (int i = 0; i < cursor.getCount(); i ++) {
        cursor.moveToPosition(i);
        int orderID = cursor.getInt(cursor.getColumnIndex("orderID"));
        int quantity = cursor.getInt(cursor.getColumnIndex("sum(quantity)"));

        System.out.println("orderId: " + orderID + ". Quanitity: " + quantity);
    }
}

It only prints out the entire sum of all orders, with the last ID that was on the table.

I believe that GROUP BY was removed and no longer supported. Is there any other way that will provide the same result?

Thank you

Answer 1

Not Sure if you are still looking for an answer, but, a hack for GROUP BY with Content Provider is to embed group by clause in the URI as a query parameter and read that in query method of the content provider.

So a Typical Query can look like

Uri uri = DatabaseContract.CONTENT_URI_ORDER_DETAILS + "?groupBy=" + DatabaseContract.DatabaseOrderDetails.ORDER_ID;

The whole Thing can look like this:

private void findQuantityByOrder(){
    Uri uri = DatabaseContract.CONTENT_URI_ORDER_DETAILS + "?groupBy=" + DatabaseContract.DatabaseOrderDetails.ORDER_ID;
    String[] projection = new String[] {DatabaseContract.DatabaseOrderDetails.ORDER_ID,
            "sum("+ DatabaseContract.DatabaseOrderDetails.QUANTITY + ")"};
    String selection = null;
    String sortBy = null;
    String[] args = null;

    Cursor cursor = getContentResolver().query(
            uri,
            projection,
            selection,
            args,
            sortBy);

    for (int i = 0; i < cursor.getCount(); i ++) {
        cursor.moveToPosition(i);
        int orderID = cursor.getInt(cursor.getColumnIndex("orderID"));
        int quantity = cursor.getInt(cursor.getColumnIndex("sum(quantity)"));

        System.out.println("orderId: " + orderID + ". Quanitity: " + quantity);
    }
}

And in your content provider code you can get hold of group by as easily as this,

String groupBy = uri.getQueryParameter("groupBy");

Hope this helps someone.

Answer 2

Answer to my own question:

There are answers out there on Stackoverflow - Android: Distinct and GroupBy in ContentResolver I will leave this question up because some thought has been put into the responses.

Whilst I feel that, as described in other answers, this should be sorted in the Content Provider itself. However, there is a quick fix to be had when you are calling your Content Provider:

In your Projection String you can simply add...

 String projection = COLUMN_NAME + "'a_condition' GROUP BY " + COLUMN_NAME" 

Hope this helps

Answer 3

I have made a content provider for my application

Unless you plan on having several apps all use this data, I do not recommend implementing a ContentProvider.

I believe that GROUP BY was removed and no longer supported

The ContentProvider/ContentResolver protocol does not support SQL in general, let alone GROUP BY. After all, there is no requirement that a ContentProvider be implemented using a SQL database.

Is there any other way that will provide the same result?

Implement the GROUP BY on the ContentProvider, not on the client. IOW, handle this the same way that you would with a REST-style Web service, where it is the Web service, not the client of the Web service, that implements the SQL.

For example, if a Uri with a path of /foo handles basic queries, /foo/summary might implement the SUM and GROUP BY bits.

I have tried to insert my GROUP BY clause in my URI for my content Provider but It will not work

Since we do not have your ContentProvider implementation, we cannot really comment on that. However, please note that GROUP BY is not something that you return, and so putting it in the projection would be an atypical choice.