Weirdness using sorter.sort for custom object list.

[Digital_Pi22]

I have a velocity script as follows:

#set ($rawList = $CstPolicyList)
#set ($sortedList = $sorter.sort($rawList,"policySaleDate:desc")) 
Here is Sorted List: 
#if($sortedList.size() > 0)
  <table>
  <tr><th>ID</th><th>Sale Date</th></th></tr>
  #foreach($policy in $sortedList)
  <tr>
    <td> $policy.policyId</td> 
    <td>$!policy.policySaleDate </td>
    </tr>
  #end
  </table>
#else
  Sorted list is empty! <br>
#end
---------
Here is Raw List: 
#if($rawList.size() > 0)
  <table>
  <tr><th>ID</th><th>Sale Date</th></th></tr>
  #foreach($policy in $rawList)
  <tr>
    <td> $policy.policyId</td> 
    <td>$!policy.policySaleDate </td>
   </tr>
  #end
  </table>
#end

policySaleDate is a Marketo Date field.  It may be empty.

When I run it, I get output for the second, "rawList".  But nothing for the sorted list (size is 0).

Even weirder, if I change the sort column to use updatedAt:

 $sorter.sort($rawList,"updatedAt:desc")) 

Or to

 $sorter.sort($rawList,"updatedAt:asc")) 

$sorter.sort($rawList,"updatedAt:desc"))

Then I get Identical output for both the sortedList and the rawList.  The "asc" and "desc" do not make a difference.

Why would the sort not return anything if I reference the "policySaleDate" field?  I know it is the correct name (and I have the check box checked in the list)

because the data shows up in the email for the $rawList

Message was edited by: Digital Pi fixed some typos

[Digital_Pi22]

I experimented sorting by the policyId (text field):

#set ($sortedList = $sorter.sort($rawList,"policyId:desc"))

This works fine, it returns a sortedList to $sortedList as expected.

The issue seems to be with the custom object policySalesDate  date field.  $sorter.sort does not like it for some reason.

[SanfordWhiteman]

It's not really weird! You can't mix types with a generic SortTool.sort, and comparing a null with a String value, or a Boolean with a String, or a Number with a Boolean, are all mixed types.  There isn't a comparator function to derive a useful result from such comparisons, so the sort will error out and you will get no result.

In Java at large you can build custom comparators for different types, so you ensure there's a result.  But in a locked-down Velocity scenario you can't do this. What you can do is iterate over the list and fill in any null with an empty-like value of the same type. Unfortunately null is the best non-value, and you can't use that or you're back where you started.

[Digital_Pi22]

Did some more testing.  There is another date field on the custom object, ("policyEffectiveDate") which is never empty. 

I tried sorting on that and all works fine. 

Then, I found some leads that had values for all of the the "policySalesDate" fields.   Again, this worked fine.

So my conclusion is the that sorter.sort() function does not work well when sorting  custom object lists with date fields that have empty values.  This is unfortunate, mysterious behavior.

[SanfordWhiteman]

Did you not read my response?

[Digital_Pi22]

Thanks Sanford.  I Just read your reply. I understand what you are saying, but think that it would be much nicer  to handle this the way SQL handles it, with a default sort order for NULL values or allowing you to declare the sort order for NULL.  Short of that, to silently fail and return *nothing* seems completely weird to me, especially when considering the near lack of documentation  of Velocity scripting in Marketo environments.  ( Your blog seems to be the only authoritative source for that!)

[SanfordWhiteman]

Well... for the reason you allude to, SQL is probably not the best example of null sorting, as the order is not defined. A standards-compliant SQL engine could, strange as it would be, apply a random sort order to NULLs so they're interleaved with other values! And some engines don't let you choose between nulls-first and nulls-last so you're at the mercy of their arbitrary implementation. (And technically-technically, in implementations that only use the system order, it's not exactly that NULL is sorted to the top or bottom, it's that the result of a comparison with NULL is sorted to the top or bottom, e.g. the unknown comparison result isn't the same as NULL itself.)

A cleaner example might be a language that coerces null, in its default sort implementation, to a single, deterministic value. In JavaScript, null becomes the string "null" for the purposes of Array.sort(). So that may not be what you want in a given scenario, but it's what you can predictably get, and you won't get a fatal runtime error -- though you may wish you'd gotten one!

At any rate, yes, this stuff is hard to find documentation for! The main reason is that Velocity is a strongly typed language masquerading as a mere scripting dialect. So it's expected that a person pushing Velocity to the fullest will find it a techie's dream (and I do kind of love it, with reservations) relative to basic scripting languages. But in reality that can be really intimidating, because the documentation seems built for people who are already Java pros. Take the SortTool doc. It says:

Each property which is to be sorted on must return one of the follow[ing]:

• Primitive type: e.g. int, char, long etc
• Standard Object: e.g. String, Integer, Long etc
• Object which implements the Comparable interface.

The sort is performed by calling Collections.sort()...

Even aside from the mention of the Comparable interface (which only is implementable if you control the objects coming in, which we don't in Marketo) this leaves a lot unspoken. What it doesn't specifically say, and which you're supposed to just kinda know, is:

• Primitives and standard boxes are always comparable with other objects of the same type but not with objects of a different type. So what it should say is "Each property must have the same type on every object in the list." This isn't a worry with Marketo or SFDC objects, but in general you can easily create 2 objects, one where the property "myProperty" is a String, and another where the property "myProperty" is an Integer, and if those objects are in the same list you can't sort on that property.  Granted it would be poor design to have objects with different shapes like that.
• null is not a type of object in Java, hence not in Velocity.  So null does not satisfy the "must" in the above quote. But... it's not actually true that you can't use $SortTool.sort() when there are nulls! It's that you can't sort when there are nulls and the type that is being compared to null doesn't have a special compareTo () method that allows it to be compared to null!  Primitives and standard types don't have such a method. But you could craft an object that does, which would prove the documentation wrong (i.e. the doc doesn't accurately reflect the Velocity source code, which yes, I've spent far too much time looking at). However if you take the docs at face value, they do say the property can't be null.
• (in the interest of obsessive detail) The property doesn't actually have to exist on every object in the list, as long as it is null wherever it appears. This is counter to the otherwise-true rule that the property needs to be on every object or else the whole  sort fails. Of course it would be another example of poor design if you ended with misshapen objects like this.

Aaaanyway, if you want to sort on a String property that may have a null value, you can loop over it and set all the nulls to an empty String, then sort:

#foreach( $itm in $myList )
#set( $itm.nullableStringProp = $display.alt($itm.nullableStringProp, "") )
#end
#set( $safeSortedList = $sorter.sort( $myList, "nullableStringProp" ) )

Similarly, if it's an Integer prop, you can set it to the lowest possible Integer:

#foreach( $itm in $myList )
#set( $itm.nullableIntegerProp = $display.alt($itm.nullableIntegerProp, $field.in(0).MIN_VALUE) )
#end
#set( $safeSortedList = $sorter.sort( $myList, "nullableIntegerProp" ) )

Each of these will sort the formerly null values first in ascending order.

This is relatively uncharted territory and I will definitely be adding a blog post on it.