Sorting collections by a date field (e.g., YYYY-MM-DD) does not give expected results.

[erichgoldman]

I am trying to sort a collection based on a date field, which is defined in YYYY-MM-DD format. The sorting works on regular text fields, but it is acting odd on these date fields. The date fields are simply string so I would not expect special behavior.

e..g, given:

May 4, 2016 ==> 2016-05-04
Jan 1, 2016 ==> 2016-01-01

We would expect Jan 1 to be sorted so it is before May 4.

---

Here is my blueprint at: [siteroot]/user/blueprints/pages/event.yaml

title: Event

form:
  fields:
    tabs:
      type: tabs
      active: 1

      fields:
        event:
          type: tab
          title: Event Details

          fields:
            header.title:
              type: text
              label: Enter the event title
              default: 'Title'
              validate:
                required: true

            header.startdate:
              type: date
              label: Enter the start date
              validate:
                required: true

            header.enddate:
              type: date
              label: Enter the end date
              validate:
                required: true

            header.description:
              type: textarea
              label: Enter the event description
              default:
              validate:
                required: true

Which works correctly in the admin:

---

I then created a template to show the list of events at: [siteroot]/user/themes/antimatter/templates/eventlist.html.twig

{% embed 'partials/base.html.twig' %}

{% block content %}

{{ page.content }}

<h1>Sort by Star Date</h1>
<h2>order('header.startdate', 'desc')</h2>

{% set eventsRaw = page.children() %}
{% set events = eventsRaw.order('header.startdate', 'desc') %}

<table>
  <thead>
    <tr>
      <th>Title</th>
      <th>Start Date</th>
      <th>End Date</th>
      <th>Description</th>
    </tr>
  </thead>
  <tbody>
  {% for p in events %}
    <tr>
      <td>{{ p.header.title }}</td>
      <td>
        {{ p.header.startdate|date("F d, Y")  }}
        <br />
        (Raw: {{ p.header.startdate }}  )
      </td>
      <td>{{ p.header.startdate|date("F d, Y") }} </td>
      <td>{{ p.header.description }}</td>
    </tr>
  {% endfor %}
  </tbody>
</table>

<hr />

<h1>Sort by Description</h1>
<h2>order('header.description', 'asc')</h2>

{% set eventsRaw = page.children() %}
{% set events = eventsRaw.order('header.description', 'asc') %}

<table>
  <thead>
    <tr>
      <th>Title</th>
      <th>Start Date</th>
      <th>End Date</th>
      <th>Description</th>
    </tr>
  </thead>
  <tbody>
  {% for p in events %}
    <tr>
      <td>{{ p.header.title }}</td>
      <td>{{ p.header.startdate|date("F d, Y")  }}</td>
      <td>{{ p.header.startdate|date("F d, Y") }} </td>
      <td>{{ p.header.description }}</td>
    </tr>
  {% endfor %}
  </tbody>
</table>

{% endblock %}

{% endembed %}

The first sort {% set events = eventsRaw.order('header.startdate', 'desc') %} does not work as expected. It looks like instead it ignore that this is even a real value and is just sorts by title. The sort for {% set events = eventsRaw.order('header.description', 'asc') %} works. I have a more complex example in my real project, I can sort on any field that is not a date - however, if I do have any fields which are in the YYYY-MM-DD format, they will also not sort as expected.

---

Here is the code at: [siteroot]/user/pages/event-list/eventlist.md

---
title: 'Event List'
---

This is the demo to make a list of events. The events are child pages of this page. This page is template type "eventlist". The children are template type "event".

This issue is with the sorting of date strings. The date is stored in the actual content element as a string of length ten in the format `YYYY-MM-DD`. Since it is a string using this presentation, it should sort like a normal string. However, the string sorting seems to be doing weird things as this example will show.

---

Here are four content pages I am using in my testing:

At: [siteroot]/user/pages/event-list/new-years-2015/event.md

---
title: 'New Years 2015'
startdate: '2015-01-01'
enddate: '2015-01-01'
description: 'Happy New Year!'
---

At: [siteroot]/user/pages/event-list/ground-hog-day/event.md

---
title: 'Ground Hog Day'
startdate: '2015-02-02'
enddate: '2015-02-02'
description: '6 more weeks of winter?'
---

At: [siteroot]/user/pages/event-list/off-to-the-races/event.md

---
title: 'Off to the Races'
startdate: '2015-04-14'
enddate: '2015-04-15'
description: 'Going to see the race horses.'
---

At: [siteroot]/user/pages/event-list//back-to-the-future/event.md

---
title: 'Back to the Future'
startdate: '2015-08-12'
enddate: '2015-12-12'
description: 'Finally got a new flux capacitor.'
---

---

Here are screenshots of what is rendered when the event-list template is used:

In the first screenshot, you can see that it was NOT sorted using the date field, but instead seems to have ignored the request or reverted for some reason?

I am wondering if the date-type field get special handling somewhere in one of the called functions used for the sorting?

[erichgoldman]

To further demonstrate this, using the same blueprint as above, I modified the header.description field on each of the pages so that the description would just be a string in the style of YYYY-MM-DD and here is the output. Now even in the description field, the sorting is wrong:

It does not seem to be sorted on any field in this case.

[erichgoldman]

I believe we are starting from this order()

which calls this sortCollection()

Taking a quick look though the various functions used to build and do the sort, I do not see anyplace where it would be setting a special type of sort flag or otherwise resetting the value; however, there is a lot of logic here so I may not be seeing anything yet.

[erichgoldman]

I am doing some further debugging and find that buildSort

It is using a locale specific sorting:

$col->asort($list, $sort_flags);

If you do not have extension_loaded('intl') then it would use generic asort(). I found that the sorting seems to work when using the generic asort() as I would expect.

---

In my testing for

$locale = setlocale(LC_COLLATE, 0); //`setlocale` with a 0 param returns the current locale set
$col = Collator::create($locale);

I have $locale = "C"

and my $col, I used the following:

$res_val = collator_get_locale( $col, \Locale::VALID_LOCALE );
$res_act = collator_get_locale( $col, \Locale::ACTUAL_LOCALE );
printf( "Valid locale name: %s <br /> Actual locale name: %s", $res_val, $res_act );

and got:

Valid locale name: en_US_POSIX
Actual locale name: en_US_POSIX

I checked the PHP docs for Collator::asort and see nothing that should be making this different, I also tried resetting the sort flags to SORT_STRING vs SORT_REGULAR; however, SORT_STRING also gave me weird results trying to sort a YYYY-MM-DD value - still the wrong order but no the same as SORT_REGULAR.

---

I next tried setting the config option Override locale to "yes" in the admin backend - this did not result in proper sorting.

I checked the info section of the configuration and saw that intl.default_locale was set with "no value"

I still get:

Valid locale name: en_US_POSIX
Actual locale name: en_US_POSIX

It would appear that there is something in the collation rules that causes a problem for this, but I am not sure how to diagnose further. I think I may need to try to change something on my server (if possible) or if we could introduce a grav parameter to set the collation to a static value to handle these types of cases.

[flaviocopes]

I've been testing this for a while, if the date is not wrapped in quotes, all works fine. Dates are compared as dates.

If instead the field is wrapped in quotes like in your example, strings are compared with Collator::asort() to make sure we compare with the correct internationalization rules, and it seems (my case too) that Collator::asort() with the default flags compares by SORT_NATURAL and this for example makes (for some reason) 2016-05-04 > 2016-09-02.

To fix this either drop the quotes, or use SORT_NUMERIC, which is a constant that translates to 1, so

{% set events = eventsRaw.order('header.startdate', 'desc', null, 1) %} will sort as expected.

[erichgoldman]

In my example, I do not create the entries by hand, but used the admin to create it. The admin uses the quoted strings. If the quotes are impacting it, then the admin should be updated not to use quotes if this will give a more consistent experience.

The SORT_NATURAL fix in {% set events = eventsRaw.order('header.startdate', 'desc', null, 1) %} with leaving the quotes did not work for me.

I even tried setting it explicitly in buildSort() @1086 and then also @1109

with $col->asort($list, 1); and with $col->asort($list, SORT_NUMERIC);.

I also tried some variations for good measure, as is: $col->asort($list, $col->SORT_NUMERIC);

But none of SORT_REGULAR, SORT_NUMERIC or SORT_STRING give anything close to correct.

The natural sort via $col->asort($list); does not seem to work.

In your testing, what is your collation? As noted above the target server where I need to run this (shared host) is set at en_US_POSIX .

I am wondering if this is perhaps something with this collation and my PHP version? I am going to run some additional testing to see if I can have the shared server run a different version of PHP.

[erichgoldman]

Something weird happened while I was writing a test script, it suddenly started to work in my test script, then in the real code with $col->asort($list, 1);

I see now that 1.1.9 updated 2 days ago and that buildSort in my install doesn't check for $sort_flags as an input parameter. Let me start retesting with the new version, sorry

[erichgoldman]

I can confirm that {% set events = eventsRaw.order('header.startdate', 'desc', null, 1) %} with Grav 1.1.9 with my current tests. I agree that explicitly stating the type of sort when sorting it the best option.

[flaviocopes]

I got en_US_POSIX too. Looks like some strange quirk. Or there is a reason in that ordering, but I can't see it

[rhukster]

Closing to due to inactivity