Not possible to sort events after endDate

mihob commented 1 week ago

Describe the bug

For an archive, I want to list all my events in descending order by end date:

{% set events = craft.events.events().endDate(null).orderBy('endDate').all() %}

This worked in v2 but results in the following Database Exception in v3:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'endDate' in 'order clause'
The SQL being executed was: SELECT `elements`.`id`, `elements`.`canonicalId`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateLastMerged`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`siteId`, `elements_sites`.`title`, `elements_sites`.`slug`, `elements_sites`.`uri`, `elements_sites`.`content`, `elements_sites`.`enabled` AS `enabledForSite`, `events_events`.`id`, `events_events`.`typeId`, `events_events`.`capacity`, `events_events`.`postDate`, `events_events`.`expiryDate`, `events_events`.`ticketsCache`, `sessions`.`startDate`, `sessions`.`endDate`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `siteSettingsId`
FROM `elements` `elements`
INNER JOIN `events_events` `events_events` ON `events_events`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
WHERE (`elements_sites`.`siteId`=1) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`events_events`.`postDate` <= '2024-11-13 01:40:13') AND ((`events_events`.`expiryDate` IS NULL) OR (`events_events`.`expiryDate` > '2024-11-13 01:40:13'))) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `endDate`) `subquery`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`siteSettingsId`
INNER JOIN `events_events` `events_events` ON `events_events`.`id` = `subquery`.`elementsId`
LEFT JOIN (SELECT `primaryOwnerId` AS `eventId`, MIN(startDate) AS `startDate`, MAX(endDate) AS `endDate`
FROM `events_sessions`
INNER JOIN `elements` `elements` ON `elements`.`id` = `events_sessions`.`id`
WHERE (`elements`.`enabled`=TRUE) AND (`elements`.`dateDeleted` IS NULL)
GROUP BY `primaryOwnerId`) `sessions` ON `sessions`.`eventId` = `events_events`.`id`
ORDER BY `endDate`

The problem is probably that the order by clause is also inserted in the subquery and the column is not present there after the changeover to the session system.

Steps to reproduce

Craft CMS version

5.5.0

Plugin version

3.0.1

Multi-site?

Yes

Additional context

No response

engram-design commented 1 week ago

Yeah, that might be a tricky one, as you're correct that it's now derived from the last session. I'll see what's possible!

engram-design commented 1 week ago

Fixed for the next release. To get this early, run composer require verbb/events:"dev-craft-5 as 3.0.2".

engram-design commented 3 days ago

Fixed in 3.0.3

verbb / events