kupshop/bundles/KupShop/ContentBundle/Util/MenuLinksPage.php

<?php

namespace KupShop\ContentBundle\Util;

use Doctrine\ORM\EntityManagerInterface;
use KupShop\ContentBundle\Entity\Page;
use KupShop\I18nBundle\Translations\MenuLinksTranslation;
use Query\Operator;
use Query\QueryBuilder;
use Query\Translation;

class MenuLinksPage
{
    private const MAX_RECURSIVE_DEPTH = 50;

    private $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function getPages(?int $parentId = null): array
    {
        $qb = $this->getBasePageQueryBuilder()
            ->andWhere(Operator::equalsNullable(['ml.parent' => $parentId]))
            ->orderBy('list_order', 'ASC');

        $pages = [];
        foreach ($qb->execute() as $item) {
            $pages[$item['id']] = $this->createPageEntity($item);
            $pages[$item['id']]->setChildren(
                $this->getPages($item['id'])
            );
        }

        return $pages;
    }

    public function getPage(int $menuLinkId): ?Page
    {
        if (!($page = $this->getBasePageQueryBuilder()
            ->andWhere(Operator::equals(['ml.id' => $menuLinkId]))
            ->execute()->fetchAssociative())) {
            return null;
        }

        return $this->createPageEntity($page);
    }

    public function createPageEntity(array $page, bool $withBlock = true): Page
    {
        $pageEntity = new Page();
        $pageData = array_replace_recursive(json_decode($page['data'], true) ?: [], json_decode($page['data_translated'], true) ?: []);
        $pageEntity->setId($page['id'])
            ->setType((int) $page['type'])
            ->setParentId($page['parent'] ? (int) $page['parent'] : null)
            ->setName($page['name'])
            ->setNameShort($page['name_short'])
            ->setTemplate($page['template'])
            ->setMetaTitle($page['meta_title'] ?: '')
            ->setMetaKeywords($page['meta_keywords'] ?: '')
            ->setMetaDescription($page['meta_description'] ?: '')
            ->setOldIdPage($page['old_id_page'] ?: '')
            ->setFigure($page['figure'])
            ->setShowInSearch($page['show_in_search'] ?? 'Y')
            ->setUrl($page['url'])
            ->setLink($page['link'])
            ->setTarget($page['target'])
            ->setData($pageData);

        if ($withBlock && !empty($page['id_block'])) {
            $block = $this->entityManager->getRepository(\KupShop\ContentBundle\Entity\Block::class);
            $pageEntity->setBlock($block->find($page['id_block']));
        }

        return $pageEntity;
    }

    protected function getBasePageQueryBuilder(): QueryBuilder
    {
        return sqlQueryBuilder()->select('ml.*')
            ->from('menu_links', 'ml')
            ->andWhere(Translation::coalesceTranslatedFields(MenuLinksTranslation::class,
                function ($columns) {
                    $columns['data'] = 'data_translated';

                    return $columns;
                }));
    }

    public function getPageWithChildren(string $label, bool $withBlocks = true): ?Page
    {
        $pagesResult = $this->createRecursiveQueryBuilder($label)->execute();

        if (!$rawRootPage = $pagesResult->fetchAssociative()) {
            return null;
        }

        $rootPage = $this->createPageEntity($rawRootPage, $withBlocks);
        $pagesIterator = $pagesResult->fetchAllAssociative();

        $this->buildTree([$rootPage->getId() => $rootPage], $pagesIterator, withBlocks: $withBlocks);

        return $rootPage;
    }

    /**
     * @param array<int, Page> $parentPages
     */
    protected function buildTree(array $parentPages, array $restPages, int $depth = 1, bool $withBlocks = true): void
    {
        if (empty($parentPages)) {
            return;
        }

        $currentLevelPages = [];

        $i = 0;
        foreach ($restPages as $pageRow) {
            if ($pageRow['level'] > $depth) {
                $this->buildTree($currentLevelPages, array_slice($restPages, $i), $depth + 1, $withBlocks);

                break;
            }

            $i++;

            $page = $this->createPageEntity($pageRow, $withBlocks);

            $currentLevelPages[$page->getId()] = $page;
            $parentPages[$page->getParentId()]->appendChild($page);
        }
    }

    protected function createRecursiveQueryBuilder(string $label, ?int $depth = null): QueryBuilder
    {
        // language=MariaDB
        $recursiveQuery = <<<__SQL__
            WITH RECURSIVE cte AS (
                SELECT  *,
                        0 as level
                FROM    menu_links
                WHERE   code = :label
                UNION ALL
                SELECT  ml.*,
                        cte.level + 1 AS level
                FROM    menu_links ml
                INNER JOIN cte ON ml.parent = cte.id WHERE level < :depth
            ) SELECT * FROM cte
        __SQL__;

        $qb = sqlQueryBuilder()
            ->select('ml.*')
            ->from("({$recursiveQuery})", 'ml')
            ->setParameter('depth', $depth ?? self::MAX_RECURSIVE_DEPTH)
            ->setParameter('label', $label)
            ->andWhere(Translation::coalesceTranslatedFields(MenuLinksTranslation::class,
                static function ($columns) {
                    $columns['data'] = 'data_translated';

                    return $columns;
                }))
            ->addOrderBy('ml.level')
            ->addOrderBy('ml.list_order');

        return $qb;
    }
}