Replacement for "GROUP BY" in ContentResolver query in Android Q ( Android 10, API 29 changes)

I'm upgrading some legacy to target Android Q, and of course this code stop working:

       String[] PROJECTION_BUCKET = {MediaStore.Images.ImageColumns.BUCKET_ID,
            MediaStore.Images.ImageColumns.BUCKET_DISPLAY_NAME,
            MediaStore.Images.ImageColumns.DATE_TAKEN,
            MediaStore.Images.ImageColumns.DATA,
            "COUNT(" + MediaStore.Images.ImageColumns._ID + ") AS COUNT",
            MediaStore.Files.FileColumns.MEDIA_TYPE,
            MediaStore.MediaColumns._ID};

        String BUCKET_GROUP_BY = " 1) and " + BUCKET_WHERE.toString() + " GROUP BY 1,(2";

        cur = context.getContentResolver().query(images, PROJECTION_BUCKET,
            BUCKET_GROUP_BY, null, BUCKET_ORDER_BY);

android.database.sqlite.SQLiteException: near "GROUP": syntax error (code 1 SQLITE_ERROR[1])

Here it supposed to obtain list of images with album name, date, count of pictures - one image for each album, so we can create album picker screen without querying all pictures and loop through it to create albums.

Is it possible to group query results with contentResolver since SQL queries stoped work?

(I know that ImageColumns.DATA and "COUNT() AS COUNT" are deprecated too, but this is a question about GROUP BY)

(There is a way to query albums and separately query photo, to obtain photo uri for album cover, but i want to avoid overheads)

Unfortunately Group By is no longer supported in Android 10 and above, neither any aggregated functions such as COUNT. This is by design and there is no workaround.

The solution is what you are actually trying to avoid, which is to query, iterate, and get metrics.

To get you started you can use the next snipped, which will resolve the buckets (albums), and the amount of records in each one.

I haven't added code to resolve the thumbnails, but is easy. You must perform a query for each bucket Id from all the Album instances, and use the image from the first record.

public final class AlbumQuery
{
    @NonNull
    public static HashMap<String, AlbumQuery.Album> get(@NonNull final Context context)
    {
        final HashMap<String, AlbumQuery.Album> output     = new HashMap<>();
        final Uri                               contentUri = MediaStore.Images.Media.EXTERNAL_CONTENT_URI;

        final String[] projection = {MediaStore.Images.Media.BUCKET_DISPLAY_NAME, MediaStore.Images.Media.BUCKET_ID};

        try (final Cursor cursor = context.getContentResolver().query(contentUri, projection, null, null, null))
        {
            if ((cursor != null) && (cursor.moveToFirst() == true))
            {
                final int columnBucketName = cursor.getColumnIndexOrThrow(MediaStore.Images.Media.BUCKET_DISPLAY_NAME);
                final int columnBucketId   = cursor.getColumnIndexOrThrow(MediaStore.Images.Media.BUCKET_ID);

                do
                {
                    final String bucketId   = cursor.getString(columnBucketId);
                    final String bucketName = cursor.getString(columnBucketName);

                    if (output.containsKey(bucketId) == false)
                    {
                        final int              count = AlbumQuery.getCount(context, contentUri, bucketId);
                        final AlbumQuery.Album album = new AlbumQuery.Album(bucketId, bucketName, count);
                        output.put(bucketId, album);
                    }

                } while (cursor.moveToNext());
            }
        }

        return output;
    }

    private static int getCount(@NonNull final Context context, @NonNull final Uri contentUri, @NonNull final String bucketId)
    {
        try (final Cursor cursor = context.getContentResolver().query(contentUri,
                null, MediaStore.Images.Media.BUCKET_ID + "=?", new String[]{bucketId}, null))
        {
            return ((cursor == null) || (cursor.moveToFirst() == false)) ? 0 : cursor.getCount();
        }
    }

    public static final class Album
    {
        @NonNull
        public final String buckedId;
        @NonNull
        public final String bucketName;
        public final int    count;

        Album(@NonNull final String bucketId, @NonNull final String bucketName, final int count)
        {
            this.buckedId = bucketId;
            this.bucketName = bucketName;
            this.count = count;
        }
    }
}