Skip to content

Instantly share code, notes, and snippets.

@kinglozzer
Created August 29, 2019 16:26
Show Gist options
  • Save kinglozzer/f368dd866e521bb0c54312f50aa18674 to your computer and use it in GitHub Desktop.
Save kinglozzer/f368dd866e521bb0c54312f50aa18674 to your computer and use it in GitHub Desktop.
Category & uncategorised blog post filtering
<?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"');
});
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment