Advanced sorting of events with database expressions (Solspace Calendar)

Question

I want to do some complicated sorting of events coming back from the Solspace Calendar plugin. I'm not certain whether this issue is specific to this plugin or whether it's a general Craft thing.

In the custom plugin I'm writing where I want to do this, I have the following:

use Solspace\Calendar\Elements\Event;

...

$events = Event::buildQuery([
    'rangeStart' => $attributes['start'],
    'rangeEnd' => $attributes['end'],
])
->orderBy([
    'least(
        datediff(`calendar_events`.`endDate`, `calendar_events`.`startDate`),
        datediff(`calendar_events`.`endDate`, now())
    )' => SORT_ASC,
    'content.field_feature' => SORT_DESC,
    'calendar_events.startDate' => SORT_ASC,
    'title' => SORT_ASC,
])
->all()

The events do not come back in the order I expect, which is:

1. By number remaining days of the event, ascending. (An event which starts tomorrow and runs for 2 days have 2 days remaining. An event which started yesterday and runs for 2 days has 1 day remaining. An event which has already finished has 0 remaining days.)
2. If otherwise equal, then events with the "featured" flag should come earlier.
3. If otherwise equal, then events which start earlier should come earlier.
4. If otherwise equal, then sort by title.

I believe I have the ordering logic correct.

If I look at the Craft log I can see the query being run. If I put that into a MySQL console and run it, the results are in the order I want.

Here's such a query (which I've modified a bit to give more useful columns in the result set, so I can verify the order is correct):

SELECT `elements`.`id`,
       calendar_events.`startdate`,
       calendar_events.`enddate`,
       Datediff(calendar_events.enddate, calendar_events.startdate) durat,
       Datediff(calendar_events.enddate, Now())                     daystogo,
       Least(Datediff(calendar_events.enddate, calendar_events.startdate),
       Datediff(
       calendar_events.enddate, Now()))                             days,
       `content`.`title`,
       `content`.`field_feature`
FROM   (SELECT `elements`.`id`       AS `elementsId`,
               `elements_sites`.`id` AS `elementsSitesId`,
               `content`.`id`        AS `contentId`
        FROM   `craft_elements` `elements`
               INNER JOIN `craft_calendar_events` `calendar_events`
                       ON `calendar_events`.`id` = `elements`.`id`
               INNER JOIN `craft_elements_sites` `elements_sites`
                       ON `elements_sites`.`elementid` = `elements`.`id`
               INNER JOIN `craft_content` `content`
                       ON `content`.`elementid` = `elements`.`id`
               INNER JOIN `craft_calendar_calendars`
                       ON `craft_calendar_calendars`.`id` =
                          calendar_events.`calendarid`
               INNER JOIN `craft_users`
                       ON `craft_users`.`id` = calendar_events.`authorid`
        WHERE  ( ( calendar_events.`rrule` IS NULL
                   AND calendar_events.`enddate` >= '2018-10-01 00:00:00' )
                  OR ( calendar_events.`rrule` IS NOT NULL
                       AND calendar_events.`until` IS NOT NULL
                       AND calendar_events.`until` >= '2018-10-01 00:00:00' )
                  OR ( calendar_events.`rrule` IS NOT NULL
                       AND calendar_events.`until` IS NULL )
                  OR ( calendar_events.`freq` = 'SELECT_DATES' ) )
           AND ( calendar_events.`startdate` <= '2018-10-31 23:59:59'
                  OR calendar_events.`freq` = 'SELECT_DATES' )
           AND ( `elements_sites`.`siteid` = '1' )
           AND ( `calendar_events`.`id` <> 9761 )
           AND ( `content`.`siteid` = '1' )
           AND ( `elements`.`archived` = false )
           AND ( `elements`.`enabled` = true )
           AND ( `elements_sites`.`enabled` = true )
        ORDER  BY Least(Datediff(`calendar_events`.`enddate`,
                        `calendar_events`.`startdate`),
                             Datediff(`calendar_events`.`enddate`, Now())),
                  `content`.`field_feature` DESC,
                  `calendar_events`.`startdate`,
                  `title`) `subquery`
       INNER JOIN `craft_calendar_events` `calendar_events`
               ON `calendar_events`.`id` = `subquery`.`elementsid`
       INNER JOIN `craft_elements` `elements`
               ON `elements`.`id` = `subquery`.`elementsid`
       INNER JOIN `craft_elements_sites` `elements_sites`
               ON `elements_sites`.`id` = `subquery`.`elementssitesid`
       INNER JOIN `craft_content` `content`
               ON `content`.`id` = `subquery`.`contentid`
       INNER JOIN `craft_calendar_calendars`
               ON `craft_calendar_calendars`.`id` = calendar_events.`calendarid`
       INNER JOIN `craft_users`
               ON `craft_users`.`id` = calendar_events.`authorid`
ORDER  BY Least(Datediff(`calendar_events`.`enddate`,
                `calendar_events`.`startdate`),
                    Datediff(`calendar_events`.`enddate`, Now())),
          `content`.`field_feature` DESC,
          `calendar_events`.`startdate`,
          `title`;

However, something must then be happening between the database returning results (in the right order) and the result being returned in my line of PHP code and stored as $events, because in $events they're in a totally different order.

What's happening here? How can I avoid whatever ordering rules are being applied by Craft or Solspace Calendar after my custom ones?

Answer 1

I emailed Solspace support about this and had an answer:

Unfortunately it's not possible to have the SQL order events like you need, because after the SQL fetches the events, we have to hydrate all occurences that any given event might have, and then reorder the bulk separately since occurrences usually change the order of all events.

We might be able to fire an event after the sorting's done so you could adjust the order, but it will never be possible to sort everything right using SQL sort alone.

This makes sense -- the database has no idea what the fields to do with event repetition mean, so there's no way it could correctly do the ordering logic where events repeat.

In my case I'm not using the repeating logic, so it's a shame I can't switch this behaviour off, but at least it's explained.