Category & uncategorised blog post filtering

BlogExtension.php

<?php

/**
 * Only show blog posts which are either:
 *
 * A) in the list of categories returned by getApplicableBlogCategories() or;
 * B) do not have any categories assigned
 */
class BlogExtension extends Extension
{
    public function getApplicableBlogCategories()
    {
        // return a DataList of applicable categories
    }
    
    /**
     * @param DataList $blogPosts
     * @throws Exception
     */
    public function updateGetBlogPosts(DataList &$blogPosts)
    {
        $blogCategoryIDs = $this->getApplicableBlogCategories()->column('ID');
        if (empty($blogCategoryIDs)) {
            return;
        }

        // We only want to return blog posts which are in the list of categories,
        // or don't have any categories set
        $blogPosts = $blogPosts->alterDataQuery(function (DataQuery $dataQuery) use ($blogCategoryIDs) {
            // Ensure we're joining to the 'BlogCategory' table
            $dataQuery->applyRelation('Categories');

            // Joined relation tables are aliased with a prefix
            $relationPrefix = DataQuery::applyRelationPrefix('Categories');
            $categoryTable = "{$relationPrefix}BlogCategory";

            // We have to use HAVING because we're calling COUNT() on the category IDs for
            // each post, which is incompatible with WHERE
            $placeholders = DB::placeholders($blogCategoryIDs);
            $having = <<<SQL
(COUNT("{$categoryTable}"."ID") = 0)
OR
("{$categoryTable}"."ID" IN ( {$placeholders} ))
SQL;
            $dataQuery->having([$having => $blogCategoryIDs]);

            // MySQL rules mean that columns in a HAVING statement must be included in the
            // SELECT statement. We give it an alias because without it, SilverStripe will
            // remove it from the SELECT statement
            $dataQuery->selectField('"'.$categoryTable.'"."ID"', "BlogCategoryID");

            // We need to group by SiteTree.ID or we'll only see one blog post per category
            $siteTreeTable = $this->owner->stageTable(
                DataObject::getSchema()->tableName(SiteTree::class),
                Versioned::get_stage()
            );
            $dataQuery->groupby('"'.$siteTreeTable.'"."ID"');
        });
    }
}