How to sort the CursorLoader results?

Question

I use CursorLoader to query a result, which is not the order that I want to show in the ListFramgenet. How to sort it ?

I use this to set the adapter:

    mAdapter = new SimpleCursorAdapter(getActivity(),
            android.R.layout.simple_list_item_2, null,
            new String[] { "name", "distance"},
            new int[] { android.R.id.text1, android.R.id.text2 }, 0);
    setListAdapter(mAdapter);

    // Start out with a progress indicator.
    setListShown(false);

    // Prepare the loader.  Either re-connect with an existing one,
    // or start a new one.
    getLoaderManager().initLoader(0, null,  this);

Create loader :

public Loader<Cursor> onCreateLoader(int id, Bundle args) {

    return new CursorLoader(getActivity(), 
            Uri.withAppendedPath(TraceTable.CONTENT_URI, "latest"),
            MEMBERS_PROJECTION,
            null,
            null,
            null);        

}


public void onLoadFinished(Loader<Cursor> loader, Cursor data) {
    mAdapter.changeCursor(data);

    // The list should now be shown.
    if (isResumed()) {
        setListShown(true);
    } else {
        setListShownNoAnimation(true);
    }
}

Well, there are latitude and longitude the queried results. I want to calculate the distance between my location and these results. and sort by distance asc.

How to sort it? Any answer will be appricated

you can not sort Cursor that was already returned ... you have to tell CursorLoader to sort it for you when it is created CursorLoader(Context context, Uri uri, String[] projection, String selection, String[] selectionArgs, String **sortOrder**) — Selvin, Dec 19 '13 at 16:12
It seems sortOrder is just a string, the final order I want need to calculate. — Alex Chan, Dec 19 '13 at 16:20
you can order by `(x-x0)^2 + (y-y0)^2` ... you can also return it `(x-x0)^2 + (y-y0)^2 AS Distance²`(of course, you have to modify ContentProvider) ... then before showing you can calculate it in Binder.bindView() or getView() or similar ... `someView.setText(Math.sqrt(c.getDouble(c.getColumnIndex("Distance²"))).toString())` — Selvin, Dec 19 '13 at 16:31
apparently you need to read some SQLite documentation : http://www.sqlite.org/syntaxdiagrams.html#ordering-term — njzk2, Dec 19 '13 at 16:34

score 13 · Accepted Answer · answered Jan 01 '14 at 18:56

It's actually quite easy:

from this:

new CursorLoader(getActivity(), 
        Uri.withAppendedPath(TraceTable.CONTENT_URI, "latest"),
        MEMBERS_PROJECTION,
        null,
        null,
        null);

to this:

// You could have them calculated in the projection like this:
String[] projection = { COLUMN1 + " * " + COLUMN2 + " as data", // Whatever your calculations are
COLUMN1, COLUMN2, COLUMN3, etc..  };
new CursorLoader(getActivity(), 
        Uri.withAppendedPath(TraceTable.CONTENT_URI, "latest"),
        projection, 
        null,
        null,
        "data ASC");

Remember that if you have some method in your provider that does a check to the projection and rise an exception, you would have to comment it out for the moment you are doing the test or add the new column (the one you do the calculation with) to your official projection array.

Although I used another way to avoid this problem. I think your answer will be better. thank you. — Alex Chan, Jan 05 '14 at 12:00

NigelK · Answer 2 · 2013-12-19T16:36:44.663

1

Provide the column names you want to sort on as the last parameter of the CursorLoader() constructor as a string. If you have more than one column to sort by, separate with a comma. If you want ascending as opposed to descending, add DESC after the column name. So pretty much what you would add after 'ORDER BY' in regular SQL syntax.

EDIT: To answer your comment below.

Yes and no. I believe you could use that as your sort order but SQLite does not have a sqrt or power function. However you can define your own SQLite functions or use a 3rd party extension. If you don't want to go that route, you'll have to use your own custom adapter instead of SimpleCursorAdapter, e.g. you would get the cursor results and then sort them in code into another data stucture which would become the datasource of your adapter.

edited Dec 19 '13 at 16:36

answered Dec 19 '13 at 16:18

NigelK

8,255
2
30
28

two column in my table: x, y . the order I want is sqrt( (x-x0)^2 + (y-y0)^2 ). Can it be sort directly in SQL? – Alex Chan Dec 19 '13 at 16:22
nota : you can drop the sqrt, as it is a growing monotonic function. – njzk2 Dec 19 '13 at 16:36
Thank you, I'd like to try to implement my own adapter, It seems easier than defining my own SQLite function. – Alex Chan Dec 19 '13 at 16:40

score 1 · Answer 3 · edited May 23 '17 at 11:46

1

What you're looking for is supplying a sortOrder argument something like the following:

(the ORDER BY at the start of this is implicait and you do not include it, simply included here for clarity)

ORDER BY 6366000*acos(cos(lat_a / (180/3.14169))*cos(lng_a / (180/3.14169))*cos(lat_b / (180/3.14169))*cos(lng_b / (180/3.14169)) + t2 + t3) ASC

(for this I took the answer here and inlined it - except I didn't bother with t2 and t3, because it's not going to work anyway)

Unfortunately, this is impossible in standard SQLite - there are no operators sin, or cos - or even a square root or power operator (after seeing your comment about only requiring a more simple calculation).

You can add your own functions, but that's a somewhat more complicated route.

Depending on how many rows you have, you might be alright to just read them all and then sort them yourself.

edited May 23 '17 at 11:46

Community

1
1

answered Dec 19 '13 at 16:34

Adam S

16,144
6
54
81

Your sort order is what I want, I just use sqrt(^2) to simplify the question. – Alex Chan Dec 19 '13 at 16:41
Aha. Ok, then you could either look into adding your own functions, or perhaps perform calculations across the results and populate a temporary 'distance' table (with your main table's primary key and the distance from current location), join on that, and then order by distance. You still have to do calculations in code, though, and iterate over all the results - though all you need from the results is the lat & long values, which is probably cheaper than creating a whole object representing each row. – Adam S Dec 19 '13 at 16:47

score 0 · Answer 4 · edited Jan 16 '18 at 06:34

0

Just add ContactsContract.Contacts.SORT_KEY_PRIMARY.

edited Jan 16 '18 at 06:34

Pang

9,564
146
81
122

answered Jan 16 '18 at 06:14

Saeef Minhaz

71
2
11

How to sort the CursorLoader results?

4 Answers4

Linked