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

Question

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)

Answer 1

GROUP_BY supporting in case of using Bundle:

  val bundle = Bundle().apply {
            putString(
                ContentResolver.QUERY_ARG_SQL_SORT_ORDER,
                "${MediaStore.MediaColumns.DATE_MODIFIED} DESC"
            )
            putString(
                ContentResolver.QUERY_ARG_SQL_GROUP_BY,
                MediaStore.Images.ImageColumns.BUCKET_ID
            )
        }
        contentResolver.query(
            uri,
            arrayOf(
                MediaStore.Images.ImageColumns.BUCKET_ID,
                MediaStore.Images.ImageColumns.BUCKET_DISPLAY_NAME,
                MediaStore.Images.ImageColumns.DATE_TAKEN,
                MediaStore.Images.ImageColumns.DATA
            ),
            bundle,
            null
        )

Answer 2

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

Answer 3

This is a more efficient(not perfect) way to do that.

I am doing it for videos, but doing so is the same for images to. just change MediaStore.Video.Media.X to MediaStore.Images.Media.X

public class QUtils {
        /*created by Nasib June 6, 2020*/
        @RequiresApi(api = Build.VERSION_CODES.Q)
        public static ArrayList<FolderHolder> loadListOfFolders(Context context) {
            ArrayList<FolderHolder> allFolders = new ArrayList<>();//list that we need
            HashMap<Long, String> folders = new HashMap<>();  //hashmap to track(no duplicates) folders by using their ids
    
            String[] projection = {MediaStore.Video.Media._ID,
                    MediaStore.Video.Media.BUCKET_ID,
                    MediaStore.Video.Media.BUCKET_DISPLAY_NAME,
                    MediaStore.Video.Media.DATE_ADDED};
    
            ContentResolver CR = context.getContentResolver();
    
            Uri root = MediaStore.Video.Media.getContentUri(MediaStore.VOLUME_EXTERNAL);
    
            Cursor c = CR.query(root, projection, null, null, MediaStore.Video.Media.DATE_ADDED + " desc");
    
            if (c != null && c.moveToFirst()) {
                int folderIdIndex = c.getColumnIndexOrThrow(MediaStore.Video.Media.BUCKET_ID);
                int folderNameIndex = c.getColumnIndexOrThrow(MediaStore.Video.Media.BUCKET_DISPLAY_NAME);
                int thumbIdIndex = c.getColumnIndexOrThrow(MediaStore.Video.Media._ID);
                int dateAddedIndex = c.getColumnIndexOrThrow(MediaStore.Video.Media.DATE_ADDED);
    
                do {
                    Long folderId = c.getLong(folderIdIndex);

                    if (folders.containsKey(folderId) == false) { //proceed only if the folder data has not been inserted already :) 
                        long thumbId = c.getLong(thumbIdIndex);
                        String folderName = c.getString(folderNameIndex);
                        String dateAdded = c.getString(dateAddedIndex);
                        Uri thumbPath = ContentUris.withAppendedId(MediaStore.Video.Media.EXTERNAL_CONTENT_URI, thumbId);
                        folders.put(folderId, folderName);
                        allFolders.add(new FolderHolder(String.valueOf(thumbPath), folderName, dateAdded));
                    }

                } while (c.moveToNext());
    
                c.close(); //close cursor
                folders.clear(); //clear the hashmap becuase it's no more useful
            }
            return allFolders;
        }
    }

FolderHolder model class

public class FolderHolder {
    private String folderName;
    public long dateAdded;
    private String thumbnailPath;
    public long folderId;

    public void setPath(String thumbnailPath) {
        this.thumbnailPath = thumbnailPath;
    }

    public String getthumbnailPath() {
        return thumbnailPath;
    }

    public FolderHolder(long folderId, String thumbnailPath, String folderName, long dateAdded) {
        this.folderId = folderId;
        this.folderName = folderName;
        this.thumbnailPath = thumbnailPath;
        this.dateAdded = dateAdded;
    }
    public String getFolderName() {
        return folderName;
    }

}