How to get the selected categories for an CE in the selected language

Question

I've seen Georg Ringer adding the categories trough the controller, I'm trying to get a ViewHelper to do this ...

<?php
namespace Vendor\Extension\ViewHelpers;

use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3Fluid\Fluid\Core\ViewHelper\AbstractViewHelper;

/**
 * will return system categories (sys_category) array of an element
 */
class CategoriesOutputViewHelper extends AbstractViewHelper
{
    protected $escapeOutput = false;

    public function initializeArguments()
    {
        $this->registerArgument('CEUid', 'integer', 'record UID, e.g. of a content element', true);
    }


    /**
     * Get content element registered categories
     */
    protected function getCategories(int $CEUid): ?array
    {
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_category');

        return $queryBuilder
            ->select('sys_category.uid', 'sys_category.title', 'sys_category.shortcut')
            ->from('sys_category')
            ->join(
                'sys_category',
                'sys_category_record_mm',
                'mm',
                $queryBuilder->expr()->eq(
                    'mm.uid_local',
                    'sys_category.uid'
                )
            )
            ->where(
                $queryBuilder->expr()->eq(
                    'mm.uid_foreign',
                    $queryBuilder->createNamedParameter($CEUid, \PDO::PARAM_INT)
                ),
                $queryBuilder->expr()->eq(
                    'mm.tablenames',
                    $queryBuilder->quote('tt_content')
                ),
                $queryBuilder->expr()->eq(
                    'mm.fieldname',
                    $queryBuilder->quote('categories')
                )

            )
            ->execute()
            ->fetchAll();
    }


    /**
     * Do category translation overlay
     */
    public function render()
    {
        $CEUid = $this->arguments['CEUid'];
        $categories = $this->getCategories($CEUid);

        foreach ($categories as $key => $category) {
            $overlaidCategory = $category;

            if ($overlaidCategory !== null){
                $categories[$key] = $overlaidCategory;
            }
        }

        return $categories;
    }
}

obviously in my render function nothing happens, but this is how far I can get things to work, it returns a correct array of categories in the default language ...

In fluid I call it like this (rs being the extensions namespace):

    <f:if condition="{data.categories}">
        <span class="category-list">
            <f:for each="{rs:CategoriesOutput(CEUid: data.uid)}" as="category" iteration="iteration">
                <f:if condition="{category.shortcut}">
                    <f:then>
                        <f:link.typolink parameter="{category.shortcut}">
                            {category.title}
                        </f:link.typolink>
                    </f:then>
                    <f:else>
                        {category.title}
                    </f:else>
                </f:if>
                <f:if condition="!{iteration.isLast}">
                    ::
                </f:if>
            </f:for>
        </span>
    </f:if>

I'm working with V11.5.13 but I think the solution would be the same from V9 and on, thanks for the attention

Answer 1

I thought that somebody would have had an answer ... eventually I worked it out, no need for 'overlay' as I tought in the start, just brougthen the query to include the language, I did it as follows:

<?php
namespace Vendor\Extension\ViewHelpers;

use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3Fluid\Fluid\Core\ViewHelper\AbstractViewHelper;

/**
 * will return the system categories (sys_category) data of an element as a localized array
 */
class CategoriesOutputViewHelper extends AbstractViewHelper
{
    protected $escapeOutput = false;

    public function initializeArguments()
    {
        $this->registerArgument('CEUid', 'integer', 'record UID, e.g. of a content element', true);
        $this->registerArgument('LangId', 'integer', 'language ID of the page', true);
    }


    public function render(): ?array
    {
        $CEUid = $this->arguments['CEUid'];
        $LangId = $this->arguments['LangId'];

        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_category');

        return $queryBuilder
            ->select('sys_category.uid', 'sys_category.title')
            ->from('sys_category')
            ->join(
                'sys_category',
                'sys_category_record_mm',
                'mm',
                $queryBuilder->expr()->or(
                    $queryBuilder->expr()->eq(
                        'mm.uid_local',
                        'sys_category.uid'
                    ),
                    $queryBuilder->expr()->eq(
                        'mm.uid_local',
                        'sys_category.t3_origuid'
                    )
                )
            )
            ->where(
                $queryBuilder->expr()->eq(
                    'mm.uid_foreign',
                    $queryBuilder->createNamedParameter($CEUid, \PDO::PARAM_INT)
                ),
                $queryBuilder->expr()->eq(
                    'mm.tablenames',
                    $queryBuilder->quote('tt_content')
                ),
                $queryBuilder->expr()->eq(
                    'mm.fieldname',
                    $queryBuilder->quote('categories')
                ),
                $queryBuilder->expr()->in(
                    'sys_category.sys_language_uid',
                    $queryBuilder->createNamedParameter($LangId, \PDO::PARAM_INT)
                )
            )
            ->execute()
            ->fetchAll();
    }
}

the JOIN has to include the t3_original field for the translated voices, and the WHERE the sys_language_uid ...

calling it in the fluid-template now has to give also the language like this:

<f:for each="{rs:CategoriesOutput(CEUid: data.uid, LangId: data.sys_language_uid)}" as="category">
  {category.title}
</f:for>

just for proper credit; I started of on the working example here: musikinsnetz.de of Hagen Gebauer, he just had never worked out the localization, thanks