How to group pdoResources by month and some TV value

Support Extras
1

I have a list of resources for every performance date, including date and time and also performance title. Some performances run for a number of dates.

I want to create a list of all events that are first grouped by month (or year) and then inside each month grouped by performance title.

What would be the best strategy to create such a list?

2

So where are these values stored?
Is “performance title” the pagetitle of the resource or a TV?
Is “performance date” a TV or a resource field (like e.g. publishedon)?

Do you need a subheading for every month in the output? (Like a heading with the month, followed by a list of all performances of that month.)

3

They are all stored in TVs. (pagetitle cannot practically be used as a performance title if we have several performances of the same name).

Yes, a subheading for the month would be ideal.

4

Dates in TVs are stored as strings (like all TV values) in the format yyyy-mm-dd hh:mm:ss (e.g. 2025-05-11 12:42:02).

When sorting by a TV of input type “Date”, pdoResources automatically casts the values to dates.
E.g. &sortby=`{"performance_date":"ASC"}` results in the SQL ... ORDER BY CAST(`TVperformance_date`.`value` AS DATETIME) ASC.

To sort only by the ‘year’ part of the date, or the ‘month’ part, you should be able to do something like this:

[[!pdoResources?
    ...
    &includeTVs=`performance_date,performance_title`
    &sortby=`{"YEAR(performance_date)":"ASC","MONTH(performance_date)":"ASC","performance_title":"ASC"}`
]]

Here the resources are first only sorted by the ‘year’ of the performance date, then by the ‘month’, and finally by the performance title.

The resulting SQL:

... ORDER BY 
YEAR(CAST(`TVperformance_date`.`value` AS DATETIME)) ASC,
MONTH(CAST(`TVperformance_date`.`value` AS DATETIME)) ASC,
`TVperformance_title`.`value` ASC
1 Like
5

To group the result by year and month, the easiest solution is probably to write a custom snippet that calls pdoResources and then processes the result further.

Something like this might work in your case:

<?php
// execute the snippet "pdoResources" and return the result as raw data
$data = $modx->runSnippet('pdoResources', [ 
    'parents' => 43, // change this to the actual value
    'includeTVs' => 'performance_date,performance_title',
    'sortby' => '{"YEAR(performance_date)":"ASC","MONTH(performance_date)":"ASC","performance_title":"ASC"}',
    'return' => 'data'
]);

$fmt = new IntlDateFormatter('en_US', null, null); // for formatting the date (in the subheading)
$fmt->setPattern('MM.yyyy');
$current_month_year = "";
$output = [];
foreach ($data as $item) {
    $month_year = strtotime($item['tv.performance_date']); // get the timestamp for the performance date
    $month_year = $fmt->format($month_year); // format the date
    if ($current_month_year != $month_year) { // the value has changed -> output a subheading
        $current_month_year = $month_year;
        $output[] = '<h3>' . $month_year . '</h3>';
    }
    $output[] = $modx->getChunk('tplPerformance', $item); // parse a result using the chunk 'tplPerformance'
}
return implode("\n", $output);
1 Like