How to order by multiple date time properties with nulls last?

Question

I have a json and I want to order this json by multiple date time properties. But there is a pinnedAt property which is pointing that post has to be on top. Im using lodash by the way.

This is the sql document of what I am trying to explain:

If NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified (thus, the default is to act as though nulls are larger than non-nulls). When USING is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.

You can read more here: https://www.postgresql.org/docs/9.0/static/sql-select.html

---

So here is my sample data:

{
  "data": [
    {
      "name": "Paris",
      "createdAt": "2018-10-01T08:28:05.074Z",
      "pinnedAt": null
    },
    {
      "name": "New York",
      "createdAt": "2018-10-01T05:16:05.074Z",
      "pinnedAt": null
    },
     {
      "name": "Washington",
      "createdAt": "2018-10-02T08:28:05.074Z",
      "pinnedAt": "2018-10-02T15:19:23.245Z"
    }
  ]
}

My code to order

posts = _.orderBy(state.posts, ['pinnedAt', 'createdAt'], ['desc', 'desc']);

But this doesn't order like what I want. Here is what I expected

{
  "data": [
    {
      "name": "Washington",
      "createdAt": "2018-10-02T08:28:05.074Z",
      "pinnedAt": "2018-10-02T15:19:23.245Z"
    },
    {
      "name": "Paris",
      "createdAt": "2018-10-01T08:28:05.074Z",
      "pinnedAt": null
    },
    {
      "name": "New York",
      "createdAt": "2018-10-01T05:16:05.074Z",
      "pinnedAt": null
    }
  ]
}

How can I do that?

Thank you.

Answer 1

You can use a custom function to treat null as a different value that will sort correctly.

const data = [
    {
      "name": "Paris",
      "createdAt": "2018-10-01T08:28:05.074Z",
      "pinnedAt": null
    },
    {
      "name": "New York",
      "createdAt": "2018-10-01T05:16:05.074Z",
      "pinnedAt": null
    },
    {
      "name": "Washington",
      "createdAt": "2018-10-02T08:28:05.074Z",
      "pinnedAt": "2018-10-02T15:19:23.245Z"
    }
]; 

const result = _.orderBy(data, 
                         [(item) => item.pinnedAt ? item.pinnedAt : "", 'createdAt'], 
                         ['desc', 'desc']);

Tested using version 4.17.11 of lodash using https://npm.runkit.com/lodash

---

This works because the empty string is "less than" any other string value. When sorting descending, this will always show up at the end of the list. Because these empty string values are equivalent, objects without pinnedAt will be sorted based on createdAt, as expected.