PHP EntityManager::createQueryBuilder方法代碼示例

 protected function execute(InputInterface $input, OutputInterface $output)
 {
     $this->em = $this->container->getDoctrine()->getManager();
     /* @var $em EntityManager */
     $this->em->beginTransaction();
     $entities = array('CmsAuthentication:User', 'CmsAuthentication:Group', 'Cms:ApplicationLocalizationParameter', 'Cms:BlockPropertyMetadata', 'Cms:ReferencedElement\\ReferencedElementAbstract', 'Cms:BlockProperty', 'Cms:Abstraction\\Block', 'Cms:Abstraction\\PlaceHolder', 'Cms:LocalizationTag', 'Cms:Abstraction\\Localization', 'Cms:Abstraction\\RedirectTarget', 'Cms:PageLocalizationPath', 'Cms:Page', 'Cms:EditLock', 'Cms:TemplateLayout', 'Cms:Template', 'Cms:FileProperty', 'Cms:ImageSize', 'Cms:Image', 'Cms:File', 'Cms:Folder', 'Cms:FilePath');
     if ($input->getOption('clear')) {
         foreach ($entities as $entity) {
             //todo: also clean audit tables here
             $this->em->createQueryBuilder()->delete($entity)->getQuery()->execute();
         }
     }
     $dataFile = $this->container->getParameter('directories.project_root') . DIRECTORY_SEPARATOR . $input->getArgument('filename');
     if (!is_file($dataFile)) {
         throw new \RuntimeException(sprintf('The file [%s] does not exists.', $dataFile));
     }
     $this->dataDir = dirname($dataFile);
     //todo: validate it
     $data = Yaml::parse(file_get_contents($dataFile));
     //we need to maintain creation order
     $sections = array('group', 'user', 'image', 'template', 'page', 'pageLocalization');
     foreach ($sections as $section) {
         foreach ($data[$section] as $name => $definition) {
             $this->createEntity($section, $name, $definition);
         }
     }
     $this->em->flush();
     $this->em->commit();
 }

 /**
  * class constructor 
  * 
  * @param ContainerInterface $container 
  */
 public function __construct(ContainerInterface $container, $em)
 {
     $this->container = $container;
     $this->em = $em;
     $this->request = $this->container->get('request');
     $this->queryBuilder = $this->em->createQueryBuilder();
 }

 /**
  * @param string $searchQuery
  * @return \Doctrine\ORM\QueryBuilder
  */
 public function createDoctrineQueryBuilder($searchQuery)
 {
     $searchQuery = str_replace('*', '%', $searchQuery);
     $qb = $this->entityManager->createQueryBuilder();
     $searchQueryParts = explode(' ', $searchQuery);
     $query = $qb->select('entity')->from($this->entityName, 'entity');
     $subquery = null;
     $subst = 'a';
     foreach ($searchQueryParts as $i => $searchQueryPart) {
         $qbInner = $this->entityManager->createQueryBuilder();
         $paramPosistion = $i + 1;
         ++$subst;
         $whereQuery = $qb->expr()->orX();
         foreach ($this->searchColumns as $column) {
             $whereQuery->add($qb->expr()->like($subst . '.' . $column, '?' . $paramPosistion));
         }
         $subqueryInner = $qbInner->select($subst . '.' . $this->idName)->from($this->entityName, $subst)->where($whereQuery);
         if ($subquery != null) {
             $subqueryInner->andWhere($qb->expr()->in($subst . '.' . $this->idName, $subquery->getQuery()->getDql()));
         }
         $subquery = $subqueryInner;
         $query->setParameter($paramPosistion, $searchQueryPart);
     }
     $query->where($qb->expr()->in('entity.' . $this->idName, $subquery->getQuery()->getDql()));
     return $query;
 }

 protected function getAddressByKyc($kyc)
 {
     $qb = $this->em->createQueryBuilder();
     $qb->select('a')->from('Multichain\\Console\\Entity\\Address', 'a')->where('a.kycname = ?1')->setParameter(1, $kyc);
     $query = $qb->getQuery();
     return $query->getOneOrNullResult();
 }

 /**
  * Find a session by its id
  *
  * @param string $id
  *
  * @return Session
  */
 public function findById($id)
 {
     $qb = $this->em->createQueryBuilder();
     $qb->select('s')->from('User\\Model\\Session', 's')->where('s.id = ?1');
     $qb->setParameter(1, $id);
     return $qb->getQuery()->getOneOrNullResult();
 }

 /**
  * Get all activities which a user is signed up for.
  *
  * @param int $userId
  *
  * @return \Activity\Model\ActivitySignup
  */
 public function getSignedUpActivities($userId)
 {
     $qb = $this->em->createQueryBuilder();
     $qb->select('a')->from('Activity\\Model\\UserActivitySignup', 'a')->join('a.user', 'u')->where('u.lidnr = ?1')->setParameter(1, $userId);
     $result = $qb->getQuery()->getResult();
     return $result;
 }

 public function getMainAdvs()
 {
     $advs = ['trade' => [], 'find' => [], 'gift' => []];
     $qb = $this->em->createQueryBuilder();
     $trade = $qb->select('a')->from('NaidusvoeBundle:Advertisment', 'a')->where($qb->expr()->andX($qb->expr()->isNotNull('a.onMainUntill'), $qb->expr()->eq('a.typeID', Advertisment::TRADE)))->getQuery()->getResult();
     $type = $this->em->find('NaidusvoeBundle:AdvertismentType', Advertisment::TRADE);
     $advsCount = count($trade);
     $advs['trade'] = $this->getRandom($trade, AdvertisementService::MAIN_ADV_COUNT);
     if ($advsCount < 5) {
         $advs['trade'] = array_merge($advs['trade'], $this->generateDummyAdvs(AdvertisementService::MAIN_ADV_COUNT - $advsCount, $type));
     }
     $qb = $this->em->createQueryBuilder();
     $find = $qb->select('a')->from('NaidusvoeBundle:Advertisment', 'a')->where($qb->expr()->andX($qb->expr()->isNotNull('a.onMainUntill'), $qb->expr()->eq('a.typeID', Advertisment::FIND)))->getQuery()->getResult();
     $type = $this->em->find('NaidusvoeBundle:AdvertismentType', Advertisment::FIND);
     $advsCount = count($find);
     $advs['find'] = $this->getRandom($find, AdvertisementService::MAIN_ADV_COUNT);
     if ($advsCount < 5) {
         $advs['find'] = array_merge($advs['find'], $this->generateDummyAdvs(AdvertisementService::MAIN_ADV_COUNT - $advsCount, $type));
     }
     $qb = $this->em->createQueryBuilder();
     $gift = $qb->select('a')->from('NaidusvoeBundle:Advertisment', 'a')->where($qb->expr()->andX($qb->expr()->isNotNull('a.onMainUntill'), $qb->expr()->eq('a.typeID', Advertisment::GIFT)))->getQuery()->getResult();
     $type = $this->em->find('NaidusvoeBundle:AdvertismentType', Advertisment::GIFT);
     $advsCount = count($gift);
     $advs['gift'] = $this->getRandom($gift, AdvertisementService::MAIN_ADV_COUNT);
     if ($advsCount < 5) {
         $advs['gift'] = array_merge($advs['gift'], $this->generateDummyAdvs(AdvertisementService::MAIN_ADV_COUNT - $advsCount, $type));
     }
     return $advs;
 }

 public function loadUserByOAuthUserResponse(UserResponseInterface $response)
 {
     //$doctrine = $em = $this->getEntityManager();
     //echo 'fsfd';
     //print_r($doctrine);
     //die;
     try {
         $userdata = $response->getResponse();
         $fbid = $userdata['id'];
         $name = $userdata['name'];
         $email = $userdata['email'];
         print_r($userdata);
         $user = $this->_em->createQueryBuilder()->select('u')->from('Yasoon\\Site\\Entity\\AuthorEntity', 'u')->where('u.facebookId = :fbid')->setParameter('fbid', $fbid)->getQuery()->getSingleResult();
         if (!is_object($user)) {
             $user = (new AuthorEntity())->setName($name)->setEmail('')->setPassword('')->setSubscribed(1)->setFacebookId($fbid)->setPublicationDate(new \DateTime())->setRole(1);
             $this->_em->persist($user);
             $this->_em->flush();
         }
     } catch (\Exception $e) {
         return ['error' => true, 'errorText' => $e->getMessage()];
     }
     //$user = $this->_em->getRepository('Yasoon\Site\Entity\AuthorEntity')->find(41);
     //print_r($user->getName());
     return $user;
 }

 /**
  * Builds the raw query
  *
  * @return void
  */
 protected function buildQuery()
 {
     $this->queryBuilder = $this->connection->createQueryBuilder();
     $this->queryBuilder->select((array) $this->conf->select);
     /*
      * Main table
      */
     foreach ($this->conf->from as $from) {
         $this->queryBuilder->from($from->name, $from->alias);
     }
     /*
      * Inner join, right join, left join
      */
     $joinTypes = ['join', 'innerJoin', 'leftJoin', 'rightJoin'];
     foreach ($joinTypes as $joinType) {
         if (isset($this->conf->{$joinType})) {
             $joins = $this->conf->{$joinType};
             $this->buildJoins($joinType, $joins);
         }
     }
     /*
      * Condition
      */
     if (isset($this->conf->where)) {
         foreach ($this->conf->where as $where) {
             $this->queryBuilder->andWhere($where);
         }
     }
     if (isset($this->conf->parameters)) {
         foreach ($this->conf->parameters as $key => $value) {
             $this->queryBuilder->setParameter($key, $value);
         }
     }
 }

 /**
  * @param NQLQuery $nqlQuery
  * @param bool $skipSelection
  * @return QueryBuilder
  * @throws SyntaxErrorException
  */
 public function convert(NQLQuery $nqlQuery, bool $skipSelection) : QueryBuilder
 {
     $this->checkTables($nqlQuery->getFrom());
     /** @var QueryBuilder $query */
     $query = $this->em->createQueryBuilder();
     /** @var string $columnDefaultAlias */
     $columnDefaultAlias = count($nqlQuery->getFrom()->getTables()) === 1 ? $nqlQuery->getFrom()->getTables()[0]->getAlias() : '';
     if ($columnDefaultAlias === 'group') {
         $columnDefaultAlias = '_group';
     }
     if ($skipSelection) {
         $query->select($columnDefaultAlias);
     } else {
         if ($nqlQuery->getSelect()->getColumns()) {
             /** @var Column $column */
             foreach ($nqlQuery->getSelect()->getColumns() as $column) {
                 $query->addSelect(($column->getAlias() ?? $columnDefaultAlias) . '.' . $column->getName());
             }
         } else {
             $query->select($columnDefaultAlias);
         }
     }
     foreach ($nqlQuery->getFrom()->getTables() as $table) {
         $query->from($this->entities[strtolower($table->getName())], $table->getAlias());
     }
     if ($nqlQuery->getWhere()->getConditions()) {
         $paramWhere = $this->getParametrizedWhere($nqlQuery->getWhere()->getConditions(), $columnDefaultAlias);
         $query->where($paramWhere['clause'])->setParameters($paramWhere['params']);
     }
     $columns = $this->getAllColumns($skipSelection ? Select::getBlank() : $nqlQuery->getSelect(), $nqlQuery->getWhere(), $nqlQuery->getOrderBy());
     $alreadyJoined = [];
     foreach ($columns as $column) {
         if (count($column->getJoinWith())) {
             $joinWith = $column->getJoinWith();
             $iMax = count($joinWith);
             for ($i = 0; $i < $iMax; $i++) {
                 if (!array_key_exists($joinWith[$i], $alreadyJoined)) {
                     if ($i === 0) {
                         $column = ($column->getAlias() === null ? $columnDefaultAlias : $column->getAlias()) . '.' . $column->getJoinWith()[$i];
                     } else {
                         $column = $joinWith[$i - 1] . '.' . $joinWith[$i];
                     }
                     $query->innerJoin($column, $joinWith[$i]);
                     $alreadyJoined[$joinWith[$i]] = null;
                 }
             }
         }
     }
     /** @var OrderingColumn $column */
     foreach ($nqlQuery->getOrderBy()->getColumns() as $column) {
         $query->addOrderBy((count($column->getJoinWith()) ? $column->getJoinWith()[count($column->getJoinWith()) - 1] : ($column->getAlias() === null ? $columnDefaultAlias : $column->getAlias())) . '.' . $column->getName(), $column->getOrdering());
     }
     if (null !== $nqlQuery->getLimit()) {
         $query->setMaxResults($nqlQuery->getLimit());
     }
     if (null !== $nqlQuery->getOffset()) {
         $query->setFirstResult($nqlQuery->getOffset());
     }
     return $query;
 }

 public function getGlobals()
 {
     if (isset($this->site)) {
         $siteIds = $this->site->getChildSiteIds();
         array_push($siteIds, $this->site->getId());
         $connection = $this->container->get('doctrine')->getConnection();
         $sql = "SELECT s.name, t.name AS type, d.domain, (SELECT COUNT(l.id) FROM lead l WHERE l.lead_status_id = 1 AND l.site_id=s.id) AS leads, ";
         $sql .= "(SELECT COUNT(a.id) FROM loan_application a WHERE a.status <= 1 AND a.deleted = 0 AND a.site_id=s.id) AS loans FROM site s ";
         $sql .= "INNER JOIN domain d ON s.primary_domain_id=d.id ";
         $sql .= "LEFT JOIN site_type t ON s.site_type_id=t.id ";
         $sql .= sprintf("WHERE s.id IN (%s) ", implode(',', $siteIds));
         $sql .= "GROUP BY s.id";
         $stmt = $connection->prepare($sql);
         //echo $sql; exit;
         $stmt->execute();
         $siteSummary = $stmt->fetchAll();
         // get loans
         $qb = $this->em->createQueryBuilder()->select('count(l.id)')->from('SudouxMortgageBundle:LoanApplication', 'l')->where('l.status = 0 OR l.status = 1')->andWhere('l.site IN (:site_ids)')->andWhere('l.deleted = 0')->setParameter('site_ids', $siteIds);
         $newLoans = $qb->getQuery()->getSingleScalarResult();
         // get docs
         return array('newLoans' => $newLoans, 'siteSummary' => $siteSummary);
     } else {
         return array();
     }
 }

 /**
  * Find all organs of this member.
  *
  * @param MemberModel $member
  *
  * @return array Of organs
  */
 public function findOrgans(MemberModel $member)
 {
     $qb = $this->em->createQueryBuilder();
     $qb->select('DISTINCT o')->from('Decision\\Model\\Organ', 'o')->join('o.members', 'om')->join('om.member', 'm')->where('m.lidnr = :lidnr')->andWhere('om.dischargeDate IS NULL');
     $qb->setParameter('lidnr', $member->getLidnr());
     return $qb->getQuery()->getResult();
 }

 /**
  * Returns the previous photo in the album to display
  *
  * @param \Photo\Model\Photo $photo
  *
  * @return \Photo\Model\Album|null Photo if there is a previous
  * photo, null otherwise
  */
 public function getPreviousPhoto($photo)
 {
     $qb = $this->em->createQueryBuilder();
     $qb->select('a')->from('Photo\\Model\\Photo', 'a')->where('a.dateTime < ?1 AND a.album = ?2')->setParameter(1, $photo->getDateTime())->setParameter(2, $photo->getAlbum())->orderBy('a.dateTime', 'DESC')->setMaxResults(1);
     $res = $qb->getQuery()->getResult();
     return empty($res) ? null : $res[0];
 }

 /**
  * {@inheritdoc}
  */
 public function flushCarts()
 {
     $expiredCarts = $this->entityManager->createQueryBuilder()->select('c')->from($this->getClass(), 'c')->where('c.locked = false AND c.expiresAt < ?1')->setParameter(1, new \DateTime("now"))->getQuery()->useResultCache(false)->getResult();
     foreach ($expiredCarts as $cart) {
         $this->removeCart($cart);
     }
 }

 public function setEntityClass($entityClass)
 {
     $this->qb = $this->em->createQueryBuilder();
     $this->qb->add('select', 'e');
     $this->qb->add('from', $entityClass . ' e');
     return $this;
 }