Query MediaStore on Android Q

Question

I am fetching all albums of images using MediaStore and it was working fine before Android Q. But today I increased my compile version to 29 and it is throwing this exception :

android.database.sqlite.SQLiteException: near "GROUP": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT bucket_id, bucket_display_name, mime_type FROM images WHERE ((is_pending=0) AND (is_trashed=0) AND (volume_name IN ( 'external_primary' ))) AND ( GROUP BY bucket_id )

Here is my code to query MediaStore :

    String[] PROJECTION_BUCKET = {
            MediaStore.Images.ImageColumns.BUCKET_ID,
            MediaStore.Images.ImageColumns.BUCKET_DISPLAY_NAME,
            MediaStore.Images.ImageColumns.MIME_TYPE};

    String BUCKET_GROUP_BY = "1) GROUP BY 1,(2";
    String BUCKET_ORDER_BY = "MAX(datetaken) DESC";

    Cursor cur = context.getContentResolver().query(getUriOfType(type),
            PROJECTION_BUCKET,
            BUCKET_GROUP_BY,
            null,
            BUCKET_ORDER_BY);

As Group is not working in Android Q.

Any solution for this?

See https://stackoverflow.com/questions/11570521/android-how-to-query-a-list-of-bucket-name#answer-20486539 — Mateen Chaudhry, Jun 30 '19 at 08:19
Get rid of that SQL, as it makes assumptions about the database structure that are not going to be valid on all versions of SQL. Do a simple projection for your three columns. Do the max calculations, grouping, and ordering in Java. — CommonsWare, Jul 01 '19 at 10:50
@CommonsWare If we still have to do this though java then there is no purpose of SQL — Mateen Chaudhry, Jul 02 '19 at 02:11
There is no requirement for any `ContentProvider` to be implemented using a SQL-compliant database. In particular, there is no requirement for `MediaStore` to be implemented using a SQL-compliant database. You used a hack, one that happened to work for a while for a few devices. Now you are complaining that your hack does not work. You should not have used the hack in the first place, as there has never been a guarantee that arbitrary SQL can be hacked into a `MediaStore` query. So, take this opportunity to fix your code and make it more reliable across devices and versions. — CommonsWare, Jul 02 '19 at 11:18
@CommonsWare Can you please explain this in answer with code? So i can understand better and accept your answer? — Mateen Chaudhry, Jul 04 '19 at 04:26
@CommonsWare Reddit found the original source of this snippet of code, it's from the Android Gallery3d: https://www.reddit.com/r/androiddev/comments/c8qtpg/the_commonsblog_leaky_apis_are_leaky/esrbqqi/ — EpicPandaForce, Jul 04 '19 at 08:33
@EpicPandaForce: Ugh. Hopefully, some current or former Google engineer reads my blog post and is embarrassed. — CommonsWare, Jul 04 '19 at 11:50
"Can you please explain this in answer with code?" -- I do not understand enough of what your code is trying to accomplish to be able to come up with the Java equivalent rapidly. — CommonsWare, Jul 04 '19 at 11:53
@CommonsWare I have read your [blog post](https://commonsware.com/blog/2019/07/03/leaky-apis-are-leaky.html) and what I understand is that I was doing this wrong from start. Then my question is what is the proper way to group without using SQL as there is no clear guidance to do that. What i am trying to do is fetch all pictures album wise. — Mateen Chaudhry, Jul 05 '19 at 06:25
Your current query does not do that. It retrieves bucket information, not information about pictures. The only thing in your projection related to a picture is `MIME_TYPE`. If your plan is to get the buckets, then make follow-on queries to get the pictures for an individual bucket, you can do that by removing `BUCKET_GROUP_BY` and `BUCKET_ORDER_BY` (and probably `MIME_TYPE`), as the resulting `query()` will give you a list of all buckets for all images. — CommonsWare, Jul 05 '19 at 11:34
That list will need to be de-duplicated, and since I haven't worked with buckets and `MediaStore`, I don't know if there is a better way of getting the unique set of buckets and bucket IDs. — CommonsWare, Jul 05 '19 at 11:35

score 0 · Answer 1 · edited Oct 24 '20 at 08:12

The GROUP BY no longer works on android 10(Q) and neither does the COUNT. The following code snippet might help you.

String path = null;
String album = null;
String timestamp = null;
String countPhoto = null;
Uri uriExternal = android.provider.MediaStore.Images.Media.EXTERNAL_CONTENT_URI;
Uri uriInternal = android.provider.MediaStore.Images.Media.INTERNAL_CONTENT_URI;
String[] projection = {
        MediaStore.MediaColumns.DATA,
        MediaStore.Images.Media.BUCKET_DISPLAY_NAME,
        MediaStore.MediaColumns.DATE_MODIFIED
};
Cursor cursorExternal = getContentResolver().query(uriExternal, projection, "_data IS NOT NULL", null, null);
Cursor cursorInternal = getContentResolver().query(uriInternal, projection, "_data IS NOT NULL", null, null);
Cursor cursor = new MergeCursor(new Cursor[]{cursorExternal, cursorInternal});
while(cursor.moveToNext()){
    path = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.MediaColumns.DATA));
    album = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.Images.Media.BUCKET_DISPLAY_NAME));
    timestamp = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.MediaColumns.DATE_MODIFIED));
    countPhoto = Function.getCount(getApplicationContext(), album);
    albumOrPhotoList.add(Function.mappingInbox(album, path, timestamp, Function.converToTime(timestamp), countPhoto));
}
cursor.close();
Collections.sort(albumOrPhotoList,new
// Arranging photo album by timestamp decending
MapComparator(Function.KEY_TIMESTAMP, "dsc"));

Query MediaStore on Android Q

1 Answers1