From e6cef37e0c782fe770db20888d99c17d10e2c479 Mon Sep 17 00:00:00 2001 From: Thomas Lange Date: Thu, 1 Jul 2021 20:11:34 +0200 Subject: Add category system to categorize posts (readme) This commit implements a new category system to categorize posts. Each category can have an unlimited number of nested children categories. A single post don't necessarily need to be in a category, but it can. Each category can have a full content body like posts or pages, so you have enough space to describe the content of your categories. Please note that you need to have at least the following MySQL/MariaDB versions to use the category system, because it uses "WITH RECURSIVE" database queries, the so-called "Common-Table-Expressions (CTE)". MariaDB: 10.2.2 MySQL: 8.0 See: https://mariadb.com/kb/en/with/ See: https://dev.mysql.com/doc/refman/8.0/en/with.html --- core/application.php | 7 ++ core/functions.php | 64 ++++++++++++- core/include/category/list.php | 69 ++++++++++++++ core/include/category/main.php | 134 ++++++++++++++++++++++++++ core/include/post/main.php | 17 ++++ core/namespace/Application.php | 10 ++ core/namespace/ORM/Entities/Category.php | 14 +++ core/namespace/ORM/Entities/Post.php | 13 +-- core/namespace/ORM/Repositories/Category.php | 138 +++++++++++++++++++++++++++ core/namespace/ORM/Repositories/Post.php | 12 +++ 10 files changed, 471 insertions(+), 7 deletions(-) create mode 100644 core/include/category/list.php create mode 100644 core/include/category/main.php create mode 100644 core/namespace/ORM/Entities/Category.php create mode 100644 core/namespace/ORM/Repositories/Category.php (limited to 'core') diff --git a/core/application.php b/core/application.php index 5bbc5d1..0e1324a 100644 --- a/core/application.php +++ b/core/application.php @@ -63,23 +63,29 @@ foreach([ 'PATHINFO.PROT' => $_SERVER['REQUEST_SCHEME'] ?? 'https', 'PATHINFO.HOST' => $_SERVER['HTTP_HOST'] ?? 'localhost', 'PATHINFO.BASE' => '', + 'CATEGORY.DIRECTORY' => 'category', 'PAGE.DIRECTORY' => 'page', 'POST.DIRECTORY' => 'post', 'USER.DIRECTORY' => 'user', + 'CATEGORY.SLUG_URLS' => TRUE, 'PAGE.SLUG_URLS' => TRUE, 'POST.SLUG_URLS' => TRUE, 'USER.SLUG_URLS' => TRUE, + 'CATEGORY.EMOTICONS' => TRUE, 'PAGE.EMOTICONS' => TRUE, 'POST.EMOTICONS' => TRUE, 'USER.EMOTICONS' => TRUE, + 'CATEGORY.LIST_SIZE' => 10, 'PAGE.LIST_SIZE' => 10, 'POST.LIST_SIZE' => 10, 'USER.LIST_SIZE' => 10, 'PAGE.FEED_SIZE' => 25, 'POST.FEED_SIZE' => 25, + 'CATEGORY.DESCRIPTION_SIZE' => 200, 'PAGE.DESCRIPTION_SIZE' => 200, 'POST.DESCRIPTION_SIZE' => 200, 'USER.DESCRIPTION_SIZE' => 200, + 'CATEGORY.SINGLE_REDIRECT' => FALSE, 'PAGE.SINGLE_REDIRECT' => FALSE, 'POST.SINGLE_REDIRECT' => FALSE, 'USER.SINGLE_REDIRECT' => FALSE, @@ -98,6 +104,7 @@ foreach([ # Set default configuration (for admin prefixes) #=============================================================================== foreach([ + 'ADMIN.CATEGORY.LIST_SIZE' => 12, # for 1/2/3-column grid layout 'ADMIN.PAGE.LIST_SIZE' => 12, # for 1/2/3-column grid layout 'ADMIN.POST.LIST_SIZE' => 12, # for 1/2/3-column grid layout 'ADMIN.USER.LIST_SIZE' => Application::get('USER.LIST_SIZE'), diff --git a/core/functions.php b/core/functions.php index dadaab8..ea4b7eb 100644 --- a/core/functions.php +++ b/core/functions.php @@ -1,8 +1,9 @@ findWithParents($Category->getID()) as $Category) { + $category_data = generateItemTemplateData($Category); + $category_list[] = $category_data; + } + + $Template = TemplateFactory::build('category/item'); + $Template->set('IS_ROOT', $is_root); + $Template->set('CATEGORY', $category_data ?? []); + $Template->set('CATEGORIES', $category_list ?? []); + $Template->set('COUNT', [ + 'POST' => $PostRepository->getCountByCategory($Category), + 'CHILDREN' => $CategoryRepository->getChildrenCount($Category) + ]); + + return $Template; +} + #=============================================================================== # Helper function to reduce duplicate code #=============================================================================== @@ -60,9 +92,18 @@ function generatePageItemTemplate(Page $Page, User $User): Template { # Helper function to reduce duplicate code #=============================================================================== function generatePostItemTemplate(Post $Post, User $User): Template { + $CategoryRepository = Application::getRepository('Category'); + + foreach($CategoryRepository->findWithParents($Post->get('category')) as $Category) { + $category_data = generateItemTemplateData($Category); + $categories[] = $category_data; + } + $Template = TemplateFactory::build('post/item'); $Template->set('POST', generateItemTemplateData($Post)); $Template->set('USER', generateItemTemplateData($User)); + $Template->set('CATEGORY', $category_data ?? []); + $Template->set('CATEGORIES', $categories ?? []); return $Template; } @@ -111,6 +152,24 @@ function generateItemTemplateData(EntityInterface $Entity): array { ]; } +#=============================================================================== +# Generate a nested tree from a category data array +#=============================================================================== +function generateCategoryDataTree(array $category_data, $root = 0): array { + foreach($category_data as &$category){ + $tree[intval($category['PARENT'])][] = &$category; + unset($category['PARENT']); + } + + foreach($category_data as &$category){ + if (isset($tree[$category['ID']])){ + $category['CHILDS'] = $tree[$category['ID']]; + } + } + + return $tree[$root] ?? []; +} + #=============================================================================== # Generate pseudo GUID for entity #=============================================================================== @@ -166,6 +225,9 @@ function parseContentTags(string $text): string { #=============================================================================== function parseEntityContent(EntityInterface $Entity): string { switch($class = get_class($Entity)) { + case 'ORM\Entities\Category': + $prefix = 'CATEGORY'; + break; case 'ORM\Entities\Page': $prefix = 'PAGE'; break; diff --git a/core/include/category/list.php b/core/include/category/list.php new file mode 100644 index 0000000..9bd2a68 --- /dev/null +++ b/core/include/category/list.php @@ -0,0 +1,69 @@ +getCount(['parent' => NULL]); +$count = $CategoryRepository->getCount(); +$lastSite = ceil($count / $site_size); + +$currentSite = HTTP::GET('site') ?? 1; +$currentSite = intval($currentSite); + +if($currentSite < 1 OR ($currentSite > $lastSite AND $lastSite > 0)) { + Application::error404(); +} + +#=============================================================================== +# Single redirect +#=============================================================================== +if(Application::get('CATEGORY.SINGLE_REDIRECT') === TRUE AND $count === 1) { + $Category = $CategoryRepository->getLast(); + HTTP::redirect(Application::getEntityURL($Category)); +} + +#=============================================================================== +# Get paginated category list +#=============================================================================== +$categories = $CategoryRepository->getPaginatedTree( + $site_size, + ($currentSite-1) * $site_size +); + +foreach($categories as $Category) { + $templates[] = generateCategoryItemTemplate($Category, TRUE); +} + +#=============================================================================== +# Build document +#=============================================================================== +$ListTemplate = Template\Factory::build('category/list'); +$ListTemplate->set('PAGINATION', [ + 'THIS' => $currentSite, + 'LAST' => $lastSite, + 'HTML' => generateCategoryNaviTemplate($currentSite) +]); +$ListTemplate->set('LIST', [ + 'CATEGORIES' => $templates ?? [] +]); + +$MainTemplate = Template\Factory::build('main'); +$MainTemplate->set('HTML', $ListTemplate); +$MainTemplate->set('HEAD', [ + 'NAME' => $Language->text('title_category_overview', $currentSite) +]); + +echo $MainTemplate; diff --git a/core/include/category/main.php b/core/include/category/main.php new file mode 100644 index 0000000..57f8625 --- /dev/null +++ b/core/include/category/main.php @@ -0,0 +1,134 @@ +findWithParentsBy('slug', $param)) { + if($categories = $CategoryRepository->findWithParents($param)) { + $redirect_scheduled = TRUE; + } + } +} + +else { + if(!$categories = $CategoryRepository->findWithParents($param)) { + if($categories = $CategoryRepository->findWithParentsBy('slug', $param)) { + $redirect_scheduled = TRUE; + } + } +} + +#=============================================================================== +# Throw 404 error if category (with parents) could not be found +#=============================================================================== +if(!isset($categories)) { + Application::error404(); +} + +#=============================================================================== +# The last element represents the current category +#=============================================================================== +$Category = $categories[array_key_last($categories)]; + +#=============================================================================== +# If category with parents was found by alternative, redirect to correct URL +#=============================================================================== +if(isset($redirect_scheduled)) { + HTTP::redirect(Application::getEntityURL($Category)); +} + +#=============================================================================== +# Generate category template data (including parents) +#=============================================================================== +foreach($categories as $_Category) { + $category_list[] = generateItemTemplateData($_Category); +} + +#=============================================================================== +# Define data variable for current category +#=============================================================================== +$category_data = $category_list[array_key_last($category_list)]; + +#=============================================================================== +# Generate category children list +#=============================================================================== +$child_categories = $CategoryRepository->getAll( + ['parent' => $Category->getID()], + Application::get('CATEGORY.LIST_SORT') +); + +foreach($child_categories as $ChildCategory) { + $child_templates[] = generateCategoryItemTemplate($ChildCategory); +} + +#=============================================================================== +# Pagination (for posts in this category) +#=============================================================================== +$site_size = Application::get('POST.LIST_SIZE'); +$site_sort = Application::get('POST.LIST_SORT'); + +$count = $PostRepository->getCountByCategory($Category); +$lastSite = ceil($count / $site_size); + +$currentSite = HTTP::GET('site') ?? 1; +$currentSite = intval($currentSite); + +if($currentSite < 1 OR ($currentSite > $lastSite AND $lastSite > 0)) { + Application::error404(); +} + +#=============================================================================== +# Get paginated post list for this category +#=============================================================================== +$posts = $PostRepository->getAll( + ['category' => $Category->getID()], + $site_sort, + ($currentSite-1) * $site_size.','.$site_size +); + +foreach($posts as $Post) { + $User = $UserRepository->find($Post->get('user')); + $post_templates[] = generatePostItemTemplate($Post, $User); +} + +#=============================================================================== +# Build document +#=============================================================================== +$CategoryTemplate = Template\Factory::build('category/main'); +$CategoryTemplate->set('CATEGORY', $category_data); +$CategoryTemplate->set('CATEGORIES', $category_list ?? []); +$CategoryTemplate->set('COUNT', [ + 'POST' => $PostRepository->getCountByCategory($Category), + 'CHILDREN' => $CategoryRepository->getChildrenCount($Category) +]); +$CategoryTemplate->set('PAGINATION', [ + 'THIS' => $currentSite, + 'LAST' => $lastSite, + 'HTML' => generatePostNaviTemplate($currentSite) +]); +$CategoryTemplate->set('LIST', [ + 'POSTS' => $post_templates ?? [], + 'CATEGORIES' => $child_templates ?? [] +]); + +$MainTemplate = Template\Factory::build('main'); +$MainTemplate->set('TYPE', 'CATEGORY'); +$MainTemplate->set('CATEGORY', $category_data); +$MainTemplate->set('CATEGORIES', $category_list ?? []); +$MainTemplate->set('HTML', $CategoryTemplate); +$MainTemplate->set('HEAD', [ + 'NAME' => $category_data['ATTR']['NAME'], + 'DESC' => description($category_data['BODY']['HTML'](), + Application::get('CATEGORY.DESCRIPTION_SIZE')), + 'PERM' => $category_data['URL'], + 'OG_IMAGES' => $category_data['FILE']['LIST'] +]); + +echo $MainTemplate; diff --git a/core/include/post/main.php b/core/include/post/main.php index 86008f6..bfccc7b 100644 --- a/core/include/post/main.php +++ b/core/include/post/main.php @@ -2,6 +2,7 @@ #=============================================================================== # Get repositories #=============================================================================== +$CategoryRepository = Application::getRepository('Category'); $PostRepository = Application::getRepository('Post'); $UserRepository = Application::getRepository('User'); @@ -49,12 +50,28 @@ if($NextPost = $PostRepository->findNext($Post)) { $post_data['NEXT'] = generateItemTemplateData($NextPost); } +#=============================================================================== +# Generate category template data (including parents) +#=============================================================================== +foreach($CategoryRepository->findWithParents($Post->get('category')) as $Category) { + $category_list[] = generateItemTemplateData($Category); +} + +#=============================================================================== +# Define data variable for current category +#=============================================================================== +if(isset($category_list)) { + $category_data = $category_list[array_key_last($category_list)]; +} + #=============================================================================== # Build document #=============================================================================== $PostTemplate = Template\Factory::build('post/main'); $PostTemplate->set('POST', $post_data); $PostTemplate->set('USER', $user_data); +$PostTemplate->set('CATEGORY', $category_data ?? []); +$PostTemplate->set('CATEGORIES', $category_list ?? []); $MainTemplate = Template\Factory::build('main'); $MainTemplate->set('TYPE', 'POST'); diff --git a/core/namespace/Application.php b/core/namespace/Application.php index 2329253..5690841 100644 --- a/core/namespace/Application.php +++ b/core/namespace/Application.php @@ -140,6 +140,13 @@ class Application { return "{$prot}://{$host}/{$base}{$more}"; } + #=============================================================================== + # Return absolute category URL + #=============================================================================== + public static function getCategoryURL($more = ''): string { + return self::getURL(self::get('CATEGORY.DIRECTORY')."/{$more}"); + } + #=============================================================================== # Return absolute post URL #=============================================================================== @@ -188,6 +195,9 @@ class Application { #=============================================================================== public static function getEntityURL(EntityInterface $Entity) { switch($class = get_class($Entity)) { + case 'ORM\Entities\Category': + $attr = self::get('CATEGORY.SLUG_URLS') ? 'slug' : 'id'; + return self::getCategoryURL($Entity->get($attr).'/'); case 'ORM\Entities\Page': $attr = self::get('PAGE.SLUG_URLS') ? 'slug' : 'id'; return self::getPageURL($Entity->get($attr).'/'); diff --git a/core/namespace/ORM/Entities/Category.php b/core/namespace/ORM/Entities/Category.php new file mode 100644 index 0000000..17560a7 --- /dev/null +++ b/core/namespace/ORM/Entities/Category.php @@ -0,0 +1,14 @@ +findWithParentsBy('id', $id); + } + + #=============================================================================== + # Find category with parents based on specific field comparison + #=============================================================================== + public function findWithParentsBy(string $field, $value): array { + $query = 'WITH RECURSIVE tree AS ( + SELECT *, 0 AS _depth FROM %s WHERE %s %s UNION + SELECT c.*, _depth+1 FROM %s c, tree WHERE tree.parent = c.id + ) SELECT * FROM tree ORDER BY _depth DESC'; + + $table = static::getTableName(); + $check = is_null($value) ? 'IS NULL': '= ?'; + $query = sprintf($query, $table, $field, $check, $table); + + $Statement = $this->Database->prepare($query); + $Statement->execute([$value]); + + # TODO: Virtual column _depth shall not be fetched into the entity class + if($entities = $Statement->fetchAll($this->Database::FETCH_CLASS, static::getClassName())) { + $this->storeMultipleInstances($entities); + return $entities; + } + + return []; + } + + #=============================================================================== + # Get paginated category tree list + #=============================================================================== + public function getPaginatedTree(int $limit, int $offset = 0): array { + $query = 'WITH RECURSIVE tree AS ( + SELECT *, name AS _depth FROM %s WHERE parent IS NULL UNION + SELECT c.*, CONCAT(_depth, "/", c.name) AS _depth FROM %s c INNER JOIN tree ON tree.id = c.parent + ) SELECT * FROM tree ORDER BY _depth %s'; + + $_limit = "LIMIT $limit"; + + if($offset) { + $_limit = "LIMIT $offset,$limit"; + } + + $table = static::getTableName(); + $query = sprintf($query, $table, $table, $_limit); + + $Statement = $this->Database->prepare($query); + $Statement->execute(); + + if($entities = $Statement->fetchAll($this->Database::FETCH_CLASS, static::getClassName())) { + $this->storeMultipleInstances($entities); + return $entities; + } + + return []; + } + + #=============================================================================== + # Get children count of $Category + #=============================================================================== + public function getChildrenCount(CategoryEntity $Category): int { + $query = 'WITH RECURSIVE tree AS ( + SELECT * FROM %s WHERE id = ? UNION + SELECT c.* FROM %s c, tree WHERE tree.id = c.parent + ) SELECT COUNT(id) FROM tree WHERE id != ?'; + + $query = sprintf($query, + static::getTableName(), + static::getTableName() + ); + + $Statement = $this->Database->prepare($query); + $Statement->execute([$Category->getID(), $Category->getID()]); + + return $Statement->fetchColumn(); + } + + #=============================================================================== + # Update category (and check for parent/child circular loops) + #=============================================================================== + public function update(EntityInterface $Entity): bool { + # Entity parent might have changed *in memory*, so we re-fetch the original + # parent of the entity from the database and save it in a variable. + # TODO: Repository/Entity class should have a mechanism to detect changes! + $query = 'SELECT parent FROM %s WHERE id = ?'; + $query = sprintf($query, static::getTableName()); + + $Statement = $this->Database->prepare($query); + $Statement->execute([$Entity->getID()]); + + $parent = $Statement->fetchColumn(); + + # If parent is unchanged, circular loop check is not needed. + if($parent === $Entity->get('parent')) { + return parent::update($Entity); + } + + $query = 'SELECT parent FROM %s WHERE id = ?'; + $query = sprintf($query, static::getTableName()); + + $Statement = $this->Database->prepare($query); + $_parent = $Entity->get('parent'); + + # Fetch the parent of the *new* parent category and let the while loop run through + # the tree until either a parent of "NULL" was found or if the new parent category + # is a *child* of the *current* category which would cause a circular loop. + while($Statement->execute([$_parent]) && $_parent = $Statement->fetchColumn()) { + if($_parent == $Entity->get('id')) { + # Set parent of the *new* parent category to the *original* parent category + # of the *current* category (one level up) to prevent a circular loop. + $query = 'UPDATE %s SET parent = ? WHERE id = ?'; + $query = sprintf($query, static::getTableName()); + + $UpdateStatement = $this->Database->prepare($query); + $UpdateStatement->execute([$parent, $Entity->get('parent')]); + break; + } else if($_parent === NULL) { + break; + } + } + + return parent::update($Entity); + } +} diff --git a/core/namespace/ORM/Repositories/Post.php b/core/namespace/ORM/Repositories/Post.php index 8eac12f..d6328e6 100644 --- a/core/namespace/ORM/Repositories/Post.php +++ b/core/namespace/ORM/Repositories/Post.php @@ -2,6 +2,7 @@ namespace ORM\Repositories; use ORM\Repository; use ORM\Entities\User; +use ORM\Entities\Category; class Post extends Repository { public static function getTableName(): string { return 'post'; } @@ -16,4 +17,15 @@ class Post extends Repository { return $Statement->fetchColumn(); } + + # TODO: This only gets the count of the direct category, not its children + public function getCountByCategory(Category $Category): int { + $query = 'SELECT COUNT(id) FROM %s WHERE category = ?'; + $query = sprintf($query, static::getTableName()); + + $Statement = $this->Database->prepare($query); + $Statement->execute([$Category->getID()]); + + return $Statement->fetchColumn(); + } } -- cgit v1.2.3