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

Sam · Accepted Answer · 2012-04-30T21:26:35.607

44

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
}

edited Apr 30 '12 at 21:26

answered Apr 30 '12 at 21:20

Sam

86,580
20
181
179

Thanks Sam, I totally forgot about moving the cursor, even within the loop, How stupid! I'll try your advice and come back to you – user1347945 Apr 30 '12 at 21:25
Updated with a limit example, but it's limited to the first 100 results. The real question is _how_ do you want to limit the cursor? Only people named "Bob"? – Sam Apr 30 '12 at 21:28
ummmm, the mistake was really moving the cursor around, but good thing that I've learnt now how to limit the cursor. The question was, how to I limit the cursor size so I can get limited number of rows even if the database had more than the number I've specified... – user1347945 Apr 30 '12 at 21:44
19

Note that this will not work with all `ContentProvider`s. It assumes a particular implementation (SQLite) and assumes that the `ContentProvider` will just pass the values along to SQLite. That will not be the case all of the time. – CommonsWare Apr 30 '12 at 21:54
Agree with CommonsWare, this is a hackerish solution. In lots of situations you can use a *selection* to minimize the returned results. Try this first – George Pligoropoulos Mar 11 '14 at 10:00
1

we have an LG-G3 device that implemented `ContentProvider` in a way that doesn't support the limit clause suggested here and ends the query like this: `ORDER BY LIMIT 50` – thepoosh Dec 30 '15 at 13:12
This works for SMS provider `content://sms/inbox` too. – v.ladynev Jul 27 '16 at 13:36
This solution is valid only if u need 4000 (ContactName *order by name asc*), if u need first 4000(inserted order) then it won't work because it will give the sorted data based on ContactName – Vishal Pandey Feb 08 '18 at 06:40
@GeorgiosPligoropoulos is there any way to limit number of rows by their index? I can limit using any column name, but thats not suitable for me. I need to sort all rows first, then get first 10 rows (next Time another 10 rows). Is this possible using ContentResolver? – artman Sep 25 '19 at 14:31

score 22 · Answer 2 · edited Dec 03 '21 at 03:55

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
}

Thanks, I was getting some unexpected "Invalid token LIMIT" on an emulator because of this — JaviCasa, Sep 11 '20 at 08:01
You need to use putStringArray(ContentResolver.QUERY_ARG_SORT_COLUMNS, new String[] { MediaStore.Files.FileColumns.DATE_MODIFIED }) otherwise sorting will be incorrect — artman, Nov 04 '20 at 14:34

score 16 · Answer 3 · answered Jul 14 '20 at 09:20

16

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
        )

answered Jul 14 '20 at 09:20

Ignacio Tomas Crespo

3,401
1
20
13

1

I wasn't able to find more info/ docs about this. I was also using the normal query path (to search specific paths only). – Ben Butterworth Nov 09 '20 at 12:23

score 3 · Answer 4 · answered Mar 17 '21 at 12:34

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()
    }

score 1 · Answer 5 · answered Apr 19 '21 at 12:03

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

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

5 Answers5

Linked