src/Kunstmaan/NodeBundle/Repository/NodeRepository.php
public function getAllMenuNodes(
        $lang,
        $permission,
        AclNativeHelper $aclNativeHelper,
        $includeHiddenFromNav = false,
        Node $rootNode = null
    ) {
        $connection = $this->_em->getConnection();
        $qb = $connection->createQueryBuilder();
        $databasePlatformName = $connection->getDatabasePlatform()->getName();
        $createIfStatement = function (
            $expression,
            $trueValue,
            $falseValue
        ) use ($databasePlatformName) {
            switch ($databasePlatformName) {
                case 'sqlite':
                    $statement = 'CASE WHEN %s THEN %s ELSE %s END';

                    break;

                default:
                    $statement = 'IF(%s, %s, %s)';
            }

            return sprintf($statement, $expression, $trueValue, $falseValue);
        };

        $sql = <<<SQL
n.id, n.parent_id AS parent, t.url, t.id AS nt_id,
{$createIfStatement('t.weight IS NULL', 'v.weight', 't.weight')} AS weight,
{$createIfStatement('t.title IS NULL', 'v.title', 't.title')} AS title,
{$createIfStatement('t.online IS NULL', '0', 't.online')} AS online,
n.hidden_from_nav AS hidden,
n.ref_entity_name AS ref_entity_name
SQL;

        $qb->select($sql)
            ->from('kuma_nodes', 'n')
            ->leftJoin(
                'n',
                'kuma_node_translations',
                't',
                '(t.node_id = n.id AND t.lang = :lang)'
            )
            ->leftJoin(
                'n',
                'kuma_node_translations',
                'v',
                '(v.node_id = n.id AND v.lang <> :lang)'
            )
            ->where('n.deleted = 0')
            ->addGroupBy('n.id')
            ->addOrderBy('t.weight', 'ASC')
            ->addOrderBy('t.title', 'ASC');

        if (!$includeHiddenFromNav) {
            $qb->andWhere('n.hidden_from_nav <> 0');
        }

        if (!is_null($rootNode)) {
            $qb->andWhere('n.lft >= :left')
                ->andWhere('n.rgt <= :right');
        }

        $permissionDef = new PermissionDefinition(array($permission));
        $permissionDef->setEntity('Kunstmaan\NodeBundle\Entity\Node');
        $permissionDef->setAlias('n');
        $qb = $aclNativeHelper->apply($qb, $permissionDef);

        $stmt = $this->_em->getConnection()->prepare($qb->getSQL());
        $stmt->bindValue(':lang', $lang);
        if (!is_null($rootNode)) {
            $stmt->bindValue(':left', $rootNode->getLeft());
            $stmt->bindValue(':right', $rootNode->getRight());
        }
        $stmt->execute();

        return $stmt->fetchAll();
    }
public function getAllMenuNodes(
        $lang,
        $permission,
        AclNativeHelper $aclNativeHelper,
        $includeHiddenFromNav = false,
        Node $rootNode = null
    ) {
        $connection = $this->_em->getConnection();
        $qb = $connection->createQueryBuilder();
        $databasePlatformName = $connection->getDatabasePlatform()->getName();
        $createIfStatement = function (
            $expression,
            $trueValue,
            $falseValue
        ) use ($databasePlatformName) {
            switch ($databasePlatformName) {
                case 'sqlite':
                    $statement = 'CASE WHEN %s THEN %s ELSE %s END';

                    break;

                default:
                    $statement = 'IF(%s, %s, %s)';
            }

            return sprintf($statement, $expression, $trueValue, $falseValue);
        };

        $sql = <<<SQL
n.id, n.parent_id AS parent, t.url, t.id AS nt_id,
{$createIfStatement('t.weight IS NULL', 'v.weight', 't.weight')} AS weight,
{$createIfStatement('t.title IS NULL', 'v.title', 't.title')} AS title,
{$createIfStatement('t.online IS NULL', '0', 't.online')} AS online,
n.hidden_from_nav AS hidden,
n.ref_entity_name AS ref_entity_name
SQL;

        $qb->select($sql)
            ->from('kuma_nodes', 'n')
            ->leftJoin(
                'n',
                'kuma_node_translations',
                't',
                '(t.node_id = n.id AND t.lang = :lang)'
            )
            ->leftJoin(
                'n',
                'kuma_node_translations',
                'v',
                '(v.node_id = n.id AND v.lang <> :lang)'
            )
            ->where('n.deleted = 0')
            ->addGroupBy('n.id')
            ->addOrderBy('t.weight', 'ASC')
            ->addOrderBy('t.title', 'ASC');

        if (!$includeHiddenFromNav) {
            $qb->andWhere('n.hidden_from_nav <> 0');
        }

        if (!is_null($rootNode)) {
            $qb->andWhere('n.lft >= :left')
                ->andWhere('n.rgt <= :right');
        }

        $permissionDef = new PermissionDefinition(array($permission));
        $permissionDef->setEntity('Kunstmaan\NodeBundle\Entity\Node');
        $permissionDef->setAlias('n');
        $qb = $aclNativeHelper->apply($qb, $permissionDef);

        $stmt = $this->_em->getConnection()->prepare($qb->getSQL());
        $stmt->bindValue(':lang', $lang);
        if (!is_null($rootNode)) {
            $stmt->bindValue(':left', $rootNode->getLeft());
            $stmt->bindValue(':right', $rootNode->getRight());
        }
        $stmt->execute();

        return $stmt->fetchAll();
    }
public function getAllMenuNodes ( $ lang , $ permission , AclNativeHelper $ aclNativeHelper , $ includeHiddenFromNav = false , Node $ rootNode = null ) { $ connection = $ this -> _em -> getConnection ( ) ; $ qb = $ connection -> createQueryBuilder ( ) ; $ databasePlatformName = $ connection -> getDatabasePlatform ( ) -> getName ( ) ; $ createIfStatement = function ( $ expression , $ trueValue , $ falseValue ) use ( $ databasePlatformName ) { switch ( $ databasePlatformName ) { case 'sqlite' : $ statement = 'CASE WHEN %s THEN %s ELSE %s END' ; break ; default : $ statement = 'IF(%s, %s, %s)' ; } return sprintf ( $ statement , $ expression , $ trueValue , $ falseValue ) ; } ; $ sql =  <<<SQL
n.id, n.parent_id AS parent, t.url, t.id AS nt_id,
{$createIfStatement('t.weight IS NULL', 'v.weight', 't.weight')} AS weight,
{$createIfStatement('t.title IS NULL', 'v.title', 't.title')} AS title,
{$createIfStatement('t.online IS NULL', '0', 't.online')} AS online,
n.hidden_from_nav AS hidden,
n.ref_entity_name AS ref_entity_name
SQL ; $ qb -> select ( $ sql ) -> from ( 'kuma_nodes' , 'n' ) -> leftJoin ( 'n' , 'kuma_node_translations' , 't' , '(t.node_id = n.id AND t.lang = :lang)' ) -> leftJoin ( 'n' , 'kuma_node_translations' , 'v' , '(v.node_id = n.id AND v.lang <> :lang)' ) -> where ( 'n.deleted = 0' ) -> addGroupBy ( 'n.id' ) -> addOrderBy ( 't.weight' , 'ASC' ) -> addOrderBy ( 't.title' , 'ASC' ) ; if ( ! $ includeHiddenFromNav ) { $ qb -> andWhere ( 'n.hidden_from_nav <> 0' ) ; } if ( ! is_null ( $ rootNode ) ) { $ qb -> andWhere ( 'n.lft >= :left' ) -> andWhere ( 'n.rgt <= :right' ) ; } $ permissionDef = new PermissionDefinition ( array ( $ permission ) ) ; $ permissionDef -> setEntity ( 'Kunstmaan\NodeBundle\Entity\Node' ) ; $ permissionDef -> setAlias ( 'n' ) ; $ qb = $ aclNativeHelper -> apply ( $ qb , $ permissionDef ) ; $ stmt = $ this -> _em -> getConnection ( ) -> prepare ( $ qb -> getSQL ( ) ) ; $ stmt -> bindValue ( ':lang' , $ lang ) ; if ( ! is_null ( $ rootNode ) ) { $ stmt -> bindValue ( ':left' , $ rootNode -> getLeft ( ) ) ; $ stmt -> bindValue ( ':right' , $ rootNode -> getRight ( ) ) ; } $ stmt -> execute ( ) ; return $ stmt -> fetchAll ( ) ; }
Get all the information needed to build a menu tree with one query.
We only fetch the fields we need, instead of fetching full objects to
limit the memory usage.

@param string          $lang                 The locale
@param string          $permission           The permission (read,
write, ...)
@param AclNativeHelper $aclNativeHelper      The acl helper
@param bool            $includeHiddenFromNav Include nodes hidden from
navigation or not
@param Node            $rootNode             The root node of the
current site

@return array
Get all the information needed to build a menu tree with one query . We only fetch the fields we need instead of fetching full objects to limit the memory usage .