PHP EntityManager::createQuery方法代码示例

    /**
     * Loads a locale.
     *
     * @param mixed $resource A resource
     * @param string $locale A locale
     * @param string $domain The domain
     *
     * @return MessageCatalogue A MessageCatalogue instance
     *
     * @throws NotFoundResourceException when the resource cannot be found
     * @throws InvalidResourceException  when the resource cannot be loaded
     */
    public function load($resource, $locale, $domain = 'messages')
    {
        if ($this->loadAll !== true) {
            return new MessageCatalogue($locale);
        }
        $dql = <<<'DQL'
                SELECT
                  t
                FROM
                  MjrLibraryEntitiesBundle:System\Translation t
              WHERE
                  t.Locale = :locale
              ORDER BY t.Id ASC
DQL;
        $query = $this->entityManager->createQuery($dql);
        $query->setParameter(':locale', $locale);
        /** @var Translation[] $results */
        $results = $query->getResult();
        $catalogue = new MessageCatalogue($locale);
        if (count($results) > 0) {
            foreach ($results as $result) {
                $catalogue->set($result->getIdentity(), $result->getTranslation(), $domain);
            }
        }
        return $catalogue;
    }

 private function loadLicenses()
 {
     $dql = 'SELECT l FROM Doctrine\\Bundle\\LicenseManagerBundle\\Entity\\License l INDEX BY l.spdxIdentifier';
     $query = $this->entityManager->createQuery($dql);
     $licenses = $query->getResult();
     $url = "http://spdx.org/licenses/";
     $content = file_get_contents($url);
     $crawler = new Crawler($content);
     $rows = $crawler->filter('table tbody tr');
     foreach ($rows as $row) {
         $row = new Crawler($row);
         $tds = $row->filter('td');
         $isOSIApproved = $tds->eq(2)->text() === "Y";
         if (!$isOSIApproved) {
             continue;
         }
         $identifier = trim($tds->eq(1)->text());
         if (isset($licenses[$identifier])) {
             continue;
         }
         $name = $tds->eq(0)->text();
         $licenseUrl = $url . substr($tds->eq(3)->filter('a')->attr('href'), 2);
         $license = new License($name, $licenseUrl, $identifier);
         $this->entityManager->persist($license);
     }
     $this->entityManager->flush();
 }

 public function eliminarItemsPorInstrumento($idInstrumento)
 {
     $query = "DELETE\r\n\r\n        FROM AppModelBundle:InstrumentoItem iin\r\n\r\n        WHERE 1 = 1\r\n        \r\n        AND iin.ins = :idInstrumento\r\n        ";
     $query = $this->em->createQuery($query);
     $query->setParameter('idInstrumento', $idInstrumento);
     return $query->execute();
 }

 /**
  * @param VersionableInterface $resource
  * @return array
  */
 public function getVersions(VersionableInterface $resource)
 {
     $query = $this->_em->createQuery("SELECT v FROM ResourceVersion v INDEX BY v.version " . "WHERE v.resourceName = ?1 AND v.resourceId = ?2 ORDER BY v.version DESC");
     $query->setParameter(1, get_class($resource));
     $query->setParameter(2, $resource->getResourceId());
     return $query->getResult();
 }

 /**
  * @param $names
  * @return array
  */
 public function getIngredientsByName($names)
 {
     $query = $this->entityManager->createQuery('SELECT i FROM CookWithMeBundle:Ingredient i WHERE i.name IN (:names)');
     $query->setParameters(array('names' => $names));
     $ingredients = $query->getResult();
     return $ingredients;
 }

 public function obtenerAlternativasPorItem($idItem)
 {
     $query = "SELECT\r\n\t\tite.id    as idItem,\r\n\t\talt.id    as idAlternativa,\r\n\t\talt.texto as textoAlternativa\r\n\r\n\t\tFROM AppModelBundle:Alternativa alt\r\n\t\tJOIN alt.ite ite\r\n\r\n\t\tWHERE 1 = 1\r\n\t\t\r\n\t\tAND ite.id = :idItem\r\n\t\t";
     $query = $this->em->createQuery($query);
     $query->setParameter('idItem', $idItem);
     return $query->getArrayResult();
 }

    /**
     * ConfigService constructor.
     * @param ContainerInterface $config
     * @param EntityManager $em
     * @throws ConfigValueCanNotBeDecoded
     * @internal param Redis $redis
     */
    public function __construct(ContainerInterface $config, EntityManager $em)
    {
        $this->Cache = $config->get('snc_redis.default');
        $this->EntityManager = $em;
        $this->Entries = unserialize($this->Cache->get(self::CACHE_KEY));
        if (!is_array($this->Entries) || count($this->Entries) < 1) {
            $this->Entries = array();
            $dql = <<<'DQL'
SELECT c FROM MjrLibraryEntitiesBundle:Config c
DQL;
            $query = $this->EntityManager->createQuery($dql);
            /** @var Config[] $results */
            $results = $query->getResult();
            if (count($results) > 0) {
                foreach ($results as $result) {
                    if (!isset($this->Entries[$result->getModule()])) {
                        $this->Entries[$result->getModule()] = array();
                    }
                    $value = unserialize($result->getValue());
                    if (!$value instanceof ConfigInterface) {
                        throw new ConfigValueCanNotBeDecoded('The Config Value for Module ' . $result->getModule() . ' with the ident ' . $result->getIdent() . ' could not be unserialized. Please check the Value!');
                    }
                    $this->Entries[$result->getModule()][$result->getIdent()] = $value;
                }
            }
            $this->Cache->set(self::CACHE_KEY, serialize($this->Entries));
        }
    }

 protected function execute(InputInterface $input, OutputInterface $output)
 {
     $app = $this->getProjectApplication();
     $this->logger = $this->getLogger();
     $this->regionService = $app['region.skyforge.service'];
     $this->regionService->setRegion($input->getOption('region'));
     /** @var EntityManager $em */
     $this->em = $app['orm.ems'][$this->regionService->getDbConnectionNameByRegion()];
     /** @var StatService $statService */
     $this->statService = $app['stat.skyforge.service'];
     $pantheonRepository = $this->em->getRepository('Erliz\\SkyforgeBundle\\Entity\\Pantheon');
     $communityRepository = $this->em->getRepository('Erliz\\SkyforgeBundle\\Entity\\Community');
     //        $lockFilePath = $app['config']['app']['path'].'/cache/curl/parse.lock';
     //        if (is_file($lockFilePath)) {
     //            throw new RuntimeException('Another parse in progress');
     //        } else {
     //            file_put_contents($lockFilePath, getmypid());
     //        }
     if ($playerId = $input->getOption('avatar')) {
         $this->statService->updatePlayer($playerId, true);
     }
     if ($communityId = $input->getOption('id')) {
         $community = $communityRepository->find($communityId);
         if (!$community) {
             $community = $pantheonRepository->find($communityId);
         }
         if (!$community) {
             $this->logger->addInfo(sprintf('Community with id %s not found in db', $communityId));
         } else {
             $this->updateCommunityMembers($community, $output);
             $this->flush();
         }
     }
     if ($input->getOption('pantheons') || $input->getOption('communities')) {
         $lastId = $input->getOption('lastId');
         if ($input->getOption('pantheons')) {
             $sqlResponse = $this->em->createQuery("\n                    SELECT pt.id, count(pl.id) cnt\n                    FROM Erliz\\SkyforgeBundle\\Entity\\Pantheon pt\n                    JOIN pt.members pl\n                    group by pt.id\n                    order by cnt DESC")->getScalarResult();
             $repo = $pantheonRepository;
         } else {
             $sqlResponse = $this->em->createQuery("\n                    SELECT pt.id, count(pl.id) cnt\n                    FROM Erliz\\SkyforgeBundle\\Entity\\Community pt\n                    JOIN pt.members pl\n                    group by pt.id\n                    order by cnt DESC")->getScalarResult();
             $repo = $communityRepository;
         }
         $communityIds = array_map('current', $sqlResponse);
         $communitiesCount = count($communityIds);
         /** @var CommunityInterface $community */
         foreach ($communityIds as $index => $communityId) {
             if ($communityId == $lastId) {
                 $lastId = false;
             }
             if ($lastId) {
                 continue;
             }
             $this->updateCommunityMembers($repo->find($communityId), $output);
             $this->logger->addInfo(sprintf('Processed %s / %s', $index + 1, $communitiesCount));
             $this->flush();
         }
     }
     //        unlink($lockFilePath);
 }

 public function setDefaultOptions(OptionsResolverInterface $resolver)
 {
     $roles = $this->em->createQuery('SELECT r FROM TSKUserBundle:Role r')->execute();
     foreach ($roles as $role) {
         $choices[$role->getName()] = $role->getName();
     }
     $resolver->setDefaults(array('required' => true, 'expanded' => true, 'multiple' => true, 'choices' => $choices));
 }

 /**
  * Marks all tips as unread
  *
  * @param Request $request The request
  *
  * @return array|\Dunglas\ApiBundle\Model\PaginatorInterface|\Traversable
  *
  * @throws RuntimeException
  */
 public function __invoke(Request $request)
 {
     $dql = "DELETE FROM PartKeepr\\TipOfTheDayBundle\\Entity\\TipOfTheDayHistory th WHERE th.user = :user";
     $query = $this->entityManager->createQuery($dql);
     $query->setParameter("user", $this->userService->getUser());
     $query->execute();
     return new Response("OK");
 }

 /**
  * После успешного логина
  *
  * @param InteractiveLoginEvent $event
  */
 public function onSecurityInteractiveLogin(InteractiveLoginEvent $event)
 {
     # запоминаем время логина пользователя
     $userId = $event->getAuthenticationToken()->getUser()->getId();
     if ($userId) {
         $lastLogin = date('Y-m-d H:i:s');
         $this->em->createQuery("\r\n\t\t\t\tUPDATE EvrikaMainBundle:User u\r\n\t\t\t\tSET u.lastLogin = '{$lastLogin}'\r\n\t\t\t\tWHERE u.id = {$userId}\r\n\t\t\t")->execute();
     }
 }

 public function obtenerAreasPorPlan($idPlan, $idNivel, $idPeriodo)
 {
     $query = "SELECT\r\n        are.id     as idArea,\r\n        are.nombre as nombreArea\r\n\r\n        FROM AppModelBundle:PlanArea pit\r\n        JOIN pit.are are\r\n\r\n        WHERE 1 = 1\r\n    \r\n        AND pit.pla = :idPlan\r\n        AND pit.niv = :idNivel\r\n        AND pit.per = :idPeriodo\r\n\r\n        ";
     $query = $this->em->createQuery($query);
     $query->setParameter('idPlan', $idPlan);
     $query->setParameter('idNivel', $idNivel);
     $query->setParameter('idPeriodo', $idPeriodo);
     return $query->getArrayResult();
 }

 /**
  * Execute given dql query
  *
  * @param string $dql
  * @param string $type
  * @return array
  */
 private function executeQuery($dql, $type)
 {
     $query = $this->em->createQuery($dql);
     $query->setParameters($this->params);
     $query->setParameter('type', $type);
     return array_map(function (array $row) {
         return $row['email'];
     }, $query->getResult());
 }

 /**
  * Load translations from the DB
  *
  * @param  string $locale           example: en_US
  * @param  string $UnusedTextDomain not used
  *
  * @return \Zend\I18n\Translator\TextDomain
  */
 public function load($locale, $UnusedTextDomain = null)
 {
     $messages = $this->entityMgr->createQuery('SELECT m.defaultText, m.text ' . 'FROM RcmI18n\\Entity\\Message m ' . 'WHERE m.locale = ?1')->setParameter(1, $locale)->getArrayResult();
     $textDomain = new TextDomain();
     foreach ($messages as &$message) {
         $textDomain[$message['defaultText']] = $message['text'];
     }
     return $textDomain;
 }

 /**
  * Loads from the database
  *
  * @param string $table    The table that contains the pages (with title, slug and controller, configure this as resource in your routing.yml, provide as type: db )
  * @param string $type     The resource type
  * @return RouteCollection the collection of routes stored in the database table
  */
 public function load($table, $type = null)
 {
     $collection = new RouteCollection();
     $pages = $this->em->createQuery('SELECT p FROM ' . $table . ' p')->execute();
     foreach ($pages as $page) {
         $collection->add('Cms' . $page->getTitle(), new Route($page->getSlug(), array('_controller' => $page->getController(), 'pageId' => $page->getId())));
     }
     return $collection;
 }