Count pages with date field by year

[DrQuincy]

I may have missed it in the docs but is there an easy and performant way to count pages that have a date field by year.

So, let's say you have a news-item template with a dateFiled field that is of Datetime type. I would like to return something like this:

2020 => 17,
2019 => 46
...

Where the values are year and count respectively. So basically something like this in MySQL by in PW selectors (ideally without having to use raw database queries):

SELECT YEAR(`dateFiled`) as `year`, COUNT(YEAR(`dateFiled`)) AS `count` FROM `news` GROUP BY YEAR(`dateFiled`);

Thanks!

[MoritzLost]

I don't think you can do counts aggregated columns with ProcessWire selectors. There's Pages::count of course, but that only returns a single count.

This isn't the most efficient solution, but should be fine for a couple hundred or thousand pages:

$results = $pages->find('template=your_template');
$years = array_filter(array_map(function ($p) {
    $year = $p->getUnformatted('your_date_field');
    return $year ? wire('datetime')->date('Y', $year) : null;
}, $results->getArray()));
$pagesPerYear = array_count_values($years);

Below that you'll have to use raw SQL I think. Might also be possible with RockFinder3, not sure (@bernhard?)

[DrQuincy]

Thanks very much for your example, I'll give it a go tomorrow and let you know. ?

[louisstephens]

This thread might also help:

 

[DrQuincy]

Thanks for your help. Because we may end up with a lot of posts I am creating a JSON file in a hook (based on @MoritzLost's example) so there is almost no overhead on the front-end.

$wire->addHookAfter('Pages::saved', function(HookEvent $event) {
    
    $page = $event->arguments(0);

    if ($page->template == 'news-item') {

        $pages = wire('pages')->find('template=news-item');
        
        $years = [];
        
        foreach ($pages as $p) {
            
            $year = wire('datetime')->date('Y', $p->getUnformatted('newsDate'));
            
            if ($year !== null) {
                
                $years[$year] = isset($years[$year]) === false ? 1 : $years[$year] + 1;
                
            }
            
        }

        file_put_contents('json/news-years.json', json_encode($years, JSON_PRETTY_PRINT));

    }
    
});

Thanks. ?

[bernhard]

I've added an example how this can be done using RockFinder3 in the related thread:

 

[DrQuincy]

Thanks! ?