RecordsXmlSitemapDataProvider with sql-join support for categories

RecordsXmlSitemap.php

<?php
declare(strict_types = 1);

namespace Brainworxx\NewsSitemap;

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Seo\XmlSitemap\Exception\MissingConfigurationException;
use TYPO3\CMS\Seo\XmlSitemap\RecordsXmlSitemapDataProvider;

class RecordsXmlSitemap extends RecordsXmlSitemapDataProvider
{

    /**
     * Pretty much copy pasta of the original
     *
     * @throws \TYPO3\CMS\Core\Context\Exception\AspectNotFoundException
     * @throws \TYPO3\CMS\Seo\XmlSitemap\Exception\MissingConfigurationException
     */
    public function generateItems(): void
    {
        $table = $this->config['table'];

        if (empty($table)) {
            throw new MissingConfigurationException(
                'No configuration found for sitemap ' . $this->getKey(),
                1535576053
            );
        }

        $pids = !empty($this->config['pid']) ? GeneralUtility::intExplode(',', $this->config['pid']) : [];
        $lastModifiedField = $this->config['lastModifiedField'] ?? 'tstamp';
        $sortField = $this->config['sortField'] ?? 'sorting';

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

        $constraints = [];
        if (!empty($GLOBALS['TCA'][$table]['ctrl']['languageField'])) {
            $constraints[] = $queryBuilder->expr()->in(
                $GLOBALS['TCA'][$table]['ctrl']['languageField'],
                [
                    -1, // All languages
                    $this->getLanguageId()  // Current language
                ]
            );
        }

        if (!empty($pids)) {
            $recursiveLevel = isset($this->config['recursive']) ? (int)$this->config['recursive'] : 0;
            if ($recursiveLevel) {
                $newList = [];
                foreach ($pids as $pid) {
                    $list = $this->cObj->getTreeList($pid, $recursiveLevel);
                    if ($list) {
                        $newList = array_merge($newList, explode(',', $list));
                    }
                }
                $pids = array_merge($pids, $newList);
            }

            $constraints[] = $queryBuilder->expr()->in('pid', $pids);
        }

        if (!empty($this->config['additionalWhere'])) {
            $constraints[] = $this->config['additionalWhere'];
        }

        $queryBuilder->select('*')
            ->from($table);

        // Edit start
        if (!empty($this->config['join'])) {
            $queryBuilder->join(
                $table,
                $this->config['join']['left'],
                $this->config['join']['alias'],
                $queryBuilder->expr()->eq(
                    $this->config['join']['on']['left'],
                    $queryBuilder->quoteIdentifier($this->config['join']['on']['right'])
                )
            );
        }
        // Edit end.

        if (!empty($constraints)) {
            $queryBuilder->where(
                ...$constraints
            );
        }

        $rows = $queryBuilder->orderBy($sortField)
            ->execute()
            ->fetchAll();

        foreach ($rows as $row) {
            $this->items[] = [
                'data' => $row,
                'lastMod' => (int)$row[$lastModifiedField]
            ];
        }
    }
}

setup.txt

plugin.tx_seo.config.xmlSitemap.sitemaps.news {
    provider = Brainworxx\NewsSitemap\RecordsXmlSitemap
    config {
        # The table in the DB
        table = tx_news_domain_model_news
        # The folder id, where we store our stuff
        pid = 50
        url {
            # The details page id
            pageId = 56
            # are we using cache hash (0/1)
            useCacheHash = 0

            # URL mapping
            fieldToParameterMap {
                uid = tx_news_pi1[news]
            }

            additionalGetParameters {
                tx_news_pi1.controller = News
                tx_news_pi1.action = detail
            }
        }
        join {
            left = sys_category_record_mm
            alias = cats
            on {
                left = cats.uid_foreign
                right = tx_news_domain_model_news.uid
            }
        }
        # Last modified field
        # lastModifiedField =
        # The sort field to use.
        sortField = crdate
        # Going  through the records rcursively?
        recursive = 0
        # Any additional where stuff for the SQL where clause?
        # The 1 is the uid of the category we want to filter.
        additionalWhere  = cats.uid_local = 1
    }
}

The sitemap will be available here:
www.yoursite.xx/?type=1533906435

If you use a routeEnhancers PageTypeSuffix, you must map the pagetype there. If you do not map it, the pagetype will be omitted inside the links of the xml index sitemap, simply leading to the frontend.

A example configuration:

routeEnhancers:

  PageTypeSuffix:
    type: PageType
    default: '/'
    index: ''
    map:
      '.html': 0
      'seo_sitemap': 1533906435

This one does the following things:

1. Add a '/' at the end of any uri
2. Allow '.html' at the end (for legacy reasons)
3. Map the sitemap to www.yoursite.xx/seo_sitemap