How to query from MEDIA provider with "group by" option?

Question

I'm a newbie to Android. Actually, I want to query data from Media provider with Content provider & content resolver.

c = mContent.query(CONTENT_URI,projection,where,null,null); 

My question is, how can I query data from media provider as below using a GROUP BY clause:

select DISTINCT _id, count(_id), _data FROM aaa_table WHERE _data LIKE "A" OR _data LIKE "B" GROUP BY _id;

I have tried setting projection and where as follows:

 final String[] projection = new String[] {
                "_id", 
                "COUNT ("+ _id +")" ,
                "_data" 
                }; 

and where:

_data LIKE "A" OR _data LIKE "B"

but, I couldn't find how to set the query option GROUP BY _id.

Please help me.

Heintz

Answer 1

where = "_data LIKE 'A' OR _data LIKE 'B'";
where += ") GROUP BY (_id"; // note the char ')' and '(', the ContentResover will completed for U
c = mContent.query(CONTENT_URI,projection,where,null,null); 

referance page = http://zengyan2012.iteye.com/blog/1118963

Underpay

seems like this might not be an entirely-reliable hack – Lysias

Answer 2

You can't from a ContentProvider. Now, if you're writing your ContentProvider you could implement it. Inside your content provider you'd have to use a SQLiteQueryBuilder class which has a query() method that takes a GROUP BY string.

http://developer.android.com/reference/android/database/sqlite/SQLiteQueryBuilder.html

This class also has a setDistinct(true) method that sets the query as DISTINCT, as you indicated you require in your SQL statement.

Nationality

Content Provider Group By is implemented here: github.com/novoda/SQLiteProvider – Eserine

Answer 3

I am not sure if that possible.

I was struggling with similar stuff lately, and I managed to workaround by inserting the data from ContentProvider into a temporary table and querying the table for results.

The story is that the data behind a ContentProvider might not be a database. It can be XML, JSON, FileSystem etc... So these do not have the GROUP BY option, and therefore they left it out. You also can't suppose always that count(_id) will work.

Steen

Answer 4

first off all excuse my POOR English! I'm new to Java/Android, started with 4.2.1 and fight with that too almost 2 days, then i start reading some more details about SQLiteQueryBuilder the query part is pretty much that what u are looking for ;)

it have:

public Cursor query (SQLiteDatabase db, String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder)

the query "function" of the Content Provider only gives you:

query(Uri uri, String[] projection, String selection,String[] selectionArgs, String sortOrder)

here u can trick around, i will post you my code snip:

    @Override
public Cursor query(Uri uri, String[] projection, String selection,
        String[] selectionArgs, String sortOrder) {
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    final SQLiteDatabase db = mOpenHelper.getReadableDatabase();
/* a String is a Object, so it can be null!*/
    String groupBy = null;
    String having = null;

    switch (sUriMatcher.match(uri)) {
...
...
...
        case EPISODES_NEXT:
        groupBy = "ShowID";
        queryBuilder.setTables(EpisodenTable.TableName);
        break;
    default:
        throw new IllegalArgumentException("Unknown URI " + uri);
    }

    Cursor c = queryBuilder.query(db, projection, selection, selectionArgs,
            groupBy, having, sortOrder);
    c.setNotificationUri(getContext().getContentResolver(), uri);
    return c;
}

thats its!

here the code i use to execute:

        Cursor showsc = getContext().getContentResolver().query(
            WhatsOnTVProvider.CONTENT_EPISODES_NEXT_URI,
            EpisodenTable.allColums_inclCount,
            String.valueOf(Calendar.getInstance().getTimeInMillis() / 1000)
                    + " < date", null, null);

Groark

Answer 5

This approach worked for me: (I have found the solution here)

val projection = arrayOf(
        MediaStore.Images.Media.BUCKET_DISPLAY_NAME,
        MediaStore.MediaColumns.DATA
    )
    val selection = " 1=1 ) GROUP BY (${MediaStore.Images.Media.BUCKET_DISPLAY_NAME}"
    val cursor = contentResolver.query(
        MediaStore.Images.Media.EXTERNAL_CONTENT_URI,
        projection,
        selection,
        null,
        null
    )

Apiece