zend-blog-3-backend

1
<?php
2

3
namespace App\Command;
4

5
use App\Entity\Category;
6
use App\Entity\Comment;
7
use App\Entity\Post;
8
use Doctrine\ORM\EntityManagerInterface;
9
use Symfony\Component\Console\Command\Command;
10
use Symfony\Component\Console\Input\InputInterface;
11
use Symfony\Component\Console\Output\OutputInterface;
12

13
class InitNestedSetTreeCommand extends Command
14
{
15
    /**
16
     * @var EntityManagerInterface
17
     */
18
    private EntityManagerInterface $em;
19

20
    /**
21
     * @param EntityManagerInterface $em
22
     */
23
    public function __construct(EntityManagerInterface $em)
24
    {
25
        parent::__construct();
26

27
        $this->em = $em;
28
        $this->em->getConfiguration()->setSQLLogger(null);
29
    }
30

31
    protected function configure()
32
    {
33
        $this
34
            ->setName('mtt:tree:init')
35
            ->setDescription('Init nested-set tree for category and comments')
36
        ;
37
    }
38

39
    protected function execute(InputInterface $input, OutputInterface $output): int
40
    {
41
        $startTime = microtime(true);
42

43
        $this->handleCategory();
44

45
        $output->writeln('');
46
        $output->writeln('<info>Update category tree</info>');
47

48
        $this->handlePosts($output);
49

50
        $endTime = microtime(true);
51

52
        $output->writeln('');
53
        $output->writeln(
54
            sprintf('<info>Total time: <comment>%s</comment> sec</info>', round($endTime - $startTime, 3))
55
        );
56

57
        return 0;
58
    }
59

60
    private function handleCategory(): void
61
    {
62
        $handled = [];
63

64
        /* @var \App\Repository\CategoryRepository $categoryRepo */
65
        $categoryRepo = $this->em->getRepository(Category::class);
66

67
        $qb = $categoryRepo->createQueryBuilder('c');
68
        $qb->update()
69
            ->set('c.nestedSet.leftKey', ':null')
70
            ->set('c.nestedSet.rightKey', ':null')
71
            ->set('c.nestedSet.depth', 1)
72
            ->setParameter('null', null)
73
            ->getQuery()
74
            ->execute()
75
        ;
76

77
        $qb = $categoryRepo->createQueryBuilder('c');
78
        $qb->orderBy('c.name');
79

80
        /* @var Category[] $categories */
81
        $categories = $qb->getQuery()->getResult();
82
        $idx = 0;
83
        foreach ($categories as $category) {
84
            if (!$category->getParent()) {
85
                $ns = $category->getNestedSet();
86
                $ns
87
                    ->setLeftKey(++$idx)
88
                    ->setRightKey(++$idx)
89
                ;
90

91
                $handled[] = $category->getId();
92
            }
93
        }
94

95
        $this->em->flush();
96

97
        do {
98
            $updateTree = false;
99
            foreach ($categories as $category) {
100
                if (!in_array($category->getId(), $handled)
101
                    && $parent = $category->getParent()
102
                ) {
103
                    if (in_array($parent->getId(), $handled)) {
104
                        $this->em->refresh($parent);
105
                        $nsParent = $parent->getNestedSet();
106

107
                        $categoryRepo->addToTree($category, $nsParent->getRightKey(), $nsParent->getDepth() + 1);
108

109
                        $handled[] = $category->getId();
110
                        $updateTree = true;
111

112
                        break;
113
                    }
114
                }
115
            }
116
        } while ($updateTree);
117
    }
118

119
    /**
120
     * @param OutputInterface $output
121
     */
122
    private function handlePosts(OutputInterface $output): void
123
    {
124
        /* @var \App\Repository\CommentRepository $commentsRepo */
125
        $commentsRepo = $this->em->getRepository(Comment::class);
126

127
        $qb = $commentsRepo->createQueryBuilder('c');
128
        $qb->update()
129
            ->set('c.nestedSet.leftKey', ':null')
130
            ->set('c.nestedSet.rightKey', ':null')
131
            ->set('c.nestedSet.depth', 1)
132
            ->setParameter('null', null)
133
            ->getQuery()
134
            ->execute()
135
        ;
136

137
        $postRepo = $this->em->getRepository(Post::class);
138
        $posts = $postRepo
139
            ->createQueryBuilder('p')
140
            ->select('p.id', 'p.url')
141
            ->innerJoin('p.comments', 'c')
142
            ->groupBy('p.id')
143
            ->getQuery()
144
            ->getArrayResult()
145
        ;
146

147
        foreach ($posts as $post) {
148
            $postId = $post['id'];
149
            $handled = [];
150
            $qb = $commentsRepo->createQueryBuilder('c');
151
            $qb
152
                ->where($qb->expr()->eq('c.post', ':postId'))
153
                ->setParameter('postId', $postId)
154
                ->orderBy('c.id');
155

156
            /* @var Comment[] $comments */
157
            $comments = $qb->getQuery()->getResult();
158
            $idx = 0;
159
            foreach ($comments as $comment) {
160
                if (!$comment->getParent()) {
161
                    $ns = $comment->getNestedSet();
162
                    $ns
163
                        ->setLeftKey(++$idx)
164
                        ->setRightKey(++$idx);
165

166
                    $handled[] = $comment->getId();
167
                }
168
            }
169

170
            $this->em->flush();
171

172
            do {
173
                $updateTree = false;
174
                foreach ($comments as $comment) {
175
                    if (!in_array($comment->getId(), $handled)
176
                        && $parent = $comment->getParent()
177
                    ) {
178
                        if (in_array($parent->getId(), $handled)) {
179
                            $this->em->refresh($parent);
180
                            $nsParent = $parent->getNestedSet();
181

182
                            $commentsRepo->addToTree(
183
                                $comment,
184
                                $nsParent->getRightKey(),
185
                                $nsParent->getDepth() + 1,
186
                                $postId
187
                            );
188

189
                            $handled[] = $comment->getId();
190
                            $updateTree = true;
191

192
                            break;
193
                        }
194
                    }
195
                }
196
            } while ($updateTree);
197

198
            $cnt = count($comments);
199
            $output->writeln(
200
                "<info>Update <comment>{$cnt}</comment> comments for topic: <comment>{$post['url']}</comment></info>"
201
            );
202
        }
203
    }
204
}
205