TYPO3 Core

Hi there!

What do I need?
I have pages , categories and some of my pages have multiple categories. Actually I try something like Give me all pages with category 46 AND 13.

How I try to get this?
I'm in a middleware which contains a getPages method with the following code.

$pagesQB = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('pages');
$categoriesQB = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_category_record_mm');
$categoriesQB2 = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('sys_category_record_mm');

$categoriesQB
    ->select('uid_foreign')
    ->from('sys_category_record_mm')
    ->where(
        $categoriesQB->expr()->eq('uid_local', $pagesQB->createNamedParameter(46, \PDO::PARAM_INT)),
        $categoriesQB->expr()->eq('sys_category_record_mm.tablenames', $pagesQB->createNamedParameter('pages')),
        $categoriesQB->expr()->eq('sys_category_record_mm.fieldname', $pagesQB->createNamedParameter('categories')),
    );

$categoriesQB2
    ->select('uid_foreign')
    ->from('sys_category_record_mm')
    ->where(
        $categoriesQB2->expr()->eq('uid_local', $pagesQB->createNamedParameter(13, \PDO::PARAM_INT)),
        $categoriesQB2->expr()->eq('sys_category_record_mm.tablenames', $pagesQB->createNamedParameter('pages')),
        $categoriesQB2->expr()->eq('sys_category_record_mm.fieldname', $pagesQB->createNamedParameter('categories')),
    );

$statement = $pagesQB
    ->select('pages.*')
    ->from('pages', 'pages')
    ->where(
        $pagesQB->expr()->in('pages.uid', $pagesQB->createNamedParameter($categoriesQB->getSQL())),
        $pagesQB->expr()->in('pages.uid', $pagesQB->createNamedParameter($categoriesQB2->getSQL()))
    );

/**
 * ERROR LOGGING SART
 * Some dirty stuff toe replace the dcvalues with the correct values in the sql statement.
 */
$sqlParameters = $statement->getParameters();
$sqlStatement = $statement->getSQL();
$sqlSearch = [];
$sqlReplace = [];

foreach ($sqlParameters as $key => $value) {
    array_push($sqlSearch, ':' . $key);
    array_push($sqlReplace, $value);
}

$test = str_replace($sqlSearch, $sqlReplace, $sqlStatement);
$test = str_replace($sqlSearch, $sqlReplace, $test);
$test = str_replace($sqlSearch, $sqlReplace, $test);
$test = str_replace($sqlSearch, $sqlReplace, $test);

error_log(print_r($sqlSearch, true));
error_log(print_r($sqlReplace, true));
error_log(print_r($sqlStatement, true));
error_log(print_r($test, true));

/**
 * ERROR LOGGING END
 */

$statement = $statement->execute();
return $statement->fetchAllAssociative();

The returned array is empty.
When I take the logged SQL Statement and paste it into Sequel I got my correct result.

The SQL Statement looks like this.

SELECT `pages`.* FROM `pages` `pages` WHERE (`pages`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = 46) AND (`sys_category_record_mm`.`tablenames` = 'pages') AND (`sys_category_record_mm`.`fieldname` = 'categories'))) AND (`pages`.`uid` IN (SELECT `uid_foreign` FROM `sys_category_record_mm` WHERE (`uid_local` = 13) AND (`sys_category_record_mm`.`tablenames` = 'pages') AND (`sys_category_record_mm`.`fieldname` = 'categories'))) AND ((`pages`.`deleted` = 0) AND (`pages`.`hidden` = 0) AND (`pages`.`starttime` <= 1636491060) AND ((`pages`.`endtime` = 0) OR (`pages`.`endtime` > 1636491060)))