Querying Android Content Provider

Question

I am trying to query my Content Provider for this data

 scheduledTime='1457093280', user='null', isCompleted='false', id='18'
 scheduledTime='1457266080', user='null', isCompleted='false', id='19'
 scheduledTime='1457352540', user='null', isCompleted='false', id='20'
 scheduledTime='1459155180', user='null', isCompleted='false', id='6'
 scheduledTime='1456391940', user='null', isCompleted='true', id='2'
 scheduledTime='1456394140', user='null', isCompleted='true', id='1'
 scheduledTime='1456395600', user='null', isCompleted='true', id='3'
 scheduledTime='1456395600', user='null', isCompleted='true', id='4'
 scheduledTime='1456399260', user='null', isCompleted='true', id='5'
 scheduledTime='1456918140', user='null', isCompleted='true', id='14'
 scheduledTime='1456918320', user='null', isCompleted='true', id='15'
 scheduledTime='1456920360', user='null', isCompleted='true', id='16'
 scheduledTime='1456921020', user='null', isCompleted='true', id='21'

And this is how I'm querying it.

 final Cursor cursor = getContentResolver().query(uri, null, null, null, "isCompleted ASC, scheduledTime ASC");

Which prints out the data above. But, this is what I want to achieve. Sort isCompleted in Ascending order and then sort by time. Which means false Completed would be sorted by the one closest to the resent time and and true completed would be sorted by the one closest to the resent time too. More like, 18, 19, 20, 21, 16, 15, 14, 5, 4, 3, 1, 2 18, 19, 20 looks well sorted, but 21, 16, 15, 14, 5, 4, 3, 1, 2. More like, sorting false in ASC order and true in DESC. Any help would be appreciated. Thanks

Answer 1

I see two possible solutions.

Solution 1

If you know a pivot time that lies between the complete and incomplete entries you sort by the absolute distance between your pivot time.

Assuming that your pivot time is stored in a long variable called pivotTime the query would look like so:

final Cursor cursor = getContentResolver().query(uri, null, null, null,
    String.format("isCompleted ASC, abs(scheduledTime - %d) ASC", pivotTime));

Explanation

For all entries with a scheduledTime that is after pivotTime the expression scheduleTime - pivotTime will be positive and the absolute value of it will be positive as well. In effect the sorting order is the same as before.

For entries with a scheduledTime that is before pivotTime the expression scheduleTime - pivotTime will become negative. Applying abs() will turn them into positive, inverting the sort order.

Solution 2

Solution #2 is sort of a hack, but it also works if the condition that you can find a time between complete or incomplete entries is not met (or if the times may overlap).

The following query should work in that case.

final Cursor cursor = getContentResolver().query(uri, null, null, null,
    "isCompleted ASC, ((isCompleted == 'false') - 0.5) * scheduledTime ASC");

Explanation

since isCompleted == 'false' is evaluated to 1 for incomplete entries the expression (isCompleted == 'false') - 0.5 results in 0.5. So incomplete entries will be sorted by 0.5 * scheduledTime which equals the original sorting.

For completed entries isCompleted == 'false' evaluates to 0 and (isCompleted == 'false') - 0.5 result in -0.5, effectively reverting the sort order.