PHP ObjectManager::createQueryBuilder方法代码示例

 /**
  * Find ungenerated objects and return an IterableResult
  *
  * @return IterableResult
  */
 public function findUngeneratedIterator()
 {
     /** @var QueryBuilder $queryBuilder */
     $queryBuilder = $this->entityManager->createQueryBuilder();
     $queryBuilder->select('t')->from($this->getEntityClassName(), 't')->where('t.resource IS NULL AND t.staticResource IS NULL');
     return $queryBuilder->getQuery()->iterate();
 }

 private function cleanupTerms(Treatment $treatment)
 {
     // find terms for this treatment
     $qb = $this->om->createQueryBuilder();
     $qb->select('t2')->from('TermBundle:Term', 't2')->innerJoin('t2.termDocuments', 'td', Join::WITH, 'td.type = :treatmentType')->setParameter('treatmentType', TermDocument::TYPE_TREATMENT)->where('td.documentId = :treatmentId')->setParameter('treatmentId', $treatment->getId());
     $terms = $qb->getQuery()->getResult();
     $this->output->writeln('Cleaning terms for treatment #' . $treatment->getId() . ' [' . $treatment->getName() . ']');
     if (\count($terms)) {
         $hasInternal = false;
         foreach ($terms as $term) {
             $this->output->write($this->indent() . $term->getName() . " [#{$term->getId()}]" . $this->indent());
             if (!$term->getInternal()) {
                 // if this has not been flagged as internal yet, flag it
                 $term->setInternal(\strtolower($term->getName()) == \strtolower($treatment->getName()));
             }
             if (!$hasInternal) {
                 $hasInternal = $term->getInternal();
             }
             $this->om->persist($term);
             $this->output->writeln('[OK]');
         }
         if (!$hasInternal) {
             $term = $this->createTermFromTreatment($treatment);
             $this->om->persist($term);
             $this->output->writeln($this->indent() . 'Added internal term');
         }
     } else {
         $this->output->write($this->indent() . "Found no terms: ");
         $term = $this->createTermFromTreatment($treatment);
         $this->om->persist($term);
         $this->output->writeln('[OK]');
     }
 }

 /**
  * Find users by given query
  *
  * @param string $q
  * @param int $limit
  * @return array
  */
 public function find($q, $limit = 25)
 {
     if (empty($q)) {
         return array();
     }
     $query = $this->em->createQueryBuilder()->select('u')->from('Newscoop\\Entity\\User', 'u')->where('u.email LIKE ?0')->orWhere('u.username LIKE ?0')->orderBy('u.id', 'asc')->setMaxResults($limit)->getQuery();
     $query->setParameter(0, "%{$q}%");
     return $query->getResult();
 }

 /**
  * Transform single id value to an entity
  *
  * @param string $value
  * @return mixed|null|object
  */
 public function reverseTransform($value)
 {
     if (empty($value)) {
         return null;
     }
     try {
         $entity = $this->em->createQueryBuilder()->select('entity')->from($this->className, 'entity')->where('entity.' . $this->primaryKey . ' = :id')->setParameter('id', $value)->getQuery()->getSingleResult();
     } catch (\Exception $ex) {
         // this will happen if the form submits invalid data
         throw new TransformationFailedException(sprintf('The choice "%s" does not exist or is not unique', $value));
     }
     if (!$entity) {
         return null;
     }
     return $entity;
 }

 /**
  * {@inheritdoc}
  */
 public function load(ObjectManager $manager)
 {
     // get already present
     $qb = $manager->createQueryBuilder();
     $qb->from(SecurityType::class, 's', 's.id');
     $qb->select('s');
     $present = $qb->getQuery()->getResult();
     // load xml
     $file = $this->container->getParameter('sulu_security.security_types.fixture');
     $doc = new \DOMDocument();
     $doc->load($file);
     $xpath = new \DOMXpath($doc);
     $elements = $xpath->query('/security-types/security-type');
     if (!is_null($elements)) {
         /** @var $element \DOMNode */
         foreach ($elements as $element) {
             /** @var $child \DOMNode */
             foreach ($element->childNodes as $child) {
                 if (isset($child->nodeName)) {
                     if ($child->nodeName == 'id') {
                         $typeId = $child->nodeValue;
                     }
                     if ($child->nodeName == 'name') {
                         $typeName = $child->nodeValue;
                     }
                 }
             }
             $securityType = array_key_exists($typeId, $present) ? $present[$typeId] : new SecurityType();
             $securityType->setId($typeId);
             $securityType->setName($typeName);
             $manager->persist($securityType);
         }
     }
     $manager->flush();
 }

 /**
  *
  * @param array $workspaces
  * @return QueryBuilder
  */
 protected function createQueryBuilder(array $workspaces)
 {
     /** @var QueryBuilder $queryBuilder */
     $queryBuilder = $this->entityManager->createQueryBuilder();
     $queryBuilder->select('n')->from(NodeData::class, 'n')->where('n.workspace IN (:workspaces)')->setParameter('workspaces', $workspaces);
     return $queryBuilder;
 }

 /**
  *
  * @param array $workspaces
  * @return QueryBuilder
  */
 protected function createQueryBuilder(array $workspaces)
 {
     /** @var QueryBuilder $queryBuilder */
     $queryBuilder = $this->entityManager->createQueryBuilder();
     $queryBuilder->select('n')->from('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->where('n.workspace IN (:workspaces)')->setParameter('workspaces', $workspaces);
     return $queryBuilder;
 }

 /**
  * Find all NodeData objects inside a given workspace sorted by path to be used
  * in publishing. The order makes sure that parent nodes are published first.
  *
  * @param Workspace $workspace
  * @return array<NodeData>
  */
 public function findByWorkspace(Workspace $workspace)
 {
     /** @var \Doctrine\ORM\QueryBuilder $queryBuilder */
     $queryBuilder = $this->entityManager->createQueryBuilder();
     $queryBuilder->select('n')->distinct()->from('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->where('n.workspace = :workspace')->orderBy('n.path', 'ASC')->setParameter('workspace', $workspace);
     return $queryBuilder->getQuery()->getResult();
 }

 /**
  * {@inheritdoc}
  */
 public function load(ObjectManager $manager)
 {
     // get already stored countries
     $qb = $manager->createQueryBuilder();
     $qb->from(Country::class, 'c', 'c.code');
     $qb->select('c');
     $existingCountries = $qb->getQuery()->getResult();
     // load xml
     $file = dirname(__FILE__) . '/../countries.xml';
     $doc = new \DOMDocument();
     $doc->load($file);
     $xpath = new \DOMXpath($doc);
     $elements = $xpath->query('/Countries/Country');
     if (!is_null($elements)) {
         /** @var $element DOMNode */
         foreach ($elements as $element) {
             /** @var $child DOMNode */
             foreach ($element->childNodes as $child) {
                 if (isset($child->nodeName)) {
                     if ($child->nodeName == 'Name') {
                         $countryName = $child->nodeValue;
                     }
                     if ($child->nodeName == 'Code') {
                         $countryCode = $child->nodeValue;
                     }
                 }
             }
             $country = array_key_exists($countryCode, $existingCountries) ? $existingCountries[$countryCode] : new Country();
             $country->setName($countryName);
             $country->setCode($countryCode);
             $manager->persist($country);
         }
     }
     $manager->flush();
 }

 /**
  * Transform array to a collection of entities
  *
  * @param array $values
  * @return array
  */
 public function reverseTransform($values)
 {
     if (!is_array($values) || empty($values)) {
         return array();
     }
     // add new tag entries
     $newObjects = array();
     $tagPrefixLength = strlen($this->newTagPrefix);
     foreach ($values as $key => $value) {
         $cleanValue = substr($value, $tagPrefixLength);
         $valuePrefix = substr($value, 0, $tagPrefixLength);
         if ($valuePrefix == $this->newTagPrefix) {
             $object = new $this->className();
             $this->accessor->setValue($object, $this->textProperty, $cleanValue);
             $newObjects[] = $object;
             unset($values[$key]);
         }
     }
     try {
         // get multiple entities with one query
         $entities = $this->em->createQueryBuilder()->select('entity')->from($this->className, 'entity')->where('entity.' . $this->primaryKey . ' IN (:ids)')->setParameter('ids', $values)->getQuery()->getResult();
     } catch (\Exception $ex) {
         // this will happen if the form submits invalid data
         throw new TransformationFailedException('One or more id values are invalid');
     }
     return array_merge($entities, $newObjects);
 }

 /**
  * Transforms a string (id) to an object (city).
  */
 public function reverseTransform($string)
 {
     if (empty($string)) {
         return null;
     }
     $titles = explode(', ', $string);
     $cityTitle = $titles[0];
     $countryTitle = count($titles) > 1 ? $titles[1] : null;
     $builder = $this->om->createQueryBuilder();
     $builder->select('city')->from('LearningMainBundle:City', 'city')->leftJoin('LearningMainBundle:Country', 'country', 'WITH', 'country = city.country')->where('city.title = :cityTitle')->orderBy('country.id', 'ASC')->setParameter('cityTitle', $cityTitle)->setMaxResults(1);
     if ($countryTitle) {
         $builder->andWhere('country.title LIKE :countryTitle')->setParameter('countryTitle', '%' . $countryTitle . '%');
     }
     $city = $builder->getQuery()->getOneOrNullResult();
     if (empty($city)) {
         throw new TransformationFailedException(sprintf('Город "%s" не найден!', $string));
     }
     return $city;
 }

 /**
  * Finds all objects and return an IterableResult
  *
  * @param string $host Full qualified host name
  * @param callable $callback
  * @return \Generator<Redirect>
  */
 public function findAll($host = null, callable $callback = null)
 {
     /** @var QueryBuilder $queryBuilder */
     $queryBuilder = $this->entityManager->createQueryBuilder();
     $query = $queryBuilder->select('r')->from($this->getEntityClassName(), 'r');
     if ($host !== null) {
         $query->andWhere('r.host = :host')->setParameter('host', $host);
     } else {
         $query->andWhere('r.host IS NULL');
     }
     $query->orderBy('r.host', 'ASC');
     $query->addOrderBy('r.sourceUriPath', 'ASC');
     return $this->iterate($query->getQuery()->iterate(), $callback);
 }

 /**
  * Repair votes action
  *
  * Compare number of votes between nodes and vote log and repair, if not dryRun
  *
  * @param boolean $dryRun Don't do anything, but report actions
  * @return string
  */
 public function repairCommand($dryRun = TRUE)
 {
     if ($dryRun) {
         echo "Dry run, not making any changes\n";
     }
     $q = new FlowQuery(array($this->context->getRootNode()));
     $answerNodes = $q->find('[instanceof Sfi.Encult:Answer]')->get();
     foreach ($answerNodes as $answerNode) {
         /** @var \Doctrine\ORM\QueryBuilder $queryBuilder */
         $queryBuilder = $this->entityManager->createQueryBuilder();
         $nodes = $queryBuilder->select('v')->from('Sfi\\Encult\\Domain\\Model\\Vote', 'v')->andWhere('v.answerIdentifier = :answerIdentifier')->setParameters(array('answerIdentifier' => $answerNode->getIdentifier()))->getQuery()->getArrayResult();
         $dbCount = count($nodes);
         $crCount = $answerNode->getProperty('voteCount');
         $path = $answerNode->getPath();
         if ($dbCount !== $crCount) {
             echo "Found mistake for {$path} (db: {$dbCount} vs. cr: {$crCount})\n";
             if (!$dryRun) {
                 echo "Fixed\n";
                 $answerNode->setProperty('voteCount', $dbCount);
             }
         }
     }
     return "Done!\n";
 }

 /**
  * Transforms a string (id) to an object (city).
  */
 public function reverseTransform($string)
 {
     if (empty($string)) {
         return null;
     }
     $titles = explode(',', $string);
     $city = trim($titles[0]);
     $region = null;
     $country = null;
     if (isset($titles[2])) {
         $region = trim($titles[1]);
         $country = trim($titles[2]);
     }
     $builder = $this->om->createQueryBuilder();
     $builder->select('city')->from('VidalMainBundle:City', 'city')->leftJoin('VidalMainBundle:Country', 'country', 'WITH', 'country = city.country')->where('city.title = :city')->orderBy('country.id', 'ASC')->setParameter('city', $city)->setMaxResults(1);
     if ($country) {
         $builder->leftJoin('city.country', 'c')->leftJoin('city.region', 'r')->andWhere('c.title LIKE :country')->andWhere('r.title LIKE :region')->setParameter('country', $country)->setParameter('region', $region);
     }
     $city = $builder->getQuery()->getOneOrNullResult();
     if (empty($city)) {
         throw new TransformationFailedException(sprintf('Город "%s" не найден!', $string));
     }
     return $city;
 }

 /**
  * Saves the given array as a node data entity without using the ORM.
  *
  * If the node data already exists (same dimensions, same identifier, same workspace)
  * it is replaced.
  *
  * @param array $nodeData node data to save as an associative array ( $column_name => $value )
  * @throws ImportException
  * @return void
  */
 protected function persistNodeData($nodeData)
 {
     if ($nodeData['workspace'] !== 'live') {
         throw new ImportException('Saving NodeData with workspace != "live" using direct SQL not supported yet. Workspace is "' . $nodeData['workspace'] . '".');
     }
     if ($nodeData['path'] === '/') {
         return;
     }
     // cleanup old data
     /** @var \Doctrine\DBAL\Connection $connection */
     $connection = $this->entityManager->getConnection();
     // prepare node dimensions
     $dimensionValues = $nodeData['dimensionValues'];
     $dimensionsHash = Utility::sortDimensionValueArrayAndReturnDimensionsHash($dimensionValues);
     $jsonPropertiesDataTypeHandler = JsonArrayType::getType(JsonArrayType::FLOW_JSON_ARRAY);
     // post-process node data
     $nodeData['dimensionsHash'] = $dimensionsHash;
     $nodeData['dimensionValues'] = $jsonPropertiesDataTypeHandler->convertToDatabaseValue($dimensionValues, $connection->getDatabasePlatform());
     $nodeData['properties'] = $jsonPropertiesDataTypeHandler->convertToDatabaseValue($nodeData['properties'], $connection->getDatabasePlatform());
     $nodeData['accessRoles'] = $jsonPropertiesDataTypeHandler->convertToDatabaseValue($nodeData['accessRoles'], $connection->getDatabasePlatform());
     $connection->executeQuery('DELETE FROM typo3_typo3cr_domain_model_nodedimension' . ' WHERE nodedata IN (' . '   SELECT persistence_object_identifier FROM typo3_typo3cr_domain_model_nodedata' . '   WHERE identifier = :identifier' . '   AND workspace = :workspace' . '   AND dimensionshash = :dimensionsHash' . ' )', array('identifier' => $nodeData['identifier'], 'workspace' => $nodeData['workspace'], 'dimensionsHash' => $nodeData['dimensionsHash']));
     /** @var \Doctrine\ORM\QueryBuilder $queryBuilder */
     $queryBuilder = $this->entityManager->createQueryBuilder();
     $queryBuilder->delete()->from('TYPO3\\TYPO3CR\\Domain\\Model\\NodeData', 'n')->where('n.identifier = :identifier')->andWhere('n.dimensionsHash = :dimensionsHash')->andWhere('n.workspace = :workspace')->setParameter('identifier', $nodeData['identifier'])->setParameter('workspace', $nodeData['workspace'])->setParameter('dimensionsHash', $nodeData['dimensionsHash']);
     $queryBuilder->getQuery()->execute();
     // insert new data
     // we need to use executeUpdate to execute the INSERT -- else the data types are not taken into account.
     // That's why we build a DQL INSERT statement which is then executed.
     $queryParts = array();
     $queryArguments = array();
     $queryTypes = array();
     foreach ($this->nodeDataPropertyNames as $propertyName => $propertyConfig) {
         if (isset($nodeData[$propertyName])) {
             $queryParts[$propertyName] = ':' . $propertyName;
             $queryArguments[$propertyName] = $nodeData[$propertyName];
             if (isset($propertyConfig['columnType'])) {
                 $queryTypes[$propertyName] = $propertyConfig['columnType'];
             }
         }
     }
     $connection->executeUpdate('INSERT INTO typo3_typo3cr_domain_model_nodedata (' . implode(', ', array_keys($queryParts)) . ') VALUES (' . implode(', ', $queryParts) . ')', $queryArguments, $queryTypes);
     foreach ($dimensionValues as $dimension => $values) {
         foreach ($values as $value) {
             $nodeDimension = array('persistence_object_identifier' => Algorithms::generateUUID(), 'nodedata' => $nodeData['Persistence_Object_Identifier'], 'name' => $dimension, 'value' => $value);
             $connection->insert('typo3_typo3cr_domain_model_nodedimension', $nodeDimension);
         }
     }
 }