Ordering on multiple fields ( Ascending / Descending )

[stamhaney]

Hi 

I would like to order a view on multiple fields where one field will be in ascending order while the other will be descending. I understand that the descending option is applied to the full set of the results for a query but there is no information on how a multi order sort can be achieved. Can you tell me how can I do this with a sample map/reduce query?

Thanks

Shashank

[Jens Alfke]

On Apr 2, 2015, at 10:31 PM, stamhaney <stam...@gmail.com> wrote:

I would like to order a view on multiple fields where one field will be in ascending order while the other will be descending. I understand that the descending option is applied to the full set of the results for a query but there is no information on how a multi order sort can be achieved. Can you tell me how can I do this with a sample map/reduce query?

In general you can’t. The view creates a single index, and the keys are arranged in order. You can use composite keys (arrays) to get primary and secondary sorts, but they still have to be in the same order.

Now, if the secondary key happens to be a number, you can flip its sign when you emit the key to reverse its sort order. But I can’t think of a similar trick for strings, unfortunately.

On iOS/Mac in version 1.0.4 you can use the CBLQuery’s postFilter property to sort the results in a more general way, so you should be able to do descending secondary keys that way. (Which, by the way, is exactly how a SQL database would do it too, behind the scenes.)

—Jens

[Jeremy Kelley]

One possibility ...

For the sake of argument, let's limit the input of the 2nd index to be ascii letters, a-z only.  So there's a finite 26 letters.

In the snippet below, the var "output" will end up with the value of "zyx" and therefore sort as expected.  An optimized version is left as an exercise to someone with more javascript skills than myself.  ;)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

var letters = 'abcdefghijklmnopqrstuvwxyz'

var input = 'abc'                          

var output = ''

for (var i=0; i< input.length; i++) {      

  var idx_new = letters.indexOf('z') - letters.indexOf(input[i])

  var new_letter = letters[ idx_new ]          

  output += new_letter                         

}

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In the example above, if you "strinvert" the inputs ['aaa', 'bbb', 'ccc'],  our index will up end being  ['zzz', 'yyy', 'xxx']  and therefore you'll get your descending sort by sorting the emitted keys ascending.

good luck,

jeremy

--
You received this message because you are subscribed to the Google Groups "Couchbase Mobile" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mobile-couchba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mobile-couchbase/1B71EB15-94D2-437F-BCFA-BBB36A0F6F89%40couchbase.com.
For more options, visit https://groups.google.com/d/optout.

[Jens Alfke]

That won’t quite work, because a string will still sort after its substrings, e.g. “foobar" still sorts after “foo”, which it shouldn’t in a descending sort.

I think you can fix that by appending a very high character to every string (maybe a “~” in your ASCII-alphabet-only example, or more realistically a very high Unicode character like \uFFFE.)

—Jens