limiting number of rows in a ContentResolver.query() function

Question

Is there a way to limit the number of returned rows to a cursor? I have a phone with about 4000 contacts, I just need some of them.

this is the code i'm using

        db = new dBHelper(this);
        ContentResolver cr = getContentResolver();
        Cursor cursor;

        cursor = cr.query(ContactsContract.Contacts.CONTENT_URI,null, null, null, ContactName + " ASC");
        Log.i(TAG, CLASSNAME + " got contacts entries");
        for (int it = 0; it <100 ; it++){//cursor.getCount()
            Log.i(TAG, CLASSNAME + " getting string");
            String mytimes_contacted = cursor.getString(cursor.getColumnIndex(dBHelper.times_contacted)); 
            Log.i(TAG, CLASSNAME + " done from the string");
        }

the Log i'm getting is

I/Check(11506): [ContactsPicker] got contacts entries
I/Check(11506): [ContactsPicker] getting first string
D/AndroidRuntime(11506): Shutting down VM
W/dalvikvm(11506): threadid=1: thread exiting with uncaught exception (group=0x2aac8578)
D/dalvikvm(11541): GC_CONCURRENT freed 923K, 46% free 4000K/7303K, external 1685K/2133K, paused 1ms+8ms
E/AndroidRuntime(11506): FATAL EXCEPTION: main
E/AndroidRuntime(11506): java.lang.RuntimeException: Unable to start activity ComponentInfo{~~my package name~~}: android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 3537

Answer 1

In order to sort descending by "date" column and limit to one result, I had to do the following. They have zero documentation about it. Bundle seems to be a key value collection. Everything I do has to be guesses or dig into their code and use assistance from Stack Overflow.

   val bundle = Bundle().apply {
            putInt(android.content.ContentResolver.QUERY_ARG_LIMIT, 1)
            putInt(android.content.ContentResolver.QUERY_ARG_SQL_SORT_ORDER , android.content.ContentResolver.QUERY_SORT_DIRECTION_DESCENDING)
            putStringArray(
                android.content.ContentResolver.QUERY_ARG_SORT_COLUMNS,
                arrayOf("date")
            )
        }

        val logs = context.contentResolver.query(
            CallLog.Calls.CONTENT_URI,
            strFields,
            bundle, null)

Old code:

  val logs = context.contentResolver.query(
                CallLog.Calls.CONTENT_URI,
                strFields,
                null,
                null,
                "date DESC limit 1;"
            )

Answer 2

My solution is more like manual. I just passed the optional limit argument and modify the while loop condition and it worked for me. No need to worry about Android version. In my app I am loading first 20 track then loading all tracks so user no need to wait to whole list to be fetched.

You can apply same thing for images, videos etc.

suspend fun getTracksFromMusicFolder(limit: Int? = null): ArrayList<Track> {
    return withContext(Dispatchers.IO) {
        var limitVal = 0
        val result = arrayListOf<Track>()

        val contentResolver = appContext.get()?.contentResolver
        val projection = arrayOf(
            MediaStore.Audio.Media._ID,
            MediaStore.Audio.Media.TITLE,
            MediaStore.Audio.Media.ARTIST,
            MediaStore.Audio.Media.ALBUM,
            MediaStore.Audio.Media.MIME_TYPE,
            MediaStore.Audio.Media.DATE_MODIFIED,
            MediaStore.Audio.Media.DATA,
            MediaStore.Audio.Media.DURATION
        )
        val sortOrder = "${MediaStore.Audio.Media.TITLE} ASC"
        val selection = MediaStore.Audio.Media.IS_MUSIC + " != 0"


        contentResolver?.query(
            MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
            projection,
            selection,
            null,
            sortOrder
        ).use {
            it?.let { cursor ->
                val idColumn = cursor.getColumnIndexOrThrow(MediaStore.Audio.Media._ID)
                val titleColumn = cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.TITLE)
                val artistColumn = cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.ARTIST)
                val albumColumn = cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.ALBUM)
                val durationColumn = cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.DURATION)
                val dateModifiedColumn =
                    cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.DATE_MODIFIED)

                while (cursor.moveToNext() && limitVal != limit) {
                    val id = cursor.getLong(idColumn)
                    val title = cursor.getString(titleColumn).trimStart()
                    val artist = cursor.getString(artistColumn).trimStart()
                    val album = cursor.getString(albumColumn).trimStart()
                    val duration = cursor.getLong(durationColumn)
                    val dateModified = cursor.getLong(dateModifiedColumn)
                    val path =
                        ContentUris.withAppendedId(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, id)
                    val pathExtension = getFileMimeType(path)

                    val track = Track(
                            id,
                            title,
                            artist,
                            album,
                            path,
                            duration,
                            pathExtension,
                            dateAdded = dateModified
                        )

                    result.add(track)
                    limitVal += 1
                    
                }
            }
        }

        result
    }
}

Example Usage

val firstTwentyTrack = getTracksFromMusicFolder(20)
val allTracks = getTracksFromMusicFolder(null)

Answer 3

From Android 11, that above solution will not work, you can try this one to fetch the data.

    /**
 * Call to fetch all media on device, it but be called synchronously since function is called on a background thread
 */
private fun fetchGalleryImages(
    context: Context,
    offset: Int,
    limit: Int
): List<MediaItem> {
    val galleryImageUrls = mutableListOf<MediaItem>()
    try {
        if (EasyPermissions.hasPermissions(
                context,
                Manifest.permission.WRITE_EXTERNAL_STORAGE
            )
        ) {
            // Define the columns that will be fetched
            val projection = arrayOf(
                MediaStore.Files.FileColumns._ID,
                MediaStore.Files.FileColumns.DATA,
                MediaStore.Files.FileColumns.DATE_ADDED,
                MediaStore.Files.FileColumns.MEDIA_TYPE,
                MediaStore.Files.FileColumns.MIME_TYPE,
                MediaStore.Files.FileColumns.TITLE,
                MediaStore.Video.Media.DURATION
            )
            val selection =
                "${MediaStore.Files.FileColumns.MEDIA_TYPE} = ? OR ${MediaStore.Files.FileColumns.MEDIA_TYPE} = ?"
            val selectionArgs = arrayOf(
                MediaStore.Files.FileColumns.MEDIA_TYPE_IMAGE.toString(),
                MediaStore.Files.FileColumns.MEDIA_TYPE_VIDEO.toString()
            )
            /**
             * Change the way to fetch Media Store
             */
            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.Q) {
                // Get All data in Cursor by sorting in DESC order
                context.contentResolver.query(
                    contentUri(),
                    projection,
                    Bundle().apply {
                        // Limit & Offset
                        putInt(ContentResolver.QUERY_ARG_LIMIT, limit)
                        putInt(ContentResolver.QUERY_ARG_OFFSET, offset)
                        // Sort function
                        putStringArray(     // <-- This should be an array. I spent a whole day trying to figure out what I was doing wrong
            ContentResolver.QUERY_ARG_SORT_COLUMNS,
            arrayOf(MediaStore.Files.FileColumns.DATE_MODIFIED)
        )

                        putInt(
                            ContentResolver.QUERY_ARG_SORT_DIRECTION,
                            ContentResolver.QUERY_SORT_DIRECTION_DESCENDING
                        )
                        // Selection
                        putString(ContentResolver.QUERY_ARG_SQL_SELECTION, selection)
                        putStringArray(
                            ContentResolver.QUERY_ARG_SQL_SELECTION_ARGS,
                            selectionArgs
                        )
                    }, null
                )
            } else {
                val sortOrder =
                    "${MediaStore.Files.FileColumns.DATE_MODIFIED} DESC LIMIT $limit OFFSET $offset"
                // Get All data in Cursor by sorting in DESC order
                context.contentResolver.query(
                    contentUri(),
                    projection,
                    selection,
                    selectionArgs,
                    sortOrder
                )
            }?.use { cursor ->
                while (cursor.moveToNext()) {
                    galleryImageUrls.add(
                        MediaItem(
                            cursor.getLong(cursor.getColumnIndex(MediaStore.Files.FileColumns._ID)),
                            ContentUris.withAppendedId(
                                contentUri(),
                                cursor.getLong(cursor.getColumnIndex(MediaStore.Files.FileColumns._ID))
                            ),
                            cursor.getString(cursor.getColumnIndex(MediaStore.Files.FileColumns.DATA)),
                            cursor.getStringOrNull(cursor.getColumnIndex(MediaStore.Files.FileColumns.MIME_TYPE)),
                            cursor.getLongOrNull(cursor.getColumnIndex(MediaStore.Video.Media.DURATION))
                        )
                    )
                }
            }
        }
    } catch (ex: Exception) {
        ex.printStackTrace()
    }
    return galleryImageUrls
}

Answer 4

If anyone is looking for Java version of the above Ignacio Tomas Crespo's answer ,

        if (Build.VERSION.SDK_INT < Build.VERSION_CODES.R) {

            cursor = context.getContentResolver().query(
                    MediaStore.Images.Media.EXTERNAL_CONTENT_URI
                            .buildUpon()
                            .encodedQuery("limit=" + offSet + "," + "100")
                            .build(),
                    columns,
                    null,
                    null,
                    null);
        } else {
            Bundle bundle = new Bundle();
            bundle.putInt(ContentResolver.QUERY_ARG_LIMIT, 100);

            cursor = context.getContentResolver()
                    .query(MediaStore.Images.Media.EXTERNAL_CONTENT_URI,
                            columns,
                            bundle,
                            null);
        }

Answer 5

In android 26 query method is upgraded. This function is using these arguments. Uri uri, String[] projection, Bundle queryArgs, CancellationSignal cancellationSignal

Below example I'm getting recent 5 pictures.

    val whereArgs = arrayOf("image/jpeg", "image/png", "image/jpg")

    val projection = arrayOf(MediaStore.Images.ImageColumns._ID,
            MediaStore.Images.ImageColumns.DATA,
            MediaStore.Images.ImageColumns.BUCKET_DISPLAY_NAME,
            MediaStore.Images.ImageColumns.DATE_TAKEN,
            MediaStore.Images.ImageColumns.MIME_TYPE)


    val selection =
            "${MediaStore.Files.FileColumns.MIME_TYPE} = ? OR ${MediaStore.Files.FileColumns.MIME_TYPE} = ?  OR ${MediaStore.Files.FileColumns.MIME_TYPE} = ?"


    val queryArgs = Bundle()
    val sortArgs = arrayOf(MediaStore.Images.ImageColumns.DATE_TAKEN)

    queryArgs.putStringArray(ContentResolver.QUERY_ARG_SORT_COLUMNS, sortArgs)
    queryArgs.putInt(ContentResolver.QUERY_ARG_SORT_DIRECTION, ContentResolver.QUERY_SORT_DIRECTION_DESCENDING)
    queryArgs.putInt(ContentResolver.QUERY_ARG_LIMIT, 5)
    queryArgs.putString(ContentResolver.QUERY_ARG_SQL_SELECTION, selection)
    queryArgs.putStringArray(ContentResolver.QUERY_ARG_SQL_SELECTION_ARGS, whereArgs)

    val cursor = context!!.contentResolver.query(MediaStore.Images.Media.EXTERNAL_CONTENT_URI,
            projection,
            queryArgs,
            null)


    if (cursor!!.moveToFirst()) {
        do {
            val imageLocation = cursor.getString(1)
            val imageFile = File(imageLocation)

            if (imageFile.exists()) {
              //access you file from imageLocation
            }
        } while (cursor.moveToNext())
        fiveRecentlyImagesAdapter!!.notifyDataSetChanged()
    }

Answer 6

The accepted answer is not valid anymore for android 11. In android 11 a constraint was added to not allow using LIMIT in sort value. You need to use the query with bundle parameters. For instance:

        val bundle = Bundle().apply {
            putInt(ContentResolver.QUERY_ARG_LIMIT, 100)
        }
        resolver.query(
                ContactsContract.Contacts.CONTENT_URI,
                projection,
                bundle,
                null
        )

Answer 7

To limit the number of results in your cursor try:

cursor = cr.query(ContactsContract.Contacts.CONTENT_URI,null, null, null, ContactName + " LIMIT 100");
while(cursor.moveToNext()) {
    // something clever
}