问对extbase TYPO3前端插件中选定类别和子类别的筛选

EN

<?php
namespace Snowflake\Events\Domain\Repository;

/***************************************************************
*  Copyright notice
*
*  (c) 2018 snowflake productions gmbh <support@snowflake.ch>
*  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 TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Persistence\Repository;

class EventRepository extends Repository {

    /**
     * @param $categories
     * @return array|\TYPO3\CMS\Extbase\Persistence\QueryResultInterface
     * @throws \TYPO3\CMS\Extbase\Persistence\Exception\InvalidQueryException
     */
    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;
    }
}