Get data from database and pass it to the template in TYPO3 11.5

Question

Please help me. I need to display the list of the 4 most searched words. I am making a query request to the table "index_stat_word" (indexed_search extension). But the result is empty.

My code is here:

1. ext\my_site_package\Classes\Domain\Repository\MostSearchedRepository.php

<?php

declare(strict_types=1);

namespace Vendor\MySitePackage\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use TYPO3\CMS\Core\Utility\GeneralUtility;

final class MostSearchedRepository
{    
    private ConnectionPool $connectionPool;
    private const TABLE_NAME = 'index_stat_word';

    public function __construct(ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }
    
    public function findMostUsedWords(int $limit = 4): array
    {
        $queryBuilder = $this->connectionPool
        ->getQueryBuilderForTable(self::TABLE_NAME);

        $queryBuilder->getRestrictions()
            ->removeAll()
            ->add(GeneralUtility::makeInstance(DeletedRestriction::class));

        $queryBuilder
            ->select('word', 'COUNT(word) AS word_count')
            ->from(self::TABLE_NAME)
            ->groupBy('word')
            ->orderBy('word_count', 'DESC')
            ->setMaxResults($limit);

        $query = $queryBuilder->executeQuery();       
        
        return $query->fetchAllAssociative();
    }
}

2. ext\my_site_package\Classes\Controller\MostSearchedController.php

<?php

declare(strict_types=1);

namespace Vendor\MySitePackage\Controller;

use Vendor\MySitePackage\Domain\Repository\MostSearchedRepository;
use TYPO3\CMS\Extbase\Mvc\Controller\ActionController;
use TYPO3\CMS\Extbase\Mvc\View\ViewInterface;
use TYPO3Fluid\Fluid\Core\Rendering\RenderingContextInterface;

class MostSearchedController extends ActionController
{
    protected MostSearchedRepository $mostSearchedRepository;

    public function __construct(MostSearchedRepository $mostSearchedRepository)
    {
        $this->mostSearchedRepository = $mostSearchedRepository;
    }

    /**
     * Action to display most searched words
     *
     * @param ViewInterface $view
     * @return void
     */
    public function showMostSearchedWordsAction(RenderingContextInterface $renderingContext)
    {
        $mostSearchedWords = $this->mostSearchedRepository->findMostUsedWords();

        $renderingContext->getVariableProvider()->add(
            'mostSearchedWords',
            $mostSearchedWords
        );
    }
}
?>

3. ext\my_site_package\Resources\Private\Partials\IndexedSearch\Form.html

<ul class="popular-words">
   <f:for each="{mostPopularWords}" as="word">
       <li>{word.word} - {word.word_count}</li>
   </f:for>
</ul>

Thank you for your answers.

Answer 1

Your action looks weird. Try this:

public function showMostSearchedWordsAction(): ResponseInterface
{
    $this->view->assign(
        'mostSearchWords',
        $this->mostSearchedRepository->findMostUsedWords()
    );
    return $this->htmlResponse();
}