5

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;
  }
  • Please add a question to your post that can be specifically answered. As it's written now, there is no question posed. – Anna_MediaGirl Feb 20 '15 at 1:01
  • 2
    @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. – Douglas McDonald Feb 22 '15 at 17:24
  • @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? – Anna_MediaGirl Feb 23 '15 at 15:46
4

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.)

  • 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. – Clemens Sum Apr 5 '15 at 15:40
  • 1
    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.) – Michael Rog Apr 6 '15 at 18:13
  • 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. – Michael Rog Apr 14 '15 at 13:40
1

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

(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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.