I have two entry-types, 'Events' and 'Reports'. 'Events' have a matrix-field ('eventMultiDate') that allows authors to assign multiple dates to an event (recurring). 'Reports' have the default 'postDate' field.
I would like to return entries from both entry-types combined in a single array, ordered by their corresponding date-fields. As 'Events' may occur on more than one date, one single 'Event' may be displayed more than once in that array.
I came up with a possible solution, but would like to know if there is a simpler way to achieve this making better use of the craft API?
/**
* As 'events' may occur on multiple dates and have to be displayed along with 'reports' they have to queried via this method.
*/
public function queryEvents(\DateTime $dateFrom, \DateTime $dateTo) {
/*
* create a 'event'-criteria to build the report query
*/
// create a ECM for element-type 'Entry'
$eventsCriteria = craft()->elements->getCriteria(ElementType::Entry);
// create a DBCommand object from ECM
$eventsDbCommand = craft()->elements->buildElementsQuery($eventsCriteria);
// alter select part to only return the elements ID (alias id) + the corresponding date (alias eventDate)
$eventsDbCommand->select(array('elements.id AS id', 'matrixcontent_eventdate.field_multiDate_date AS eventDate'));
// add section handle with placeholder 'sectionsHandle1'
$eventsDbCommand->andWhere('sections.handle = :sectionsHandle1');
// filter the matrix-eventdate-field by current locale, too
$eventsDbCommand->andWhere('matrixcontent_eventdate.locale = :locale');
// add filter on from/to dates for matrix-column field_multidate_date to query
$eventsDbCommand->andWhere('matrixcontent_eventdate.field_multiDate_date >= :dateFrom');
$eventsDbCommand->andWhere('matrixcontent_eventdate.field_multiDate_date < :dateTo');
// join the matrixblocks + matrixcontent_eventdate tables
$eventsDbCommand->join('matrixblocks matrixblocks', 'entries.id = matrixblocks.ownerId');
$eventsDbCommand->join('matrixcontent_eventdate matrixcontent_eventdate', 'matrixblocks.id = matrixcontent_eventdate.elementId');
// group by element ID + the eventdates elementId to remove duplicates
$eventsDbCommand->group(array('elements.id', 'matrixcontent_eventdate.elementId'));
// get the resulting SQL-String
$eventsSql = $eventsDbCommand->getText();
/*
* create a 'report'-criteria to build the report query
*/
// create a ECM for element-type 'Entry'
$reportsCriteria = craft()->elements->getCriteria(ElementType::Entry);
// create a DBCommand object from ECM
$reportsDbCommand = craft()->elements->buildElementsQuery($reportsCriteria);
// alter select part to only return the elements ID (alias id) + the corresponding date (alias eventDate)
$reportsDbCommand->select(array('elements.id AS id', 'entries.postDate AS eventDate'));
// add section handle with placeholder 'sectionsHandle2'
$reportsDbCommand->andWhere('sections.handle = :sectionsHandle2');
// add filter on from/to dates for postDate column to query
$reportsDbCommand->andWhere('entries.postDate >= :dateFrom');
$reportsDbCommand->andWhere('entries.postDate < :dateTo');
// group by element ID
$reportsDbCommand->group(array('elements.id'));
// get the resulting SQL-String
$reportsSql = $reportsDbCommand->getText();
/*
* Create a new DbCommand to union these two together and order in descending order by the eventdate
*/
// convert incoming \DateTime parameters to Craft::DateTime instances
$dateTimeFrom = new DateTime('@'.$dateFrom->getTimestamp());
$dateTimeTo = new DateTime('@'.$dateTo->getTimestamp());
$unionCommand = craft()->db->createCommand();
$unionCommand
->setText('SELECT id, eventDate FROM (' . $eventsSql . ' UNION ALL ' . $reportsSql . ') e ORDER BY eventDate DESC')
->bindValue(':locale', $eventsCriteria->locale)
->bindValue(':sectionsHandle1', 'events')
->bindValue(':sectionsHandle2', 'reports')
->bindValue(':dateTo', $dateTimeTo->mySqlDateTime())
->bindValue(':dateFrom', $dateTimeFrom->mySqlDateTime());
$results = $unionCommand->queryAll();
$resultingEntries = array();
foreach ($results as $result) {
$resultingEntries[] = array(
'id' => $result['id'],
'eventDate' => DateTime::createFromFormat(DateTime::MYSQL_DATETIME, $result['eventDate']),
'entry' => craft()->entries->getEntryById($result['id'])
);
}
return $resultingEntries;
}