zend-blog-3-backend

1
<?php
2

3
namespace App\Repository;
4

5
use App\Doctrine\DBAL\Type\MillisecondsDateTime;
6
use App\Entity\Comment;
7
use App\Entity\GeoLocation;
8
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
9
use Doctrine\Persistence\ManagerRegistry;
10

11
/**
12
 * CommentRepository
13
 *
14
 * @method Comment|null findOneByDisqusId($id)
15
 * @method Comment|null find($id, $lockMode = null, $lockVersion = null)
16
 */
17
class CommentRepository extends ServiceEntityRepository
18
{
19
    use ListQueryTrait;
20

21
    /**
22
     * @param ManagerRegistry $registry
23
     */
24
    public function __construct(ManagerRegistry $registry)
25
    {
26
        parent::__construct($registry, Comment::class);
27
    }
28

29
    /**
30
     * @return array
31
     */
32
    public function getUncheckedIps()
33
    {
34
        $qb = $this->createQueryBuilder('c');
35

36
        $qb
37
            ->select('c.ipAddress')
38
            ->leftJoin('c.geoLocation', 'g')
39
            ->where($qb->expr()->orX(
40
                $qb->expr()->isNull('c.geoLocation'),
41
                $qb->expr()->isNull('g.city')
42
            ))
43
            ->andWhere($qb->expr()->isNotNull('c.ipAddress'))
44
            ->groupBy('c.ipAddress')
45
            ->setMaxResults(20)
46
        ;
47

48
        return array_column($qb->getQuery()->getArrayResult(), 'ipAddress');
49
    }
50

51
    /**
52
     * @param GeoLocation $location
53
     * @param string $ip
54
     */
55
    public function updateLocation(GeoLocation $location, string $ip)
56
    {
57
        $qb = $this->createQueryBuilder('c');
58
        $qb
59
            ->update()
60
            ->set('c.geoLocation', ':location')
61
            ->where($qb->expr()->eq('c.ipAddress', ':ip'))
62
            ->setParameter('location', $location->getId())
63
            ->setParameter('ip', $ip)
64
        ;
65

66
        $qb->getQuery()->execute();
67
    }
68

69
    public function updateUserComments(int $userId)
70
    {
71
        $qb = $this->createQueryBuilder('c');
72
        $qb
73
            ->update()
74
            ->set('c.lastUpdate', ':now')
75
            ->where($qb->expr()->eq('c.user', ':id'))
76
            ->setParameter('now', (new \DateTime())->format(MillisecondsDateTime::FORMAT_TIME))
77
            ->setParameter('id', $userId)
78
        ;
79

80
        $qb->getQuery()->execute();
81
    }
82

83
    /**
84
     * @param Comment $entity
85
     * @param int $index
86
     * @param int $depth
87
     * @param int $postId
88
     */
89
    public function addToTree(Comment $entity, int $index, int $depth, int $postId): void
90
    {
91
        $qb0 = $this->createQueryBuilder('c');
92
        $qb0->update()
93
            ->set('c.nestedSet.leftKey', 'c.nestedSet.leftKey + 2')
94
            ->where($qb0->expr()->gte('c.nestedSet.leftKey', ':idx'))
95
            ->andWhere($qb0->expr()->isNotNull('c.nestedSet.leftKey'))
96
            ->andWhere($qb0->expr()->eq('c.post', ':postId'))
97
            ->setParameter('postId', $postId)
98
            ->setParameter('idx', $index)
99
            ->getQuery()
100
            ->execute()
101
        ;
102

103
        $qb1 = $this->createQueryBuilder('c');
104
        $qb1->update()
105
            ->set('c.nestedSet.rightKey', 'c.nestedSet.rightKey + 2')
106
            ->where($qb1->expr()->gte('c.nestedSet.rightKey', ':idx'))
107
            ->andWhere($qb1->expr()->isNotNull('c.nestedSet.leftKey'))
108
            ->andWhere($qb1->expr()->eq('c.post', ':postId'))
109
            ->setParameter('postId', $postId)
110
            ->setParameter('idx', $index)
111
            ->getQuery()
112
            ->execute()
113
        ;
114

115
        $ns = $entity->getNestedSet();
116
        $ns
117
            ->setLeftKey($index)
118
            ->setRightKey($index + 1)
119
            ->setDepth($depth)
120
        ;
121

122
        $this->getEntityManager()->flush();
123
    }
124

125
    /**
126
     * @param Comment $entity
127
     */
128
    public function markAsDeleted(Comment $entity): void
129
    {
130
        $entity->setDeleted(true);
131
        $this->getEntityManager()->flush();
132
    }
133

134
    public function save(Comment $entity): void
135
    {
136
        $new = is_null($entity->getId());
137

138
        $this->getEntityManager()->persist($entity);
139
        $this->getEntityManager()->flush();
140

141
        if ($new) {
142
            if ($parent = $entity->getParent()) {
143
                $this->addToTree(
144
                    $entity,
145
                    $parent->getNestedSet()->getRightKey(),
146
                    $parent->getNestedSet()->getDepth() + 1,
147
                    $entity->getPost()->getId()
148
                );
149
            } else {
150
                $maxRightKey = $this->getMaxRightKey($entity->getPost()->getId());
151
                $entity
152
                    ->getNestedSet()
153
                    ->setLeftKey($maxRightKey + 1)
154
                    ->setRightKey($maxRightKey + 2)
155
                ;
156

157
                $this->getEntityManager()->flush();
158
            }
159
        }
160
    }
161

162
    private function getMaxRightKey(int $postId): int
163
    {
164
        $qb = $this->createQueryBuilder('c');
165
        $qb
166
            ->select('COALESCE(MAX(c.nestedSet.rightKey), 0)')
167
            ->where($qb->expr()->eq('c.post', ':postId'))
168
            ->setParameter('postId', $postId)
169
        ;
170

171
        return (int)$qb->getQuery()->getSingleScalarResult();
172
    }
173
}
174