/**
* @test
*/
public function should_create_complex_query()
{
$specification = $this->userSpecificationFactory->status('approved')->orX($this->userSpecificationFactory->status('declined')->andX($this->userSpecificationFactory->name('John')));
$this->queryBuilder->select('user')->from('User', 'user')->where($specification->expr())->setParameters($specification->parameters());
$this->assertEquals(['status_1' => 'approved', 'status_2' => 'declined', 'name_3' => 'John'], $specification->parameters());
$this->assertEquals('SELECT user FROM User user WHERE user.status = :status_1 OR (user.status = :status_2 AND user.name = :name_3)', $this->queryBuilder->getDQL());
}
/**
* @param ChannelConditionInterface $condition
* @return ChannelInterface
*/
public function searchOne(ChannelConditionInterface $condition)
{
$this->qb = $this->em->createQueryBuilder();
$this->qb->select('ch')->from('CoreBundle:Channel', 'ch');
$this->initQueryBuilder($condition);
return $this->qb->getQuery()->getOneOrNullResult();
}
/**
* Constructor.
*
* @access public
* @param ORM\QueryBuilder $query
* @since 1.0.0-alpha
* @version 1.0.0-alpha
*/
public function __construct(ORM\QueryBuilder $query)
{
$this->query = $query;
$this->oCountingQuery = clone $this->query;
$this->oCountingQuery->select('COUNT(DISTINCT t.id)');
$this->iCount = $this->getCountingQuery()->single();
}
/**
* Constructor
*
* @param EntityManager $manager
* @param string $class
*/
public function __construct(EntityManager $manager, $class)
{
$this->manager = $manager;
$this->metadata = $manager->getClassMetadata($class);
$this->qBuilder = $manager->createQueryBuilder();
$this->qBuilder->select('e')->from($class, 'e');
}
/**
* @param CompanyConditionInterface $condition
* @return Company[]
*/
public function search(CompanyConditionInterface $condition)
{
$this->qb = $this->em->createQueryBuilder();
$this->qb->select('co')->from('CoreBundle:Company', 'co');
$this->initQueryBuilder($condition);
$this->preparePagination($this->qb, $condition);
return $this->qb->getQuery()->getResult();
}
/**
* Return an article array by tag
* @param $strings
* @return array
*/
public function searchArticlesByTags($strings)
{
$strings = $this->setCleanRequest($strings);
$this->qb->select('t')->from('AppBundle:Tag', 't')->where('t.slug LIKE :strings')->addOrderBy('t.frequency', 'DESC')->setParameter('strings', $strings);
$q = $this->qb->getQuery();
$tags = $q->getResult();
$articlesArray = array();
foreach ($tags as $tag) {
$articles = $tag->getArticles();
foreach ($articles as $article) {
array_push($articlesArray, $article);
}
}
return $articlesArray;
}
public function buildCount(QueryBuilder $queryBuilder) : Query
{
if (!empty($this->name)) {
$queryBuilder->where($queryBuilder->expr()->like('o.name.name', ':name'))->setParameter('name', '%' . $this->name . '%');
}
return $queryBuilder->select($queryBuilder->expr()->count('o.id'))->from(Organization::class, 'o')->leftJoin('o.organizationMembers', 'om')->leftJoin('o.owners', 'ow')->andWhere($queryBuilder->expr()->orX($queryBuilder->expr()->eq('om.userId', ':userId'), $queryBuilder->expr()->eq('ow.userId', ':userId')))->setParameter('userId', $this->userId->id())->getQuery();
}
/**
* @route: blog_home
* Homepage controller
*/
public function homeAction()
{
// entity manager
$em = $this->getEm();
$qb = new QueryBuilder($em);
// dql query
$qb->select('p,c,t')
->from('Bundle\BlogBundle\Entity\Post', 'p')
->join('p.category', 'c')
->join('p.tags', 't')
->orderBy('p.date', 'DESC');
// array of objects
$posts = $qb->getQuery()->getResult();
$query = $em->createQuery('SELECT c,p FROM Bundle\BlogBundle\Entity\Category c JOIN c.posts p');
$categories = $query->getResult();
$query = $em->createQuery('SELECT t,p FROM Bundle\BlogBundle\Entity\Tag t JOIN t.posts p');
$tags = $query->getResult();
return $this->render('BlogBundle:Blog:homepage.html.twig', array(
'posts' => $posts,
'categories' => $categories,
'tags' => $tags
));
}
/**
* Build the count of filtered query
*
* @return QueryBuilder
*/
public function getFilteredCountQuery()
{
$this->buildFilteredQuery();
$this->currentQueryBuilder->select('COUNT(DISTINCT ' . $this->getDistinct() . ')');
$this->currentQueryBuilder->resetDQLPart('orderBy');
return $this->currentQueryBuilder;
}
/**
* @extra:Route("/", name="_alf")
* @extra:Template()
*/
public function leftMenuAction()
{
// $catalog = new Catalogs();
// $catalog->setCategory('1');
// $catalog->setName('skarpety wpisane');
// $em = $this->get('doctrine.orm.entity_manager');
// $em->persist($catalog);
// $em->flush();
// $menu = $this->get('doctrine.orm.entity_manager')
// ->createQuery('SELECT c FROM AlfShopBundle:Catalogs c ORDER BY c.id DESC')
// ->getResult()
// ;
// $menu = array( array('name' => 'A'), array('name' => 'B'),array('name' => 'C'));
$em = $this->get('doctrine.orm.entity_manager');
$qb = new QueryBuilder($em);
$dql = $qb->select('c.name')->from('AlfShopBundle:Catalogs', 'c')->getQuery();
$menu = $dql->getResult();
$em = $this->get('doctrine.orm.entity_manager');
$qb = new QueryBuilder($em);
$dql = $qb->select('c')->from('AlfShopBundle:Catalogs', 'c');
$query = $em->createQuery($dql);
$adapter = $this->get('knplabs_paginator.adapter');
$adapter->setQuery($query);
$adapter->setDistinct(true);
$paginator = new \Zend\Paginator\Paginator($adapter);
$paginator->setCurrentPageNumber($this->get('request')->query->get('page', 1));
$paginator->setItemCountPerPage(2);
$paginator->setPageRange(5);
// $menu = $m ->getResult();
// $menu = $em->find('AlfShopBundle:Catalogs');
// $em->persist($menu);
// $em->flush();
// $menu = array('A', 'B');
return $this->render('AlfShopBundle:Shop:leftMenu.html.twig', array('menuList' => $paginator, 'paginator' => $paginator));
}
/**
* Builds the query for the given data request object
*
* @access public
* @param \Zepi\DataSource\Core\Entity\DataRequest $dataRequest
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param string $entity
* @param string $tableCode
*/
public function buildDataRequestQuery(DataRequest $dataRequest, QueryBuilder $queryBuilder, $entity, $tableCode)
{
$queryBuilder->select($tableCode)->from($entity, $tableCode);
$hasWhere = false;
$i = 1;
foreach ($dataRequest->getFilters() as $filter) {
$whereQuery = $tableCode . '.' . $filter->getFieldName() . ' ' . $filter->getMode() . ' :' . $i;
if ($hasWhere) {
$queryBuilder->andWhere($whereQuery);
} else {
$queryBuilder->where($whereQuery);
$hasWhere = true;
}
$queryBuilder->setParameter($i, $filter->getNeededValue());
$i++;
}
// Sorting
if ($dataRequest->hasSorting()) {
$mode = 'ASC';
if (in_array($dataRequest->getSortByDirection(), array('ASC', 'DESC'))) {
$mode = $dataRequest->getSortByDirection();
}
$queryBuilder->orderBy($tableCode . '.' . $dataRequest->getSortBy(), $mode);
}
// Offset
if ($dataRequest->hasRange()) {
$queryBuilder->setFirstResult($dataRequest->getOffset());
$queryBuilder->setMaxResults($dataRequest->getNumberOfEntries());
}
}
public function implement(DoctrineQueryBuilder $queryBuilder)
{
if (!$this->getValue()) {
return $queryBuilder;
}
$queryBuilder->select('COUNT(' . QueryBuilder::DEFAULT_ALIAS . '.id)');
}
public function getQueryBuilder()
{
if (!$this->queryBuilder) {
$this->queryBuilder = $this->em->createQueryBuilder();
$this->queryBuilder->select('root')->from($this->getClassMetadata()->getName(), 'root');
}
return $this->queryBuilder;
}
/**
* 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;
}
/**
* @dataProvider addDatePartsSelectProvider
*/
public function testAddDatePartsSelect($start, $end, $expects)
{
$start = new \DateTime($start, new \DateTimeZone('UTC'));
$end = new \DateTime($end, new \DateTimeZone('UTC'));
$queryBuilder = new QueryBuilder($this->getTestEntityManager());
$queryBuilder->select('id')->from('Oro\\Bundle\\DashboardBundle\\Tests\\Unit\\Fixtures\\FirstTestBundle\\Entity\\TestEntity', 't');
$this->helper->addDatePartsSelect($start, $end, $queryBuilder, 't.createdAt');
$this->assertEquals($expects, $queryBuilder->getDQL());
}
/**
* 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(['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());
}
/**
* 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());
}
/**
* @param ClientConditionInterface $condition
* @return array
*/
public function resultCount(ClientConditionInterface $condition)
{
$this->qb = $this->em->createQueryBuilder();
$this->qb->select('COUNT(c)')->from('CoreBundle:Client', 'c');
$this->initQueryBuilder($condition);
try {
$count = (int) $this->qb->getQuery()->getSingleScalarResult();
} catch (NoResultException $e) {
$count = 0;
}
return $count;
}
protected function getFilteredPostsIds(array $identifiers, $mask, array $orX = [])
{
$qb = new QueryBuilder($this->entityManager);
$query = $qb->select('p')->from('Tests\\AlexDpy\\Acl\\Post', 'p')->getQuery();
$aclFilter = new DoctrineOrmAclFilter($query);
$aclFilter->setAclSchema($this->aclSchema);
$aclFilter->apply('p', 'id', 'post-', $identifiers, $mask, $orX);
$result = array_map(function (Post $post) {
return (int) $post->getId();
}, $query->getResult());
return [$result];
}
/**
* Get ids of entities from the QueryBuilder
*
* @return array
*/
protected function getEntitiesIds()
{
if (null === $this->entitiesIds) {
$rootAlias = current($this->queryBuilder->getRootAliases());
$rootIdExpr = sprintf('%s.id', $rootAlias);
$from = current($this->queryBuilder->getDQLPart('from'));
$this->queryBuilder->select($rootIdExpr)->resetDQLPart('from')->from($from->getFrom(), $from->getAlias(), $rootIdExpr)->groupBy($rootIdExpr);
$results = $this->queryBuilder->getQuery()->getArrayResult();
$this->entitiesIds = array_keys($results);
}
return $this->entitiesIds;
}
/**
* 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;
}
/**
* @param QueryBuilder $queryBuilder
* @return void
*/
public function __invoke(QueryBuilder $queryBuilder)
{
$options = $this->getOptions();
if ($options->getSelect()) {
$queryBuilder->select($options->getSelect());
}
if ($options->getJoins() && is_array($options->getJoins())) {
foreach ($options->getJoins() as $alias => $join) {
$queryBuilder->leftJoin($join, $alias);
}
}
}
public function testJoinACLWithoutEntityAliasAndClass()
{
$em = $this->getMock('Doctrine\\ORM\\EntityManager', [], [], '', false);
$identity = $this->getMockForAbstractClass('MyCLabs\\ACL\\Model\\SecurityIdentityInterface');
$qb = new QueryBuilder($em);
$qb->select('test')->from('test', 'test');
ACLQueryHelper::joinACL($qb, $identity, Actions::VIEW);
$dql = 'SELECT test FROM test test INNER JOIN MyCLabs\\ACL\\Model\\Authorization authorization ' . 'WITH test.id = authorization.entityId ' . 'WHERE authorization.entityClass = :acl_entity_class ' . 'AND authorization.securityIdentity = :acl_identity ' . 'AND authorization.actions.view = true';
$this->assertEquals($dql, $qb->getDQL());
$this->assertSame('test', $qb->getParameter('acl_entity_class')->getValue());
$this->assertSame($identity, $qb->getParameter('acl_identity')->getValue());
}
public function implement(DoctrineQueryBuilder $queryBuilder)
{
if ($this->payload->get('count')->getValue() === true) {
return $queryBuilder;
}
foreach ($this->getValue() as $i => $field) {
if ($i === 0) {
$queryBuilder->select($field);
} else {
$queryBuilder->addSelect($field);
}
}
}
/**
* @SuppressWarnings(PHPMD.ExcessiveMethodLength)
*/
public function testVisitDatasource()
{
$qb = new QueryBuilder($this->getTestEntityManager());
$qb->select(['user.id', 'user.name as user_name', 'user.status as user_status'])->from('Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser', 'user')->join('user.address', 'address');
$manager = $this->getMockBuilder('Oro\\Bundle\\QueryDesignerBundle\\QueryDesigner\\Manager')->disableOriginalConstructor()->getMock();
$manager->expects($this->any())->method('createFilter')->will($this->returnCallback(function ($name, $params) {
return $this->createFilter($name, $params);
}));
$extension = new OrmDatasourceExtension(new RestrictionBuilder($manager));
$datasource = $this->getMockBuilder('Oro\\Bundle\\DataGridBundle\\Datasource\\Orm\\OrmDatasource')->disableOriginalConstructor()->getMock();
$datasource->expects($this->once())->method('getQueryBuilder')->will($this->returnValue($qb));
$config = DatagridConfiguration::create(['source' => ['query_config' => ['filters' => [['column' => 'user_name', 'filter' => 'string', 'filterData' => ['type' => '2', 'value' => 'test_user_name'], 'columnAlias' => 'user_name'], 'AND', [['column' => 'user_status', 'filter' => 'datetime', 'filterData' => ['type' => '2', 'value' => ['start' => '2013-11-20 10:30', 'end' => '2013-11-25 11:30']]], 'AND', [[['column' => 'address.country', 'filter' => 'string', 'filterData' => ['type' => '1', 'value' => 'test_address_country'], 'columnAlias' => 'address_country'], 'OR', ['column' => 'address.city', 'filter' => 'string', 'filterData' => ['type' => '1', 'value' => 'test_address_city']]], 'OR', ['column' => 'address.zip', 'filter' => 'string', 'filterData' => ['type' => '1', 'value' => 'address_zip']]]]]]]]);
$extension->visitDatasource($config, $datasource);
$result = $qb->getDQL();
$counter = 0;
$result = preg_replace_callback('/(:[a-z]+)(\\d+)/', function ($matches) use(&$counter) {
return $matches[1] . ++$counter;
}, $result);
$this->assertEquals('SELECT user.id, user.name as user_name, user.status as user_status ' . 'FROM Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser user ' . 'INNER JOIN user.address address ' . 'WHERE user_name NOT LIKE :string1 AND (' . '(user_status < :datetime2 OR user_status > :datetime3) AND ' . '(address.country LIKE :string4 OR address.city LIKE :string5 OR address.zip LIKE :string6)' . ')', $result);
}
/**
* @dataProvider visitDatasourceProvider
*/
public function testVisitDatasource($source, $expected)
{
$qb = new QueryBuilder($this->getTestEntityManager());
$qb->select(['user.id', 'user.name as user_name', 'user.status as user_status'])->from('Oro\\Bundle\\QueryDesignerBundle\\Tests\\Unit\\Fixtures\\Models\\CMS\\CmsUser', 'user')->join('user.address', 'address');
$manager = $this->getMockBuilder('Oro\\Bundle\\QueryDesignerBundle\\QueryDesigner\\Manager')->disableOriginalConstructor()->getMock();
$manager->expects($this->any())->method('createFilter')->will($this->returnCallback(function ($name, $params) {
return $this->createFilter($name, $params);
}));
$extension = new OrmDatasourceExtension(new RestrictionBuilder($manager));
$datasource = $this->getMockBuilder('Oro\\Bundle\\DataGridBundle\\Datasource\\Orm\\OrmDatasource')->disableOriginalConstructor()->getMock();
$datasource->expects($this->once())->method('getQueryBuilder')->will($this->returnValue($qb));
$config = DatagridConfiguration::create($source);
$extension->visitDatasource($config, $datasource);
$result = $qb->getDQL();
$counter = 0;
$result = preg_replace_callback('/(:[a-z]+)(\\d+)/', function ($matches) use(&$counter) {
return $matches[1] . ++$counter;
}, $result);
$this->assertEquals($expected, $result);
}
/**
* @param QueryBuilder $qb
* @param Search $search
* @return QueryBuilder
*/
public function map(QueryBuilder $qb, Search $search)
{
$qb->select('c, s')->from('EtuModuleCovoitBundle:Covoit', 'c')->leftJoin('c.subscriptions', 's');
if (!$search->olds) {
$qb->andWhere('c.date > CURRENT_DATE()');
}
if ($search->startCity) {
$qb->andWhere('c.startCity = :startCity')->setParameter('startCity', $search->startCity->getId());
}
if ($search->endCity) {
$qb->andWhere('c.endCity = :endCity')->setParameter('endCity', $search->endCity->getId());
}
if ($search->date) {
if ($search->dateBeforeAfter) {
$before = clone $search->date;
$before->add(\DateInterval::createFromDateString('-1 day'));
$after = clone $search->date;
$after->add(\DateInterval::createFromDateString('1 day'));
$qb->andWhere('c.date BETWEEN :before AND :after')->setParameter('before', $before->format('Y-m-d') . ' 00:00:00')->setParameter('after', $after->format('Y-m-d') . ' 00:00:00');
} else {
$qb->andWhere('c.date = :date')->setParameter('date', $search->date->format('Y-m-d') . ' 00:00:00');
}
}
if ($search->priceMax) {
$qb->andWhere('c.price <= :priceMax')->setParameter('priceMax', $search->priceMax);
}
if ($search->hourMin) {
$qb->andWhere('c.startHour >= :hourMin')->setParameter('hourMin', $search->hourMin->format('H:i:s'));
}
if ($search->hourMax) {
$qb->andWhere('c.startHour <= :hourMax')->setParameter('hourMax', $search->hourMax->format('H:i:s'));
}
if ($search->keywords) {
$qb->andWhere('c.notes LIKE :keywords')->setParameter('keywords', '%' . implode('%', explode(' ', $search->keywords)) . '%');
}
if ($search->placesLeft) {
$qb->having('(c.capacity - COUNT(s)) >= :placesLeft')->setParameter('placesLeft', $search->placesLeft);
}
return $qb;
}
public function gridFields(QueryBuilder $qb, $params)
{
$select = array();
$select[] = 'o.id as DT_RowId';
foreach ($params['columns'] as $columns) {
if ($columns['name'] && substr($columns['name'], 0, 1) != '_') {
$field = $this->_getFieldName($columns['name']);
$fieldAlias = str_replace(".", "_", $columns['name']);
$select[] = $field . ' as ' . $fieldAlias;
}
}
$qb->select(implode(", ", $select));
foreach ($params['order'] as $order) {
if (substr($params['columns'][$order['column']]['name'], 0, 1) != '_') {
$field = $this->_getFieldName($params['columns'][$order['column']]['name']);
$orderColName = $field;
$qb->addOrderBy($orderColName, $order['dir']);
}
}
$qb->setFirstResult($params['start'])->setMaxResults($params['length']);
return $qb->getQuery()->getArrayResult();
}
public function buildSelectQuery(QueryBuilder $queryBuilder, $parameters, $class, $sort = array(), $join = "", $joinParams = array())
{
if (!empty($joinParams)) {
foreach ($joinParams as $key => $joinParam) {
unset($parameters[$key]);
}
}
if (!empty($sort)) {
unset($parameters[$sort['key']]);
}
$from = "DraftBundle:" . $class;
$queryBuilder->select("c")->from($from, "c");
if (isset($parameters['limit'])) {
$limit = $parameters['limit'];
$queryBuilder->setMaxResults($limit);
if (isset($parameters['page'])) {
$page = ($parameters['page'] - 1) * $limit;
$queryBuilder->setFirstResult($page);
unset($parameters['page']);
}
unset($parameters['limit']);
}
$i = 0;
foreach ($parameters as $key => $value) {
if ($value === "") {
continue;
}
if ($i == 0) {
$queryBuilder->where("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}");
} else {
$queryBuilder->andWhere("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}");
}
$i++;
}
if (!empty($sort)) {
$queryBuilder->orderBy("c." . $sort['key'], $sort['value']);
}
return $queryBuilder;
}
