Android: Distinct and GroupBy in ContentResolver

Question

What would be the correct way to add DISTINCT and/or GROUPBY to ContentResolver-based queries?

Right now I have to create custom URI for each special case.

Is there a better way?

(I still program for 1.5 as lowest common denominator)

Answer 1

You can do nice hack when querying contentResolver, use:

String selection = Models.SOMETHING + "=" + something + ") GROUP BY (" + Models.TYPE;

Answer 2

If you want to use DISTINCT with SELECT more then one column, You need to use GROUP BY.
Mini Hack over ContentResolver.query for use this:

Uri uri = Uri.parse("content://sms/inbox");
        Cursor c = getContentResolver().query(uri, 
            new String[]{"DISTINCT address","body"}, //DISTINCT
            "address IS NOT NULL) GROUP BY (address", //GROUP BY
            null, null);
        if(c.moveToFirst()){
            do{
                Log.v("from", "\""+c.getString(c.getColumnIndex("address"))+"\"");
                Log.v("text", "\""+c.getString(c.getColumnIndex("body"))+"\"");

            } while(c.moveToNext());
        }

This code select one last sms for each of senders from device inbox.
Note: before GROUP BY we always need to write at least one condition. Result SQL query string inside ContentResolver.query method will:

SELECT DISTINCT address, body FROM sms WHERE (type=1) AND (address IS NOT NULL) GROUP BY (address) 

Answer 3

Since no one came to answer I'm just going to tell how I solved this. Basically I would create custom URI for each case and pass the criteria in selection parameter. Then inside ContentProvider#query I would identify the case and construct raw query based on table name and selection parameter.

Here's quick example:

switch (URI_MATCHER.match(uri)) {
    case TYPES:
        table = TYPES_TABLE;
        break;
    case TYPES_DISTINCT:
        return db.rawQuery("SELECT DISTINCT type FROM types", null);
    default:
        throw new IllegalArgumentException("Unknown URI " + uri);
    }
    return db.query(table, null, selection, selectionArgs, null, null, null);