PHP EntityManager::createQuery方法代碼示例

 /**
  * @param string $imageName image name is comprised of UUID and original name (UUID/origName.extension)
  * @throws DBALException
  * @throws FileRemovalException
  */
 public function removeImage($imageName)
 {
     $id = mb_substr($imageName, 0, mb_strpos($imageName, '/'));
     $file = sprintf('%s/%s', $this->imageFileRoot, $imageName);
     try {
         $this->em->beginTransaction();
         $d = $this->em->createQuery('DELETE ' . Image::class . ' i WHERE i.id = :id')->execute(['id' => $id]);
         $directory = sprintf('%s/%s', $this->imageFileRoot, $id);
         // checks whether directory exists (each directory has always one file)
         if ($d > 0 and \file_exists($directory) and is_dir($directory)) {
             $r = \unlink($file);
             // and if so then remove file in it
             if ($r === false) {
                 // file couldn't be removed
                 $this->em->rollback();
                 $this->em->close();
                 throw new FileRemovalException();
             } else {
                 // remove directory
                 FileSystem::delete($directory);
             }
         }
         $this->em->commit();
     } catch (DBALException $e) {
         $this->em->rollback();
         $this->em->close();
         throw $e;
     }
 }

 public function findEventsByType($logTypeID)
 {
     return $this->cache->load('logEvents-' . $logTypeID, function (&$dependencies) use($logTypeID) {
         return array_column($this->em->createQuery('SELECT e.id, e.name FROM ' . EventLog::class . ' e INDEX BY e.id
              WHERE e.logType = :typeID')->setParameter('typeID', $logTypeID)->getArrayResult(), 'name', 'id');
     });
 }

 public function findAllLocales()
 {
     return $this->cache->load('locales', function () {
         $locales = $this->em->createQuery('SELECT l FROM ' . Locale::class . ' l INDEX BY l.name')->getArrayResult();
         if (empty($locales)) {
             return [];
         }
         return $locales;
     });
 }

 /**
  * @return ArrayHash|null
  */
 public function loadOptions()
 {
     $options = $this->cache->load(Option::getCacheKey(), function () use(&$dependencies) {
         $options = $this->em->createQuery('SELECT o FROM ' . Option::class . ' o')->getArrayResult();
         if (empty($options)) {
             return null;
         }
         $options = ArrayHash::from(Arrays::associate($options, 'name=value'));
         $dependencies = [Cache::TAGS => Option::getCacheKey()];
         return $options;
     });
     return $options;
 }

 /**
  * @param Comment $comment
  * @throws ActionFailedException
  */
 public function remove(Comment $comment)
 {
     try {
         $this->em->beginTransaction();
         $this->em->remove($comment)->flush();
         $this->em->createQuery('UPDATE ' . Comment::class . ' c SET c.order = c.order - 1
              WHERE c.page = :page and c.order > :order')->execute(['page' => $comment->getPageId(), 'order' => $comment->getOrder()]);
         $this->em->commit();
     } catch (\Exception $e) {
         $this->em->rollback();
         $this->em->close();
         throw new ActionFailedException();
     }
 }

 /**
  * Returns current user identity, if any.
  * @return IIdentity|NULL
  */
 public function getIdentity()
 {
     $identity = parent::getIdentity();
     // if we have our fake identity, we now want to
     // convert it back into the real entity
     if ($identity instanceof FakeIdentity) {
         return $this->cache->load(sprintf('user-%s', $identity->getId()), function () use($identity) {
             return $this->entityManager->createQuery('SELECT u, roles FROM ' . $identity->getClass() . ' u
                  LEFT JOIN u.roles roles
                  WHERE u.id = :id')->setParameter('id', $identity->getId())->getOneOrNullResult();
         });
         //return $this->entityManager->getReference($identity->getClass(), $identity->getId());
     }
     return $identity;
 }

 /**
  * @param $event
  * @return EventLog|null Returns null if there is no EventLog with given name
  * @throws \Doctrine\ORM\ORMException
  * @throws \Exception
  */
 private function getEventLog($event)
 {
     $eventLog = $this->cache->load(self::getEventLogCacheKey($event), function () use($event) {
         return $this->em->createQuery('SELECT el, lt FROM ' . EventLog::class . ' el
              JOIN el.logType lt
              WHERE el.name = :name')->setParameter('name', $event)->getOneOrNullResult();
     });
     return $eventLog;
 }

 /**
  * @param Listing $listing
  * @param WorkedHours $newWorkedHours
  * @param array $daysToChange
  * @return ListingItem[]
  */
 public function changeWorkedHours(Listing $listing, WorkedHours $newWorkedHours, array $daysToChange)
 {
     $workedHours = $this->workedHoursProvider->setupWorkedHoursEntity($newWorkedHours);
     $this->em->createQuery('UPDATE ' . ListingItem::class . ' li
          SET li.workedHours = :workedHours
          WHERE li.listing = :listing AND li.day IN (:days)')->setParameters(['workedHours' => $workedHours, 'listing' => $listing, 'days' => $daysToChange])->execute();
     $updatedItems = $items = $this->listingItemsReader->findListingItems($listing, $daysToChange);
     return $updatedItems;
 }

 /**
  * @param User $user
  * @return mixed
  */
 public function getTotalWorkedStatistics(User $user)
 {
     $stats = $this->em->createQuery('SELECT SUM(time_to_sec(ADDTIME(SUBTIME(SUBTIME(wh.workEnd, wh.workStart), wh.lunch), wh.otherHours))) AS total_worked_hours,
                     COUNT(li.id) AS worked_days
              FROM ' . Listing::class . ' l
              LEFT JOIN ' . ListingItem::class . ' li WITH li.listing = l
              LEFT JOIN li.workedHours wh
              WHERE l.user = :user')->setParameter('user', $user);
     return $stats->getSingleResult();
 }

 /**
  * @param Locality $locality
  * @return Locality
  * @throws \Doctrine\DBAL\DBALException
  * @throws \Exception
  */
 public function setupLocalityEntity(Locality $locality)
 {
     /* In order to NOT auto increment locality ID counter in DB by
                INSERTs that actually wont happen (e.g. safePersist()) and
                because Doctrine2 does NOT support locking of entire tables,
                we have to use native SQL(MySQL) query.
     
                DUAL is "dummy" table - there is no need to reference any table
                (more info in MySQL SELECT documentation)
             */
     $this->em->getConnection()->executeQuery('INSERT INTO locality (name)
          SELECT :name FROM DUAL
          WHERE NOT EXISTS(
                SELECT l.name
                FROM locality l
                WHERE l.name = :name)
          LIMIT 1', ['name' => $locality->getName()]);
     $result = $this->em->createQuery('SELECT l FROM ' . Locality::class . ' l
          WHERE l.name = :name')->setParameters(['name' => $locality->getName()])->getSingleResult();
     return $result;
 }

 /**
  * Vraci nahodne clanky
  * @param int $count
  * @param DateTime|NULL $period
  * @return Entities\Article[]
  */
 public function getRandArticles($count = 1, $period = NULL)
 {
     if ($period === NULL) {
         $now = new DateTime();
         $period = $now->modify("-6 month");
     }
     $cacheId = 'rand-' . $count;
     $countAllArticles = (int) $this->countAllArticles();
     $query = "SELECT a FROM \\App\\Model\\Entities\\Article a WHERE a.published = true " . "AND a.publishDate >= :date ";
     $randArticles = $this->em->createQuery($query)->setParameter('date', $period)->setMaxResults($count)->setFirstResult(mt_rand(0, $countAllArticles - 1))->useResultCache(TRUE, 600, $cacheId)->getResult();
     return $randArticles;
 }

 private function loadPermissions(Permission $acl)
 {
     $permissions = $this->em->createQuery('SELECT p, pr FROM ' . \Users\Authorization\Permission::class . ' p
          LEFT JOIN p.privilege pr')->execute();
     /** @var \Users\Authorization\Permission $permission */
     foreach ($permissions as $permission) {
         if ($permission->isAllowed() === true) {
             $acl->allow($permission->getRoleName(), $permission->getResourceName(), $permission->getPrivilegeName());
         } else {
             $acl->deny($permission->getRoleName(), $permission->getResourceName(), $permission->getPrivilegeName());
         }
     }
     $acl->allow(Role::GOD, IAuthorizator::ALL, IAuthorizator::ALL);
 }

 /**
  * @param WorkedHours $workedHours
  * @return WorkedHours
  * @throws \Doctrine\DBAL\DBALException
  * @throws \Exception
  */
 public function setupWorkedHoursEntity(WorkedHours $workedHours)
 {
     $values = $workedHours->toArray(true);
     /* In order to NOT auto increment workedHours ID counter in DB by
                INSERTs that actually wont happen (e.g. safePersist()) and
                because Doctrine2 does NOT support locking of entire tables,
                we have to use native SQL(MySQL) query.
     
                DUAL is "dummy" table - there is no need to reference any table
                (more info in MySQL SELECT documentation)
             */
     $this->em->getConnection()->executeQuery('INSERT INTO worked_hours (work_start, work_end, lunch, other_hours)
          SELECT :workStart, :workEnd, :lunch, :otherHours FROM DUAL
          WHERE NOT EXISTS(
                SELECT work_start, work_end, lunch, other_hours
                FROM worked_hours
                WHERE work_start = :workStart AND work_end = :workEnd AND
                      lunch = :lunch AND other_hours = :otherHours)
          LIMIT 1', $values);
     $result = $this->em->createQuery('SELECT wh FROM ' . WorkedHours::class . ' wh
          WHERE wh.workStart = :workStart AND wh.workEnd = :workEnd AND
                wh.lunch = :lunch AND wh.otherHours = :otherHours')->setParameters($values)->getOneOrNullResult();
     return $result;
 }

 /**
  * @param $path
  * @return null|Url
  */
 private function loadUrlEntity($path)
 {
     /** @var Url $urlEntity */
     $urlEntity = $this->cache->load($path, function (&$dependencies) use($path) {
         /** @var Url $urlEntity */
         $urlEntity = $this->em->createQuery('SELECT u, rt FROM ' . Url::class . ' u
              LEFT JOIN u.actualUrlToRedirect rt
              WHERE u.urlPath = :urlPath')->setParameter('urlPath', $path)->getOneOrNullResult();
         if ($urlEntity === null) {
             $this->logger->addError(sprintf('Page not found. URL_PATH: %s', $path));
             return null;
         }
         $dependencies = [Nette\Caching\Cache::TAGS => $urlEntity->getCacheKey()];
         return $urlEntity;
     });
     return $urlEntity;
 }

 /**
  * Method used for search functionality
  *
  * @param int $tagID
  * @return array
  */
 public function searchByTag($tagID)
 {
     $rsm = new ResultSetMappingBuilder($this->em);
     $rsm->addEntityResult(Page::class, 'p');
     $rsm->addFieldResult('p', 'id', 'id');
     $rsm->addFieldResult('p', 'title', 'title');
     $rsm->addFieldResult('p', 'intro', 'intro');
     $rsm->addFieldResult('p', 'intro_html', 'introHtml');
     //$rsm->addFieldResult('p', 'text', 'text');
     $rsm->addMetaResult('p', 'author', 'author');
     $rsm->addMetaResult('p', 'url', 'url');
     $rsm->addFieldResult('p', 'created_at', 'createdAt');
     $rsm->addFieldResult('p', 'is_draft', 'isDraft');
     $rsm->addFieldResult('p', 'published_at', 'publishedAt');
     $rsm->addFieldResult('p', 'allowed_comments', 'allowedComments');
     $rsm->addScalarResult('commentsCount', 'commentsCount', 'integer');
     $rsm->addIndexByColumn('p', 'id');
     $rsm->addJoinedEntityResult(Locale::class, 'l', 'p', 'locale');
     $rsm->addFieldResult('l', 'locale_id', 'id');
     $rsm->addFieldResult('l', 'locale_name', 'name');
     $rsm->addFieldResult('l', 'locale_code', 'code');
     $rsm->addFieldResult('l', 'locale_lang', 'lang');
     $rsm->addFieldResult('l', 'locale_default', 'default');
     $nativeQuery = $this->em->createNativeQuery('SELECT p.id, p.title, p.intro, p.intro_html, p.author, p.url,
                 p.created_at, p.is_draft, p.published_at,
                 p.allowed_comments, COUNT(c.page) AS commentsCount,
                 l.id AS locale_id, l.name AS locale_name, l.code AS locale_code,
                 l.lang AS locale_lang, l.default AS locale_default
          FROM (
             SELECT pts.page_id, pts.tag_id
             FROM page_tag pts
             WHERE pts.tag_id = :id
             GROUP BY pts.page_id
          ) AS pt
          JOIN page p ON (p.id = pt.page_id AND p.is_draft = 0 AND p.published_at <= NOW())
          JOIN locale l ON (l.id = p.locale)
          LEFT JOIN comment c ON (c.page = pt.page_id)
          GROUP BY pt.page_id', $rsm)->setParameter('id', $tagID);
     $pages = $nativeQuery->getResult();
     $this->em->createQuery('SELECT PARTIAL page.{id}, tags FROM ' . Page::class . ' page
          LEFT JOIN page.tags tags INDEX BY tags.id
          WHERE page.id IN (:ids)')->setParameter('ids', array_keys($pages))->execute();
     return $pages;
 }