PHP QueryBuilder::from方法代码示例

 public function setUp()
 {
     $this->queryConfig = new QueryConfig();
     $this->queryConfig->setLimit(5)->setSelect(array('t.a', 't.b', 't.c'))->setPage(2)->setOrderBy(array('t.d' => true, 't.e' => false))->addParameter('query', '%xxx%')->getConstraints()->add('LOWER(t.a) LIKE :query')->add('LOWER(t.c) LIKE :query');
     $this->queryBuilder = new QueryBuilderMock();
     $this->queryBuilder->from('Table', 't');
     if ($this->queryConfig->getConstraints()->count() > 0) {
         $this->queryBuilder->andWhere($this->queryConfig->getConstraints());
     }
     if (count($this->queryConfig->getParameters()) > 0) {
         $this->queryBuilder->setParameters($this->queryConfig->getParameters());
     }
     $this->dataProvider = new DataProvider();
 }

 /**
  * 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);
         }
     }
 }

 /**
  * set entity
  * 
  * @param string $entity_name
  * @param string $entity_alias
  * 
  * @return Datatable 
  */
 public function setEntity($entity_name, $entity_alias)
 {
     $this->entity_name = $entity_name;
     $this->entity_alias = $entity_alias;
     $this->queryBuilder->from($entity_name, $entity_alias);
     return $this;
 }

 /**
  * Helper function which sets the fromPath and the selectPath for the order list query.
  * @param \Doctrine\ORM\QueryBuilder $builder
  * @return \Doctrine\ORM\QueryBuilder
  */
 protected function selectListQuery(\Doctrine\ORM\QueryBuilder $builder)
 {
     //select the different entities
     $builder->select(array('documents.id as id', 'documents.date as date', 'documents.typeId as typeId', 'documents.customerId as customerId', 'documents.orderId as orderId', 'documents.amount as amount', 'documents.documentId as documentId', 'documents.hash as hash', 'type.name as typeName'));
     //join the required tables for the order list
     $builder->from('Shopware\\Models\\Order\\Document\\Document', 'documents')->join('documents.type', 'type');
     return $builder;
 }

 /**
  * Set select from.
  *
  * @return $this
  */
 private function setSelectFrom()
 {
     foreach ($this->selectColumns as $key => $value) {
         $this->qb->addSelect('partial ' . $key . '.{' . implode(',', $this->selectColumns[$key]) . '}');
     }
     $this->qb->from($this->entity, $this->tableName);
     return $this;
 }

 /**
  * @param $columns \Sorien\DataGridBundle\Grid\Column\Column[]
  * @param $page int Page Number
  * @param $limit int Rows Per Page
  * @return \Sorien\DataGridBundle\Grid\Rows
  */
 public function execute($columns, $page = 0, $limit = 0)
 {
     $this->query = $this->manager->createQueryBuilder($this->class);
     $this->query->from($this->class, self::TABLE_ALIAS);
     $where = $this->query->expr()->andx();
     $sorted = false;
     foreach ($columns as $column) {
         $this->query->addSelect($this->getFieldName($column));
         if ($column->isSorted() && !$column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
             $sorted = true;
         } elseif (!$sorted && $column->isSorted() && $column->isDefaultSort()) {
             $this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
         }
         if ($column->isFiltered()) {
             if ($column->getFiltersConnection() == column::DATA_CONJUNCTION) {
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $where->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
             } elseif ($column->getFiltersConnection() == column::DATA_DISJUNCTION) {
                 $sub = $this->query->expr()->orx();
                 foreach ($column->getFilters() as $filter) {
                     $operator = $this->normalizeOperator($filter->getOperator());
                     $sub->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
                 }
                 $where->add($sub);
             }
             $this->query->where($where);
         }
     }
     foreach ($this->joins as $alias => $field) {
         $this->query->leftJoin($field, $alias);
     }
     if ($page > 0) {
         $this->query->setFirstResult($page * $limit);
     }
     if ($limit > 0) {
         $this->query->setMaxResults($limit);
     }
     //call overridden prepareQuery or associated closure
     $query = $this->prepareQuery(clone $this->query);
     $items = $query->getQuery()->getResult();
     // hydrate result
     $result = new Rows();
     foreach ($items as $item) {
         $row = new Row();
         foreach ($item as $key => $value) {
             $row->setField($key, $value);
         }
         //call overridden prepareRow or associated closure
         if (($modifiedRow = $this->prepareRow($row)) != null) {
             $result->addRow($modifiedRow);
         }
     }
     return $result;
 }

 /**
  * Create query based on current settings
  *
  * @param null $offset
  * @param null $itemCountPerPage
  *
  * @return QueryBuilder
  */
 public function createQuery($offset = null, $itemCountPerPage = null)
 {
     $service = $this->getService();
     $alias = $service->getAlias();
     $entity = $service->getEntityClass();
     //gets custom query if set
     if (!($this->_qb = $this->getGrid()->getCustomQueryBuilder())) {
         $this->_qb = $this->getService()->getEntityManager()->createQueryBuilder();
     }
     $this->_qb->select($alias);
     $this->_qb->from($entity, $alias);
     if ($itemCountPerPage) {
         $this->_qb->setMaxResults($itemCountPerPage);
     }
     if ($offset) {
         $this->_qb->setFirstResult($offset);
     }
     $filter = $this->getFilter();
     if (is_array($filter) && array_key_exists('field', $filter) && array_key_exists('value', $filter) && array_key_exists('expression', $filter) && array_key_exists('options', $filter)) {
         $this->filter($filter['field'], $filter['value'], $filter['expression'], $filter['options']);
     }
     if ($treeFilter = $this->getTreeFilter()) {
         $this->buildTreeFilterQuery($treeFilter);
     }
     $sort = $this->getSort();
     if (is_array($sort)) {
         $c = 0;
         foreach ($sort as $s) {
             if (isset($s['sidx'])) {
                 $field = $s['sidx'];
                 $direction = isset($s['sord']) ? $s['sord'] : 'asc';
                 if ($c) {
                     $this->_qb->addOrderBy($this->getService()->getAlias() . '.' . $field, $direction);
                 } else {
                     $this->_qb->orderBy($this->getService()->getAlias() . '.' . $field, $direction);
                 }
                 $c++;
             }
         }
     }
     return $this->_qb;
 }

 /**
  * Testing plugin
  *
  * @return void
  */
 public function testInvoke()
 {
     $options = new Options(['select' => 'a, b, c', 'joins' => ['b' => 'a.test', 'c' => 'b.test2']]);
     $plugin = new \Webowy\Doctrine\Query\DataTable\Plugin\Select($options);
     $entityManager = $this->getMockBuilder(EntityManager::class)->disableOriginalConstructor()->getMock();
     $queryBuilder = new QueryBuilder($entityManager);
     $queryBuilder->select('e');
     $queryBuilder->from('testTable', 'e');
     $plugin($queryBuilder);
     $this->assertEquals('SELECT a, b, c FROM testTable e LEFT JOIN a.test b LEFT JOIN b.test2 c', $queryBuilder->getDQL());
 }

 /**
  * Testing plugin
  *
  * @return void
  */
 public function testInvoke()
 {
     $options = new Options(['criteria' => ['name' => 'test', 'isActive' => true], 'fieldMapping' => ['name' => 'e.name', 'isActive' => 'e.isActive'], 'valueTypeMapping' => ['name' => Criteria::LIKE_LEFT]]);
     $plugin = new Criteria($options);
     $entityManager = $this->getMockBuilder(EntityManager::class)->disableOriginalConstructor()->getMock();
     $queryBuilder = new QueryBuilder($entityManager);
     $queryBuilder->select('e');
     $queryBuilder->from('testTable', 'e');
     $plugin($queryBuilder);
     $this->assertEquals('SELECT e FROM testTable e WHERE e.name LIKE ?1 AND e.isActive = ?2', $queryBuilder->getDQL());
 }

 /**
  * Create query object
  *
  * @return \Doctrine\ORM\QueryBuilder
  */
 public function createQuery()
 {
     $offset = ($this->_options->getPage() - 1) * $this->_options->getRows();
     if (!($this->_qb = $this->_options->getCustomQueryBuilder())) {
         $this->_qb = $this->getEntityManager()->createQueryBuilder();
     }
     $this->_qb->select($this->getAlias());
     $this->_qb->from($this->getEntityClass(), $this->getAlias());
     if ($joins = $this->getOptions()->getJoins()) {
         foreach ($joins as $index => $join) {
             $placeHolder = ':p' . $join['field'] . $index;
             $this->_qb->innerJoin($this->getAlias() . '.' . $join['field'], $join['alias'])->andWhere($join['alias'] . '.id = ' . $placeHolder)->setParameter($placeHolder, $join['key']);
         }
     }
     if ($itemCountPerPage = $this->_options->getRows()) {
         $this->_qb->setMaxResults($itemCountPerPage);
     }
     if ($offset) {
         $this->_qb->setFirstResult($offset);
     }
     if ($presets = $this->_options->getPresets()) {
         $this->addPresets($presets);
     }
     $filter = $this->_options->getFilters();
     if (is_array($filter) && array_key_exists('field', $filter) && array_key_exists('value', $filter) && array_key_exists('expression', $filter) && array_key_exists('options', $filter)) {
         $this->filter($filter['field'], $filter['value'], $filter['expression'], $filter['options']);
     }
     if ($treeFilter = $this->_options->getTreeFilter()) {
         $this->buildTreeFilterQuery($treeFilter);
     }
     $sort = $this->_options->getSortOrder();
     if (is_array($sort)) {
         $c = 0;
         foreach ($sort as $s) {
             if (!empty($s['sidx'])) {
                 $field = $s['sidx'];
                 $direction = isset($s['sord']) ? $s['sord'] : 'asc';
                 if ($c) {
                     $this->_qb->addOrderBy($this->getAlias() . '.' . $field, $direction);
                 } else {
                     $this->_qb->orderBy($this->getAlias() . '.' . $field, $direction);
                 }
                 $c++;
             }
         }
     }
     if ($subGridFilter = $this->_options->getSubGridFilter()) {
         foreach ($subGridFilter as $field => $value) {
             $this->_qb->andWhere($this->_qb->expr()->eq($this->getAlias() . '.' . $field, $value));
         }
     }
     return $this->_qb;
 }

 /**
  * @Route("/", name="homepage")
  */
 public function indexAction(Request $request)
 {
     /** @var EntityManager $em */
     $em = $this->getDoctrine()->getManager();
     $user = new EnboxUser();
     $user->setName('Alan');
     $quotation = new Quotation();
     $quote = new Quote();
     $company = new Company();
     $company->setName('Enbox');
     $timeline = new Timeline();
     $timeline->setCompany($company);
     $qo = new QuotationOpenActivityItem();
     $qs = new QuoteSentActivityItem();
     $timeline->addActivity($qo);
     $timeline->addActivity($qs);
     $qo->setActor($user);
     $qo->setObject($quotation);
     $qo->setTarget($company);
     $qs->setActor($company);
     $qs->setObject($quote);
     $qs->setTarget($quotation);
     $em->persist($user);
     $em->persist($quotation);
     $em->persist($quote);
     $em->persist($company);
     $em->persist($qo);
     $em->persist($qs);
     $em->persist($timeline);
     $em->flush();
     //dump($company);
     $qb = new QueryBuilder($em);
     $qb->from('AppBundle:Timeline', 't')->select('t')->leftJoin('t.activities', 'ta');
     $timeline = $qb->getQuery()->execute();
     dump($timeline);
     $qb = new QueryBuilder($em);
     $qb->from('AppBundle:ActivityItem', 'a')->select('a');
     $activities = $qb->getQuery()->execute();
     dump($activities);
     return $this->render('default/result.html.twig', array("timeline" => $timeline[0], "activities" => $activities));
 }

 /**
  * @param  Param        $params
  * @param  QueryBuilder $qb
  * @return $this
  */
 protected function addFrom(Params $params, QueryBuilder $qb)
 {
     $qb->from($this->getRepository(), $this->getRepositoryAlias());
     return $this;
 }

 public function getUniqueIndex(UniqueConstraint $constraint, $qbFunction = NULL)
 {
     $qb = new QueryBuilder($this->_em);
     // alle Felder des Constraints müssen in den Index
     foreach ($constraint->getKeys() as $key) {
         $qb->addSelect(sprintf('e.%s', $key));
     }
     // der Identifier muss auch mit selected werden
     $identifierField = $this->getIdentifier(TRUE);
     // foreach as $identifierField) {
     $qb->addSelect(sprintf('e.%s AS %s', $identifierField, 'identifier'));
     //}
     // alle Entities aus der Tabelle
     $qb->from($this->_entityName, 'e');
     return $this->deliverQuery($qb, $qbFunction, 'scalar');
     // als array ausgeben für performance (wir wollen ja eh ein Array zurückgeben)
 }

 /**
  * Configure the specific columns to select for the query
  *
  * @param QueryBuilder The Doctrine QueryBuilder object
  */
 public function setSelect(QueryBuilder $qb)
 {
     $columns = array();
     $partials = array();
     // Combine all columns to pull
     foreach ($this->associations as $column) {
         $parts = explode('.', $column['fullName']);
         $columns[$parts[0]][] = $parts[1];
     }
     // Partial column results on entities require that we include the identifier as part of the selection
     foreach ($this->identifiers as $joinName => $identifiers) {
         if (!in_array($identifiers[0], $columns[$joinName])) {
             array_unshift($columns[$joinName], $identifiers[0]);
         }
     }
     // Make sure to include the identifier for the main entity
     $identifiers = $this->metadata->getIdentifierFieldNames();
     if (!in_array($identifiers[0], $columns[$this->tableName])) {
         array_unshift($columns[$this->tableName], $identifiers[0]);
     }
     foreach ($columns as $columnName => $fields) {
         $partials[] = 'partial ' . $columnName . '.{' . implode(',', $fields) . '}';
     }
     $qb->select(implode(',', $partials));
     $qb->from($this->metadata->getName(), $this->tableName);
 }

 /**
  * Create a new query builder, and default FROM to the model of this Dao
  *
  * @return QueryBuilder
  */
 protected function newQueryBuilder()
 {
     $queryBuilder = new QueryBuilder($this->entityManager);
     $queryBuilder->select($this->prefix);
     $queryBuilder->from($this->model, $this->prefix);
     return $queryBuilder;
 }