How to get DB field from related Table with GPvar using Typoscript

Question

I'm using TYPO3 9.5 and want to get a field from a related table using Typoscript. I've tried different variation but non is working.

This is my last and from my point of view most promising approach that didn't work. I hope it at least helps to make clear what I'm aiming for:

1 = LOAD_REGISTER
1.param = TEXT
1.param.dataWrap = DB : tx_hproducts_domain_model_product:{GP:tx_hproducts_hproduct|id}:relation
1.param.wrap3 = {|}
1.param.insertData = 1
2 = CONTENT
2.table = tx_hproducts_domain_model_related
2.select {
  pidInList = 43
  orderBy = sorting
  where = uid = {REGISTER:param}
  where.insertData = 1
}
2.renderObj = COA
2.renderObj {
  10 = TEXT
  10.stdWrap.field = name
}

So in fact I get the procuts ID as a GetVar, select the ID of the record from the related DB. I a second step I want to select the name of the record from the related table.

Hope it's not to confusion and there is a solution for that.

Answer 1

Use PHP, life will be better ;)

Assuming your vendor name is LUFI and extension key is extkey it can be something like this (of course I do not try to use your tables, instead getting some tt_content rows by hardcoded uid):

typo3conf/ext/extkey/Classes/AdditionalHeaders.php

<?php
namespace LUFI\Extkey;

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Utility\DebuggerUtility;

class AdditionalHeaders
{
    public function addHeaders()
    {
        /** @var \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder */
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tt_content');

        $queryBuilder
            ->getRestrictions()
            ->removeAll();

        $res = $queryBuilder
            ->select('uid', 'pid', 'header')
            ->from('tt_content')
            ->where($queryBuilder
                ->expr()
                ->orX(
                    $queryBuilder->expr()->eq(
                        'pid',
                        $queryBuilder->createNamedParameter('12', \PDO::PARAM_INT)
                    ),
                    $queryBuilder->expr()->eq(
                        'pid',
                        $queryBuilder->createNamedParameter('13', \PDO::PARAM_INT)
                    ),
                )
            )
            ->orderBy('sorting', 'ASC');

            // DebuggerUtility::var_dump($res->getSQL(), 'SQL', 8, false);
            // DebuggerUtility::var_dump($res->getParameters(), 'parameters');

        $statement = $res->execute();

        /** @var \TYPO3\CMS\Frontend\Controller\TypoScriptFrontendController $tsfe */
        $tsfe = $GLOBALS['TSFE'];

        while ($row = $statement->fetch()) {
            // make sure the index for additionalHeaderData is unique ie by adding UID of record
            $tsfe->additionalHeaderData['tt_content_meta' . $row['uid']] = '<meta name="some-header" content="' . $row['header'] . '"/>';
        }
        return null;
    }
}

In Setup of your TypoScript Template:

page.9999 = USER
page.9999 {
  userFunc = LUFI\Extkey\AdditionalHeaders->addHeaders
}