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; }
/** * Returns a specified record * * @param string $table Table Name * @param array $condition Conditions to build query * * @return false|array */ public function getRecord($table, array $condition) { $em = $this->getEntityManager(); $qb = new QueryBuilder($em); $alias = $this->_getNewAlias(); $newCondition = array(); //remove alias and set the one used for this query foreach ($condition as $fieldName => $value) { $field = $this->_removeAlias($fieldName); $field = $alias . '.' . $field; $newCondition[$field] = $value; } $metadata = $em->getClassMetadata($table); $select = $alias; $qb->from($table, $alias); //create a query where all fields are contained, even the associations foreach ($metadata->associationMappings as $column => $detail) { //skip relations where the type is many to many or this side is not the //owning side if ($detail['type'] == ClassMetadata::MANY_TO_MANY || $detail['isOwningSide'] == false) { continue; } $joinAlias = $this->_getNewAlias(); $refColumn = $detail['joinColumns'][0]['referencedColumnName']; //join the table $qb->leftJoin($alias . '.' . $detail['fieldName'], $joinAlias); //append primary key from the joined table to the select //and use the defined column "AS" the name $select .= ', ' . $joinAlias . '.' . $refColumn . ' AS ' . $column; } $qb->select($select); $this->_createWhereConditions($qb, $newCondition); try { $result = $qb->getQuery()->getResult(Query::HYDRATE_ARRAY); $result = $this->_cleanQueryResults($result); } catch (Query\QueryException $e) { $result[0] = array(); Zend_Debug::dump($e); } return $result[0]; }
/** * {@inheritdoc} */ protected function addFromStatement($entityClassName, $tableAlias) { $this->qb->from($entityClassName, $tableAlias); }
/** * Configure the specific columns to select for the query * * @param QueryBuilder The Doctrine QueryBuilder object */ public function setSelect(QueryBuilder $qb) { $columns = array(); $partials = array(); // Make sure all related joins are added as needed columns. A column many entities deep may rely on a // column not specifically requested in the mData foreach (array_keys($this->assignedJoins) as $joinName) { $columns[$joinName] = 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 if (!in_array($this->rootEntityIdentifier, $columns[$this->tableName])) { array_unshift($columns[$this->tableName], $this->rootEntityIdentifier); } foreach ($columns as $columnName => $fields) { $partials[] = 'partial ' . $columnName . '.{' . implode(',', $fields) . '}'; } $qb->select(implode(',', $partials)); $qb->from($this->metadata->getName(), $this->tableName); }
private function applyFrom(QueryBuilderFilter $filter, QueryBuilder $queryBuilder) { $queryBuilder->from($filter->getFromEntity(), $filter->getFromAlias()); }
protected function getModuleRelation(QueryBuilder $qb, $entityModuleAttribute, $uri) { $uriParts = explode('/', $uri); $vendor = $uriParts[0]; $project = $uriParts[1]; $identifier = $uriParts[2]; $qb->from('CampaignChain\\CoreBundle\\Entity\\Module', 'm'); $qb->from('CampaignChain\\CoreBundle\\Entity\\Bundle', 'b'); $qb->where('b.id = m.bundle'); $qb->andWhere('b.name = :package'); $qb->andWhere('m.identifier = :module'); $qb->setParameter('package', $vendor . '/' . $project); $qb->setParameter('module', $identifier); $qb->andWhere($entityModuleAttribute . ' = m.id'); return $qb; }
/** * @param \APY\DataGridBundle\Grid\Column\Column[] $columns * @param int $page Page Number * @param int $limit Rows Per Page * @param int $gridDataJunction Grid data junction * @return \APY\DataGridBundle\Grid\Rows */ public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION) { $this->query = $this->manager->createQueryBuilder($this->class); $this->query->from($this->class, self::TABLE_ALIAS); $this->querySelectfromSource = clone $this->query; $bindIndex = 123; $serializeColumns = array(); $where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx(); foreach ($columns as $column) { $fieldName = $this->getFieldName($column, true); $this->query->addSelect($fieldName); $this->querySelectfromSource->addSelect($fieldName); if ($column->isSorted()) { $this->query->orderBy($this->getFieldName($column), $column->getOrder()); } if ($column->isFiltered()) { // Some attributes of the column can be changed in this function $filters = $column->getFilters('entity'); $isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION; $hasHavingClause = $column->hasDQLFunction(); $sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where); foreach ($filters as $filter) { $operator = $this->normalizeOperator($filter->getOperator()); $q = $this->query->expr()->{$operator}($this->getFieldName($column, false, $hasHavingClause), "?{$bindIndex}"); if ($filter->getOperator() == Column::OPERATOR_NLIKE) { $q = $this->query->expr()->not($q); } $sub->add($q); if ($filter->getValue() !== null) { $this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue())); } } if ($hasHavingClause) { $this->query->having($sub); } elseif ($isDisjunction) { $where->add($sub); } } if ($column->getType() === 'array') { $serializeColumns[] = $column->getId(); } } if ($where->count() > 0) { $this->query->where($where); } foreach ($this->joins as $alias => $field) { $this->query->leftJoin($field, $alias); $this->querySelectfromSource->leftJoin($field, $alias); } if ($page > 0) { $this->query->setFirstResult($page * $limit); } if ($limit > 0) { if ($maxResults !== null && $maxResults - $page * $limit < $limit) { $limit = $maxResults - $page * $limit; } $this->query->setMaxResults($limit); } elseif ($maxResults !== null) { $this->query->setMaxResults($maxResults); } if (!empty($this->groupBy)) { $this->query->resetDQLPart('groupBy'); $this->querySelectfromSource->resetDQLPart('groupBy'); foreach ($this->groupBy as $field) { $this->query->addGroupBy($this->getGroupByFieldName($field)); $this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field)); } } //call overridden prepareQuery or associated closure $this->prepareQuery($this->query); $query = $this->query->getQuery(); foreach ($this->hints as $hintKey => $hintValue) { $query->setHint($hintKey, $hintValue); } $items = $query->getResult(); // hydrate result $result = new Rows(); foreach ($items as $item) { $row = new Row(); foreach ($item as $key => $value) { $key = str_replace('::', '.', $key); if (in_array($key, $serializeColumns) && is_string($value)) { $value = unserialize($value); } $row->setField($key, $value); } //call overridden prepareRow or associated closure if (($modifiedRow = $this->prepareRow($row)) != null) { $result->addRow($modifiedRow); } } return $result; }
/** * * Método que seleciona a tabela de acordo com a necessidade da query da caixa de minutas * @param \Doctrine\ORM\QueryBuilder $queryBuilder * @param \Core_Dto_Search $dto */ protected function selectTableQuery(\Doctrine\ORM\QueryBuilder &$queryBuilder, \Core_Dto_Search $dto) { $hasEmAcompanhamento = FALSE; switch ($dto->getView()) { case self::TIPO_VISUALIZACAO_EM_ACOMPANHAMENTO: $queryBuilder->from('app:VwCaixaMinutaAcompanhamento', 'vcm'); $hasEmAcompanhamento = TRUE; break; default: $queryBuilder->from('app:VwCaixaMinuta', 'vcm'); break; } $this->addFilterStatus($queryBuilder, $dto); return $hasEmAcompanhamento; }
/** * @return void */ protected function selectTarget() { $alias = $this->aliases->get($this->getTargetId()); $this->builder->select($alias); $this->builder->from($this->getTargetId(), $alias); }
/** * Set select statement. * * @return DatatableData */ private function setSelect() { foreach ($this->selectFields as $key => $value) { // example: $qb->select('partial comment.{id, title}, partial post.{id, title}'); $this->qb->addSelect('partial ' . $key . '.{' . implode(',', $this->selectFields[$key]) . '}'); } $this->qb->from($this->metadata->getName(), $this->tableName); return $this; }
public function queryAssinatura(\Doctrine\ORM\QueryBuilder &$queryBuilder, $dto, $query) { $queryBuilder->from('app:PessoaAssinanteArtefato', 'pa')->innerJoin('pa.sqPessoaUnidadeOrg', 'ue')->innerJoin('ue.sqPessoaSgdoce', 'ps')->innerJoin('pa.sqArtefato', 'a')->innerJoin('ps.sqPessoaCorporativo', 'p')->innerJoin('p.sqProfissional', 'vprof')->where('pa.dtAssinado IS NULL'); $this->andWhereQuery($queryBuilder, $dto, $query); $queryBuilder->andWhere('a.sqArtefato = :sqArtefato')->setParameter('sqArtefato', $dto->getSqArtefato()); }
/** * {@inheritdoc} */ public function parse($value, QueryBuilder $qb) { if (!is_array($value)) { $value = Yaml::parse($value); } $processor = new Processor(); $value = $processor->processConfiguration(new QueryConfiguration(), $value); if (!isset($value['from'])) { throw new \RuntimeException('Missing mandatory "from" section'); } foreach ((array) $value['from'] as $from) { $qb->from($from['table'], $from['alias']); } if (isset($value['select'])) { foreach ($value['select'] as $select) { $qb->add('select', new Expr\Select($select), true); } } if (isset($value['distinct'])) { $qb->distinct((bool) $value['distinct']); } if (isset($value['groupBy'])) { $qb->groupBy($value['groupBy']); } if (isset($value['having'])) { $qb->having($value['having']); } $this->addJoin($qb, $value); $this->addWhere($qb, $value); $this->addOrder($qb, $value); return $qb; }