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)

Columbus

Answer 1

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

Columbus

@njzk2 If I understand you correctly, you are saying that it's possible to get the instance of the actual contacts db? – Caneghem

@Ilya_Gazman no, I'm saying that this solution only works if you are the one implementing the content provider. – Yearning

@Yearning sorry for luck of knowledge but in order to implement the content provider you need to write a costume rom, right? It's like a part of Android core system, right? – Caneghem

@Ilya_Gazman here we are talking about content providers in general. If you have a specific question about contacts, write a new question. – Yearning

Answer 2

You can do nice hack when querying contentResolver, use:

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

Saltation

Nice hack if you don't have access to ContentProvider source. Thank you. – Starfish

sorry its my fault I used GROUP BY with the column I wanted to DISTINCT with I'm not good in SQL – Raskin

this is not working any more. is there any way to select distinct in ICS? – Roundabout

"What a Terrible Failure" on behalf of the designers :) Works for me - min sdk 15, target sdk 19. One note: I added a "having" clause. – Saddleback

Answer 3

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) 

Grunt

Note: Column with distinct must be at the begging of projection array – Befit

Answer 4

In your overridden ContentProvider query method have a specific URI mapping to using distinct.

Then use SQLiteQueryBuilder and call the setDistinct(boolean) method.

@Override
public Cursor query(Uri uri, String[] projection, String selection,
        String[] selectionArgs, String sortOrder)
{
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    boolean useDistinct = false;

    switch (sUriMatcher.match(uri))
    {
    case YOUR_URI_DISTINCT:
        useDistinct = true;
    case YOUR_URI:
        qb.setTables(YOUR_TABLE_NAME);
        qb.setProjectionMap(sYourProjectionMap);
        break;

    default:
        throw new IllegalArgumentException("Unknown URI " + uri);
    }

    // If no sort order is specified use the default
    String orderBy;
    if (TextUtils.isEmpty(sortOrder))
    {
        orderBy = DEFAULT_SORT_ORDER;
    }
    else
    {
        orderBy = sortOrder;
    }
    // Get the database and run the query
    SQLiteDatabase db = mDBHelper.getReadableDatabase();
            // THIS IS THE IMPORTANT PART!
    qb.setDistinct(useDistinct);
    Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, orderBy);

    if (c != null)
    {
        // Tell the cursor what uri to watch, so it knows when its source data changes
        c.setNotificationUri(getContext().getContentResolver(), uri);
    }

    return c;
}

Pyrotechnics

what is the mDBHelper ؟ – Oteliaotero

Answer 5

Though I have not used Group By, I have used Distinct in content resolver query.

Cursor cursor = contentResolver
                .query(YOUR_URI,
                        new String[] {"Distinct "+ YOUR_COLUMN_NAME},
                        null,
                        null, 
                        null);

Ferrotype

This is fine if you just need one column of data, but when you want more than one you'll need to use GROUP BY – Dunedin

Answer 6

Adding the Distinct keyword in the projection worked for me too, however, it only worked when the distinct keyword was the first argument:

String[] projection = new String[]{"DISTINCT " + DBConstants.COLUMN_UUID, ... };

Clerestory

Answer 7

In some condition, we can use "distinct(COLUMN_NAME)" as the selection, and it work perfect. but in some condition, it will cause a exception.

when it cause a exception, i will use a HashSet to store the column values....

Michalemichalski

Answer 8

// getting sender list from messages into spinner View
    Spinner phoneListView = (Spinner) findViewById(R.id.phone_list);
    Uri uri = Uri.parse("content://sms/inbox");     
    Cursor c = getContentResolver().query(uri, new String[]{"Distinct address"}, null, null, null);
    List <String> list;
    list= new ArrayList<String>();
    list.clear();
    int msgCount=c.getCount();
    if(c.moveToFirst()) {
        for(int ii=0; ii < msgCount; ii++) {
            list.add(c.getString(c.getColumnIndexOrThrow("address")).toString());
            c.moveToNext();
        }
    }
    phoneListView.setAdapter(new ArrayAdapter<String>(BankActivity.this, android.R.layout.simple_dropdown_item_1line, list));

Atomicity

you'd better explain your answer – Hogan

Answer 9

When you have multiple columns in your projection you should do like this:

    val uri = MediaStore.Images.Media.EXTERNAL_CONTENT_URI
    val projection = arrayOf(
        "DISTINCT " + MediaStore.Images.Media.BUCKET_ID,
        MediaStore.Images.Media.BUCKET_DISPLAY_NAME,
        MediaStore.Images.Media.BUCKET_ID,
        MediaStore.MediaColumns.DATA
    )
    val groupBySelection = " 1) GROUP BY (${MediaStore.Images.Media.BUCKET_ID}"
    contentResolver.query(
        uri,
        projection,
        null,
        groupBySelection,
        null,
        null
    )

groupBySelection with closing bracket and number "1" inside is a tiny hack, but it works absolutely fine

Churchless

Answer 10

I created a utility method for using group by and distinct.

Usage

Here is an example of selecting unseen thread_id with the last message date from the MMS database.

query(contentResolver= contentResolver,
        select = arrayOf(Mms.THREAD_ID, "max(${Mms.DATE}) as date"),
        from = Mms.CONTENT_URI,
        where = "${Mms.SEEN} = 0",
        groupBy = "1",
        orderBy = "2 desc"
        ).use {
    while (it?.moveToNext() == true){
        val threadId = it.getInt(0)
        val date = it.getLong(1)
    }
}

Source

fun query(
        contentResolver: ContentResolver,
        from: Uri,
        select: Array<String>,
        where: String? = null,
        groupBy: Array<out String>? = null,
        distinct: Boolean = false,
        selectionArgs: Array<out String>? = null,
        orderBy: String? = null,
): Cursor? {
    val tmpSelect = select[0]
    val localWhere =
            if (groupBy == null) where
            else "${where ?: "1"}) group by (${groupBy.joinToString()}"
    if (distinct) {
        select[0] = "distinct $tmpSelect"
    }
    val query = contentResolver.query(from, select, localWhere, selectionArgs, orderBy)
    select[0] = tmpSelect
    return query
}

Caneghem

Answer 11

Maybe its more simple to get distinct values, try to add the DISTINCT word before the column name you want into the projection table

String[] projection = new String[]{
                BaseColumns._ID,
                "DISTINCT "+ Mediastore.anything.you.want
};

and use it as an argument to query method of the content resolver!

I hope to help you, cause I have the same question before some days

Rutherfordium

This doesn't work for me. My code: String[] projection = new String[] { "DISTINCT" + ContactsContract.CommonDataKinds.Phone.DISPLAY_NAME}; Cursor cur = cr.query( ContactsContract.CommonDataKinds.Phone.CONTENT_URI, projection, "LENGTH(" + ContactsContract.CommonDataKinds.Phone.NUMBER + ") >= 8", null, sortOrder); I get invalid column DISTINCT display_name – Whorled

Lets try to add a space at "DISTINCT" -> "DISTINCT "; At this blog javment.blogspot.com/2011/07/… I have made a simple tutorial about this, may help you. – Rutherfordium

@Rutherfordium it works only with single column its stupid solution if you have to query multiple columns – Schulte

Column with distinct must be at the begging of projection array. Also you must specify selection "some_column IS NOT NULL) GROUP BY (distinct_column_name". Source: qaru.site/questions/2903561/… – Befit