1: <?php
  2: 
  3: namespace AlexisLefebvre\Bundle\AsyncTweetsBundle\Entity;
  4: 
  5: use Doctrine\ORM\EntityRepository;
  6: 
  7: /**
  8:  * TweetRepository.
  9:  *
 10:  * This class was generated by the Doctrine ORM. Add your own custom
 11:  * repository methods below.
 12:  */
 13: class TweetRepository extends EntityRepository
 14: {
 15:     private $nbTweets = 5;
 16: 
 17:     public function getWithUsers($page = 1)
 18:     {
 19:         $firstResult = (($page - 1) * $this->nbTweets);
 20: 
 21:         $qb = $this->createQueryBuilder('t');
 22: 
 23:         $query = $qb
 24:             ->select('t, user, rt, rt_user')
 25:             ->innerJoin('t.user', 'user')
 26:             ->leftJoin('t.retweeted_status', 'rt')
 27:             ->leftJoin('rt.user', 'rt_user')
 28: 
 29:             // Ignore tweets that were only retweeted
 30:             ->where($qb->expr()->eq('t.in_timeline', 'true'))
 31: 
 32:             ->orderBy('t.id', 'DESC')
 33: 
 34:             ->setFirstResult($firstResult)
 35:             ->setMaxResults($this->nbTweets);
 36: 
 37:         return $query->getQuery()->getResult();
 38:     }
 39: 
 40:     /**
 41:      * @param \Doctrine\ORM\QueryBuilder $qb
 42:      *
 43:      * @return \Doctrine\ORM\QueryBuilder $query
 44:      */
 45:     private function getWithUsersAndMediasQuery($qb)
 46:     {
 47:         $query = $qb
 48:             ->select('t, user, medias, rt, rt_user')
 49:             ->innerJoin('t.user', 'user')
 50:             ->leftJoin('t.medias', 'medias')
 51:             ->leftJoin('t.retweeted_status', 'rt')
 52:             ->leftJoin('rt.user', 'rt_user')
 53: 
 54:             // Ignore tweets that were only retweeted
 55:             ->where($qb->expr()->eq('t.in_timeline', 'true'))
 56: 
 57:             ->orderBy('t.id', 'ASC')
 58: 
 59:             ->setFirstResult(0)
 60:             ->setMaxResults($this->nbTweets);
 61: 
 62:         return $query;
 63:     }
 64: 
 65:     public function getWithUsersAndMedias($firstTweetId = null)
 66:     {
 67:         $qb = $this->createQueryBuilder('t');
 68: 
 69:         $query = $this->getWithUsersAndMediasQuery($qb);
 70: 
 71:         if (!is_null($firstTweetId)) {
 72:             $query = $query->andWhere(
 73:                 $qb->expr()->gte('t.id', $firstTweetId)
 74:             );
 75:         }
 76: 
 77:         return $query->getQuery()->getResult();
 78:     }
 79: 
 80:     /**
 81:      * @param string $condition
 82:      * @param string $order
 83:      * @param int    $tweetId
 84:      *
 85:      * @return int|null
 86:      */
 87:     private function getTweetId($condition, $order, $tweetId)
 88:     {
 89:         $qb = $this->createQueryBuilder('t')
 90:             ->select('t.id')
 91: 
 92:             ->where('t.id '.$condition.' :tweetId')
 93:             ->setParameter(':tweetId', $tweetId)
 94: 
 95:             ->andWhere('t.in_timeline = true')
 96: 
 97:             ->orderBy('t.id', $order)
 98: 
 99:             ->setFirstResult($this->nbTweets - 1)
100:             ->setMaxResults(1);
101: 
102:         $result = $qb->getQuery()->getOneOrNullResult();
103: 
104:         return is_array($result) ? $result['id'] : null;
105:     }
106: 
107:     public function getPreviousTweetId($tweetId)
108:     {
109:         return $this->getTweetId('<', 'DESC', $tweetId);
110:     }
111: 
112:     public function getNextTweetId($tweetId)
113:     {
114:         return $this->getTweetId('>', 'ASC', $tweetId);
115:     }
116: 
117:     public function countPendingTweets($lastTweetId = null)
118:     {
119:         /** @var \Doctrine\ORM\QueryBuilder $qb */
120:         $qb = $this->createQueryBuilder('t');
121: 
122:         $query = $qb
123:             ->add('select', $qb->expr()->count('t.id'))
124:             // Ignore tweets that were only retweeted
125:             ->where(
126:                 $qb->expr()->eq('t.in_timeline', 'true')
127:             );
128: 
129:         if (!is_null($lastTweetId)) {
130:             $query = $query->andWhere(
131:                 $qb->expr()->gte('t.id', $lastTweetId)
132:             );
133:         }
134: 
135:         // return result of "COUNT()" query
136:         return $query->getQuery()->getSingleScalarResult();
137:     }
138: 
139:     public function getLastTweet()
140:     {
141:         $qb = $this->createQueryBuilder('t')
142:             ->addOrderBy('t.id', 'DESC')
143:             ->setFirstResult(0)
144:             ->setMaxResults(1);
145: 
146:         return $qb->getQuery()->getOneOrNullResult();
147:     }
148: 
149:     /**
150:      * @param int $tweetId
151:      *
152:      * @return array
153:      */
154:     private function getTweetsLessThanId($tweetId)
155:     {
156:         $qb = $this->createQueryBuilder('t')
157:             ->select('t, m')
158:             ->leftJoin('t.medias', 'm')
159:             ->where('t.id < :tweetId')
160:             ->setParameter(':tweetId', $tweetId)
161: 
162:             // Get retweeted tweets (it would break foreign keys)
163:             //  http://stackoverflow.com/questions/15087933/how-to-do-left-join-in-doctrine/15088250#15088250
164:             ->leftJoin(
165:                 'AsyncTweetsBundle:Tweet',
166:                 't2',
167:                 'WITH',
168:                 't.id = t2.retweeted_status'
169:             )
170: 
171:             ->orderBy('t.id', 'DESC');
172: 
173:         return $qb->getQuery()->getResult();
174:     }
175: 
176:     /**
177:      * Remove Media not associated to any Tweet.
178:      *
179:      * @param Media $media
180:      */
181:     private function removeOrphanMedias(Media $media)
182:     {
183:         if (count($media->getTweets()) == 0) {
184:             $this->_em->remove($media);
185:         }
186:     }
187: 
188:     /**
189:      * Remove the tweet and return 1 is the deleted tweet is not a
190:      *  retweet.
191:      *
192:      * @param Tweet $tweet
193:      *
194:      * @return int
195:      */
196:     protected function removeTweet($tweet)
197:     {
198:         $count = 0;
199: 
200:         foreach ($tweet->getMedias() as $media) {
201:             $tweet->removeMedia($media);
202:             $this->removeOrphanMedias($media);
203:         }
204: 
205:         // Don't count tweets that were only retweeted
206:         if ($tweet->isInTimeline()) {
207:             $count = 1;
208:         }
209: 
210:         $this->_em->remove($tweet);
211: 
212:         return $count;
213:     }
214: 
215:     /**
216:      * Delete tweets and return the number of deleted tweets (excluding
217:      *  retweeted-only tweets).
218:      *
219:      * @param int $tweetId
220:      *
221:      * @return int
222:      */
223:     public function deleteAndHideTweetsLessThanId($tweetId)
224:     {
225:         $count = 0;
226: 
227:         $tweets = $this->getTweetsLessThanId($tweetId);
228: 
229:         foreach ($tweets as $tweet) {
230:             if ($tweet->mustBeKept($tweetId)) {
231:                 // The Tweet is still in the timeline, it can only be hidden
232:                 $tweet->setInTimeline(false);
233:                 $this->_em->persist($tweet);
234:             } else {
235:                 // The Tweet has not been retweeted, it can be removed
236:                 $count += $this->removeTweet($tweet);
237:             }
238:         }
239: 
240:         $this->_em->flush();
241: 
242:         return $count;
243:     }
244: }
245: