Answers.Body: <p>My <a href="http://michaelrog.com/craft/supersort" rel="nofollow">SuperSort</a> 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.</p>
  
  <p>To solve your particular use case, just fetch both of the arrays, merge them, and SuperSort them by their appropriate date field:</p>
  
  <pre><code>{% 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 }}') %}
  </code></pre>
  
  <p>(The plugin parses the supplied '<a href="http://buildwithcraft.com/classreference/services/TemplatesService#renderObjectTemplate-detail" rel="nofollow">micro-template</a>' into its value and sorts the list according to that computed value.)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-04-05T15:40:01.993Z
Answers.Text: But in this case, ALL entries of both sections will have to be fetched from database to be able to sort/process them. Performance-wise this isn't an optimal solution, unless you know beforehand that you are dealing with sections that have only a limited amount of entries. Correct me if I'm wrong here.
Answers.UserDisplayName: Clemens Sum
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-04-06T18:13:23.310Z
Answers.Text: There are several ways you could pull entries from the database, depending on how many entries there are, how many you want to use, etc. In any case, you shouldn't need to fetch ALL the entries from a section... only as many as you want to display. (So if you want to display the 20 most recent entries from either section, ordered by date, you probably need to pull 20 entries from each of the sections, and then sort them. You'd be discarding 20 entries in that case, but that's not particularly heinous.)
Answers.UserDisplayName: Michael Rog
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-04-14T13:40:14.660Z
Answers.Text: Ah, I overlooked a key part of OP's question when I responded to your comment. Yeah, if some of the date information is in Matrix block fields, you would have to fetch all the entries from that section and spin up the ECM for that Matrix field on each of then. That's gonna be a performance hit. If your section has lots of entries, a custom query/plugin would be the way to go.
Answers.UserDisplayName: Michael Rog
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-03-31T07:02:58.657Z
Answers.LastActivityDate: 2015-03-31T07:02:58.657Z
Answers.UserDisplayName: Michael Rog
Answers.Body: <p>It's useful to note that you can also use SQL comparison functions in the <code>order</code> param, as explained in this answer:</p>
  
  <ul>
  <li><a href="https://craftcms.stackexchange.com/a/78/689">How do you sort elements queried from multiple channels</a></li>
  </ul>
  
  <p>(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.)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-04-14T13:35:27.950Z
Answers.LastActivityDate: 2015-04-14T13:44:14.520Z
Answers.LastEditDate: 2017-04-13T12:50:59.930Z
Answers.UserDisplayName: Michael Rog
Body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <pre class="lang-php prettyprint-override"><code>  /**
     * 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()-&gt;elements-&gt;getCriteria(ElementType::Entry);
  
      // create a DBCommand object from ECM
      $eventsDbCommand = craft()-&gt;elements-&gt;buildElementsQuery($eventsCriteria);
  
      // alter select part to only return the elements ID (alias id) + the corresponding date (alias eventDate)
      $eventsDbCommand-&gt;select(array('elements.id AS id', 'matrixcontent_eventdate.field_multiDate_date AS eventDate'));
  
      // add section handle with placeholder 'sectionsHandle1'
      $eventsDbCommand-&gt;andWhere('sections.handle = :sectionsHandle1');
  
      // filter the matrix-eventdate-field by current locale, too
      $eventsDbCommand-&gt;andWhere('matrixcontent_eventdate.locale = :locale');
  
      // add filter on from/to dates for matrix-column field_multidate_date to query
      $eventsDbCommand-&gt;andWhere('matrixcontent_eventdate.field_multiDate_date &gt;= :dateFrom');
      $eventsDbCommand-&gt;andWhere('matrixcontent_eventdate.field_multiDate_date &lt; :dateTo');
  
      // join the matrixblocks + matrixcontent_eventdate tables
      $eventsDbCommand-&gt;join('matrixblocks matrixblocks', 'entries.id = matrixblocks.ownerId');
      $eventsDbCommand-&gt;join('matrixcontent_eventdate matrixcontent_eventdate', 'matrixblocks.id = matrixcontent_eventdate.elementId');
  
      // group by element ID + the eventdates elementId to remove duplicates
      $eventsDbCommand-&gt;group(array('elements.id', 'matrixcontent_eventdate.elementId'));
  
      // get the resulting SQL-String
      $eventsSql = $eventsDbCommand-&gt;getText();
  
      /*
       *  create a 'report'-criteria to build the report query
       */
      // create a ECM for element-type 'Entry'
      $reportsCriteria = craft()-&gt;elements-&gt;getCriteria(ElementType::Entry);
  
      // create a DBCommand object from ECM
      $reportsDbCommand = craft()-&gt;elements-&gt;buildElementsQuery($reportsCriteria);
  
      // alter select part to only return the elements ID (alias id) + the corresponding date (alias eventDate)
      $reportsDbCommand-&gt;select(array('elements.id AS id', 'entries.postDate AS eventDate'));
  
      // add section handle with placeholder 'sectionsHandle2'
      $reportsDbCommand-&gt;andWhere('sections.handle = :sectionsHandle2');
  
      // add filter on from/to dates for postDate column to query
      $reportsDbCommand-&gt;andWhere('entries.postDate &gt;= :dateFrom');
      $reportsDbCommand-&gt;andWhere('entries.postDate &lt; :dateTo');
  
      // group by element ID
      $reportsDbCommand-&gt;group(array('elements.id'));
  
      // get the resulting SQL-String
      $reportsSql = $reportsDbCommand-&gt;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-&gt;getTimestamp());
      $dateTimeTo = new DateTime('@'.$dateTo-&gt;getTimestamp());
  
      $unionCommand = craft()-&gt;db-&gt;createCommand();
      $unionCommand
        -&gt;setText('SELECT id, eventDate FROM (' . $eventsSql . ' UNION ALL ' . $reportsSql . ') e ORDER BY eventDate DESC')
        -&gt;bindValue(':locale', $eventsCriteria-&gt;locale)
        -&gt;bindValue(':sectionsHandle1', 'events')
        -&gt;bindValue(':sectionsHandle2', 'reports')
        -&gt;bindValue(':dateTo', $dateTimeTo-&gt;mySqlDateTime())
        -&gt;bindValue(':dateFrom', $dateTimeFrom-&gt;mySqlDateTime());
  
      $results = $unionCommand-&gt;queryAll();
  
      $resultingEntries = array();
  
      foreach ($results as $result) {
        $resultingEntries[] = array(
          'id' =&gt; $result['id'],
          'eventDate' =&gt; DateTime::createFromFormat(DateTime::MYSQL_DATETIME, $result['eventDate']),
          'entry' =&gt; craft()-&gt;entries-&gt;getEntryById($result['id'])
        );
      }
  
      return $resultingEntries;
    }
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-20T01:01:59.333Z
Comments.Text: Please add a question to your post that can be specifically answered. As it's written now, there is no question posed.
Comments.UserDisplayName: Anna_MediaGirl
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-22T17:24:50.563Z
Comments.Text: @Anna_MediaGirl Personally I think this is a great question and shouldn't be closed. It's one of the few examples of creating a complex query/elementCriteriaModel on the craft SE, which has helped me. I would also like to know if there is a better way to do this; and might have a simpler approach to offer.
Comments.UserDisplayName: Douglas McDonald
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-02-23T15:46:22.093Z
Comments.Text: @DouglasMcDonald I'm okay with it staying on the site but it needs to be reformatted to fit the StackExchange model of question & answer. As posted it's a code sample which  can never have a "correct" answer. HulaBula... can you adjust to a question/answer format so I can reopen?
Comments.UserDisplayName: Anna_MediaGirl
Tags: <plugin-development><matrix><datetime><elementcriteriamodel>
Title: How to retrieve entries from two entry-types ordered by different date-fields?
ThreadText: # How to retrieve entries from two entry-types ordered by different date-fields?
  
  (Asked by: username_0 on 2014-11-06)
  
  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?
  
  ```php
    /**
     * 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;
    }
  
  ```
  
  **Tags:** plugin-development, matrix, datetime, elementcriteriamodel
  
  > **Comments:**
  > **username_1**: Please add a question to your post that can be specifically answered. As it's written now, there is no question posed.  
  > **username_2**: @username_1 Personally I think this is a great question and shouldn't be closed. It's one of the few examples of creating a complex query/elementCriteriaModel on the craft SE, which has helped me. I would also like to know if there is a better way to do this; and might have a simpler approach to offer.  
  > **username_1**: @username_2 I'm okay with it staying on the site but it needs to be reformatted to fit the StackExchange model of question & answer. As posted it's a code sample which  can never have a "correct" answer. HulaBula... can you adjust to a question/answer format so I can reopen?  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_3 on 2015-03-31. Score: 4
  
  My [SuperSort](http://username_3.com/craft/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](http://buildwithcraft.com/classreference/services/TemplatesService#renderObjectTemplate-detail)' into its value and sorts the list according to that computed value.)
  
  > **Comments:**
  > **username_0**: But in this case, ALL entries of both sections will have to be fetched from database to be able to sort/process them. Performance-wise this isn't an optimal solution, unless you know beforehand that you are dealing with sections that have only a limited amount of entries. Correct me if I'm wrong here.  
  > **username_3**: There are several ways you could pull entries from the database, depending on how many entries there are, how many you want to use, etc. In any case, you shouldn't need to fetch ALL the entries from a section... only as many as you want to display. (So if you want to display the 20 most recent entries from either section, ordered by date, you probably need to pull 20 entries from each of the sections, and then sort them. You'd be discarding 20 entries in that case, but that's not particularly heinous.)  
  > **username_3**: Ah, I overlooked a key part of OP's question when I responded to your comment. Yeah, if some of the date information is in Matrix block fields, you would have to fetch all the entries from that section and spin up the ECM for that Matrix field on each of then. That's gonna be a performance hit. If your section has lots of entries, a custom query/plugin would be the way to go.  
  
  ---
  
  ### Answer by username_3 on 2015-04-14. Score: 1
  
  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](https://craftcms.stackexchange.com/a/78/689)
  
  (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.)
  
  
  ---
  
  