How to retrieve entries from two entry-types ordered by different date-fields?

Question

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;
  }

Answer 1

My SuperSort Plugin is built for use cases exactly such as this. It allows you to render each object in your array using a Twig object template and sort the array based on the rendered value.

To solve your particular use case, just fetch both of the arrays, merge them, and SuperSort them by their appropriate date field:

{% set foo = craft.entries.section('foo')... %}
{% set bar = craft.entries.section('bar')... %}
{% set feed = foo|merge(bar) %}
{% set sortedFeed = feed | supersort('sortAs', '{{ object.fooProperty is defined ?: object.barProperty }}') %}

(The plugin parses the supplied 'micro-template' into its value and sorts the list according to that computed value.)

Answer 2

It's useful to note that you can also use SQL comparison functions in the order param, as explained in this answer:

• How do you sort elements queried from multiple channels

(This isn't exactly applicable to OP's case, since we need to compare an entry's field value to a Matrix block's field value to determine the appropriate sort order. But it would be super-useful in solving performance issues if all the data was directly in the entry's fields.)