过滤扩展库 TYPO3 前端插件中的选定类别和子类别

Question

我正在使用TYPO3 8.7。

我有一个扩展程序，可以列出事件。在列表插件设置中，编校器选择类别。他希望所有分配了选定类别或其子类别的事件都显示在前端。

我一直在阅读有关 docs.typo3.org 的文档，并一直在查看类CategoryCollection。据我了解，这个类可以帮助我获取某个记录的关键字，但无助于通过关键字选择记录。

我还想过滤子类别，因为它使扩展的处理变得更加容易。让我们假设以下事件类别：

• 学习课程
  • 硕士学位
  • 巴凯勒学位
• 训练
  • 内部培训
  • 外部培训

后端编辑器希望可以选择显示内部培训或所有培训，而无需显式激活每个事件的父类别。

在extbase 存储库中筛选类别及其子类别以在前端显示记录列表的正确方法是什么？

我是否必须手动实现逻辑来筛选类别和子类别？

Answer 1

我的解决方案如下所示：

<?php
namespace SnowflakeEventsDomainRepository;
/***************************************************************
*  Copyright notice
*
*  (c) 2018 snowflake productions gmbh <[e-mail]>
*  All rights reserved
*
*  This script is part of the TYPO3 project. The TYPO3 project is
*  free software; you can redistribute it and/or modify
*  it under the terms of the GNU General Public License as published by
*  the Free Software Foundation; either version 2 of the License, or
*  (at your option) any later version.
*
*  The GNU General Public License can be found at
*  http://www.gnu.org/copyleft/gpl.html.
*
*  This script is distributed in the hope that it will be useful,
*  but WITHOUT ANY WARRANTY; without even the implied warranty of
*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*  GNU General Public License for more details.
*
*  This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/
use TYPO3CMSCoreDatabaseConnectionPool;
use TYPO3CMSCoreDatabaseQueryQueryBuilder;
use TYPO3CMSCoreUtilityGeneralUtility;
use TYPO3CMSExtbasePersistenceRepository;
class EventRepository extends Repository {
/**
* @param $categories
* @return array|TYPO3CMSExtbasePersistenceQueryResultInterface
* @throws TYPO3CMSExtbasePersistenceExceptionInvalidQueryException
*/
public function findByCategoryFilter($categories)
{
$categories = array_map('intval', explode(',', $categories));
$categories = $this->expandCategories($categories);
$uids = $this->getUidsByCategories($categories);
if (count($uids) == 0)
return array();
$query = $this->createQuery();
$query->matching(
$query->in('uid', $uids)
);
return $query->execute(true);
}
/**
* Return the categories and all subcategories (recursive)
*
* @param $categories
* @return array
*/
private function expandCategories($categories)
{
// get all categories from database
/** @var QueryBuilder $queryBuilder */
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
->getQueryBuilderForTable('sys_category');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid', 'parent')
->from('sys_category');
$allCategoriesFromDatabase = $queryBuilder->execute()->fetchAll();
// index the categories by parent
$categoriesByParent = array();
foreach($allCategoriesFromDatabase as $categoryFromDatabase) {
$categoriesByParent[(int)$categoryFromDatabase['parent']][] = (int)$categoryFromDatabase['uid'];
}
// expand the categories to all subcategories
$categoriesToExpand = $categories;
$expandedCategories = $categories;
while(count($categoriesToExpand) > 0) {
$currentSubCategories = array();
foreach($categoriesToExpand as $category) {
foreach ($categoriesByParent[$category] as $subCategory) {
$currentSubCategories[] = $subCategory;
}
}
$categoriesToExpand = array_diff($currentSubCategories, $expandedCategories);
$expandedCategories = array_unique(array_merge($expandedCategories, $currentSubCategories));
}
return $expandedCategories;
}
/**
* This is a workaround because
*
*  $query = $this->createQuery();
*     $query->matching(
*     $query->contains('category', $categories)
*  );
*  return $query->execute(true);
*
* generate a useless SQL query (equals instead of in, see first WHERE condition in subquery)
*
* SELECT `tx_events_domain_model_event`.*
* FROM `tx_events_domain_model_event` `tx_events_domain_model_event`
* WHERE (`tx_events_domain_model_event`.`uid` IN
*   (SELECT `uid_foreign`
*   FROM `sys_category_record_mm`
*   WHERE (`uid_local` = '1,3,5,4,6')
*   AND ((`sys_category_record_mm`.`tablenames` = 'tx_events_domain_model_event')
*   AND (`sys_category_record_mm`.`fieldname` = 'category'))))
*   AND (`tx_events_domain_model_event`.`sys_language_uid` IN (0, -1))
*   AND (`tx_events_domain_model_event`.`pid` = 161)
*   AND ((`tx_events_domain_model_event`.`deleted` = 0)
*   AND (`tx_events_domain_model_event`.`hidden` = 0)
*   AND (`tx_events_domain_model_event`.`starttime` <= 1516715340)
*   AND (
*     (`tx_events_domain_model_event`.`endtime` = 0)
*     OR (`tx_events_domain_model_event`.`endtime` > 1516715340)))
*
* @param $categories
* @return array
*/
private function getUidsByCategories($categories) {
$result = array();
/** @var QueryBuilder $queryBuilder */
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
->getQueryBuilderForTable('sys_category_record_mm');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
->select('uid_foreign')
->from('sys_category_record_mm')
->where(
$queryBuilder->expr()->andX(
$queryBuilder->expr()->in('uid_local', $categories),
$queryBuilder->expr()->eq('tablenames', ''tx_events_domain_model_event''),
$queryBuilder->expr()->eq('fieldname', ''category'')
)
);
$records = $queryBuilder->execute()->fetchAll();
foreach($records as $record) {
$result[] = $record['uid_foreign'];
}
return $result;
}
}