I wonder if anyone could please give me some pointers as to how best to achieve this:

I'm using EventCalendar and EventResources modules. What I want to do is sort the order of calendar events displayed by Resource. For anyone not familiar with the eventresources a resource is stored for each CalendarDateTime record.

CREATE TABLE IF NOT EXISTS `CalendarDateTime_Resources` (
  `ID` int(11) NOT NULL auto_increment,
  `CalendarDateTimeID` int(11) NOT NULL default '0',
  `EventResourceID` int(11) NOT NULL default '0',
  `BookingQuantity` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `CalendarDateTimeID` (`CalendarDateTimeID`),
  KEY `EventResourceID` (`EventResourceID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

In a simple world I would simply join CalendarDateTime to CalendarDateTime_Resources to return a recordset and then sort by EventResourceID in my calendar display. My uncertaintly is how to approach this the Silverstripe way. Presumably I do not want to touch any of the existing code for EventCalendat or EventResouces, so I'm guessing I need to extend CalendarDateTime to return my joined CalendarDateTime and CalendarDateTime_Resources records?

I'm going insane here. I can see how subclass any level of eventcalendar to add new fields, what i can't see is how to add new fields that already exist in a related table. Fof every CalendarDateTime record that is created a record is also created in CalendarDateTime_Resources, with the resource information held in table:

CREATE TABLE IF NOT EXISTS `EventResource` (
  `ID` int(11) NOT NULL auto_increment,
  `ClassName` enum('EventResource') character set utf8 default 'EventResource',
  `Created` datetime default NULL,
  `LastEdited` datetime default NULL,
  `Title` varchar(255) character set utf8 default NULL,
  `Description` mediumtext character set utf8,
  `Type` enum('Single','Limited','Unlimited') character set utf8 default 'Single',
  `Quantity` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `ClassName` (`ClassName`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

So in SQL what I want to do is instead of returning a CalendarDateTime record I was to return:

SELECT * FROM `CalendarDateTime` 
LEFT JOIN `CalendarDateTime_Resources` ON `CalendarDateTime_Resources`.CalendarDateTimeID = `CalendarDateTime`.ID
LEFT JOIN `EventResource` ON `EventResource`.ID = `CalendarDateTime_Resources`.EventResourceID

So maybe I subclass Calendar and replace the function getDateJoin to include extended query? But i can't see that working as the fields in the additional tables are not defined anywhere. So maybe i need to subclass CalendarDatetime?