Answers.Body: <p>I emailed Solspace support about this and had an answer:</p>
  
  <blockquote>
    <p>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.</p>
    
    <p>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.</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-26T22:19:58.837Z
Answers.LastActivityDate: 2018-09-26T22:19:58.837Z
Answers.UserDisplayName: tremby
Body: <p>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.</p>
  
  <p>In the custom plugin I'm writing where I want to do this, I have the following:</p>
  
  <pre class="lang-php prettyprint-override"><code>use Solspace\Calendar\Elements\Event;
  
  ...
  
  $events = Event::buildQuery([
      'rangeStart' =&gt; $attributes['start'],
      'rangeEnd' =&gt; $attributes['end'],
  ])
  -&gt;orderBy([
      'least(
          datediff(`calendar_events`.`endDate`, `calendar_events`.`startDate`),
          datediff(`calendar_events`.`endDate`, now())
      )' =&gt; SORT_ASC,
      'content.field_feature' =&gt; SORT_DESC,
      'calendar_events.startDate' =&gt; SORT_ASC,
      'title' =&gt; SORT_ASC,
  ])
  -&gt;all()
  </code></pre>
  
  <p>The events do not come back in the order I expect, which is:</p>
  
  <ol>
  <li>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.)</li>
  <li>If otherwise equal, then events with the "featured" flag should come earlier.</li>
  <li>If otherwise equal, then events which start earlier should come earlier.</li>
  <li>If otherwise equal, then sort by title.</li>
  </ol>
  
  <p>I believe I have the ordering logic correct.</p>
  
  <p>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 <em>are</em> in the order I want.</p>
  
  <p>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):</p>
  
  <pre class="lang-sql prettyprint-override"><code>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` &gt;= '2018-10-01 00:00:00' )
                    OR ( calendar_events.`rrule` IS NOT NULL
                         AND calendar_events.`until` IS NOT NULL
                         AND calendar_events.`until` &gt;= '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` &lt;= '2018-10-31 23:59:59'
                    OR calendar_events.`freq` = 'SELECT_DATES' )
             AND ( `elements_sites`.`siteid` = '1' )
             AND ( `calendar_events`.`id` &lt;&gt; 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`;
  </code></pre>
  
  <p>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 <code>$events</code>, because in <code>$events</code> they're in a totally different order.</p>
  
  <p>What's happening here? How can I avoid whatever ordering rules are being applied by Craft or Solspace Calendar after my custom ones?</p>
  
Tags: <craft3><database><ordering><sorting><plugin-solspacecalendar>
Title: Advanced sorting of events with database expressions (Solspace Calendar)
ThreadText: # Advanced sorting of events with database expressions (Solspace Calendar)
  
  (Asked by: username_0 on 2018-09-26)
  
  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:
  
  ```php
  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):
  
  ```sql
  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?
  
  **Tags:** craft3, database, ordering, sorting, plugin-solspacecalendar
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2018-09-26. Score: 1 | Accepted answer
  
  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.
  
  
  ---
  
  