chatbotbegriffe/Classes/Domain/Repository/TermRepository.php

<?php

  declare(strict_types=1);

  namespace Meramo\Begriffmgt\Domain\Repository;

  use TYPO3\CMS\Core\Database\ConnectionPool;
  use TYPO3\CMS\Core\Utility\GeneralUtility;
  use TYPO3\CMS\Extbase\Persistence\QueryInterface;
  use TYPO3\CMS\Extbase\Persistence\Repository;

  class TermRepository extends Repository
  {
      /**
       * Find one record by 'title'.
       *
       * @return \TYPO3\CMS\Extbase\Persistence\QueryResultInterface|array
       */
      public function findByTitle($term)
      {
          $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_begriffmgt_domain_model_term');
          return $query = $queryBuilder
              ->select('*')
              ->from('tx_begriffmgt_domain_model_term')
              ->where(
                  $queryBuilder->expr()->eq('term', $queryBuilder->createNamedParameter($term, \PDO::PARAM_STR))
              )
              ->setMaxResults(1)
              ->execute()
              ->fetch();
      }

      /**
       * Find all records and order them by 'term' in ascending order.
       *
       * @return \TYPO3\CMS\Extbase\Persistence\QueryResultInterface|array
       */
      public function findAllOrderedByTerm()
      {
          $query = $this->createQuery();
          $query->setOrderings(['term' => QueryInterface::ORDER_ASCENDING]);

          return $query->execute();
      }

      public function findAllTermsByTypeOrCategoryOrSimplyAll($typeTitle= null, $categoryTitle = null) {
          $connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_begriffmgt_domain_model_term');
          $queryBuilder = $connection->createQueryBuilder();
          $queryBuilder
              ->select('term','type.title as type_title', 'category.title as category_title','url.title as url_title')
              ->from('tx_begriffmgt_domain_model_term', 't')
              ->leftJoin('t', 'tx_begriffmgt_domain_model_type', 'type', 't.type = type.uid')
              ->leftJoin('t', 'tx_begriffmgt_domain_model_category', 'category', 't.category = category.uid')
              ->leftJoin('t', 'tx_begriffmgt_domain_model_url', 'url', 't.url = url.uid');
         if($typeTitle && $categoryTitle){
             $queryBuilder
                 ->where($queryBuilder->expr()->eq('type.title', $queryBuilder->createNamedParameter($typeTitle)))
                 ->andWhere($queryBuilder->expr()->eq('category.title', $queryBuilder->createNamedParameter($categoryTitle)));
         }
          if(!$typeTitle && $categoryTitle){
              $queryBuilder
                  ->where($queryBuilder->expr()->eq('category.title', $queryBuilder->createNamedParameter($categoryTitle)));
          }
          if($typeTitle && !$categoryTitle){
              $queryBuilder
                  ->where($queryBuilder->expr()->eq('type.title', $queryBuilder->createNamedParameter($typeTitle)));
          }
          $results = $queryBuilder->execute();
          if(!$typeTitle && !$categoryTitle){
              $terms = $this->buildTermsEachToArray($results);
          } else {
              $terms = $this->buildTermsAggregatedToArray($results);
          }
          return $terms;
      }

      public function buildTermsEachToArray($results){
          $terms = array();
          foreach ($results as $res){
              array_push($terms, ['type'=>$res['type_title'],'category'=>$res['category_title'],'term'=>$res['term'], 'url'=>$res['url_title']]);
          }
          return $terms;
      }

      public function buildTermsAggregatedToArray($results){
          $terms = [];
          $response = [];
          foreach ($results as $res){
              array_push($terms, $res['term']);
          }
          array_push($response, ['type'=>$res['type_title'],'category'=>$res['category_title'], 'terms'=>$terms, 'url'=>$res['url_title']]);
          return $response;
      }
  }