/** * {@inheritdoc} */ public function searchDestinationUser(CallEventInterface $ce) { /** @var Call $call */ $call = $ce->getCall(); $qb = $this->em->createQueryBuilder(); $query = $qb->select('u')->from('UserBundle:User', 'u')->leftJoin('u.groups', 'g')->leftJoin('u.contacts', 'c')->where('g.account = :account')->setParameter('account', $call->getAccount())->andWhere('c.sip LIKE :sip')->setParameter('sip', '%' . $ce->getDstNumber() . '%')->setMaxResults(1)->getQuery(); return $query->getOneOrNullResult(); }
/** * Cleanup old sessions * @link http://php.net/manual/en/sessionhandlerinterface.gc.php * @param int $maxLifetime <p> * Sessions that have not updated for * the last maxLifetime seconds will be removed. * </p> * @return bool <p> * The return value (usually TRUE on success, FALSE on failure). * Note this value is returned internally to PHP for processing. * </p> * @since 5.4.0 */ public function gc($maxLifetime) { $qb = $this->entityManager->createQueryBuilder(); $qb->delete(get_class($this->sessionDataClass), 's'); $qb->where($qb->expr()->lt('s.lastHit', time() - $maxLifetime)); $qb->getQuery()->execute(); return true; }
public function findAllWithCategories() { # Doctrine dokumentace: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods $queryBuilder = $this->entityManager->createQueryBuilder(); $products = $queryBuilder->select('p.id, p.name, pc.name as categoryName')->from(Product::class, 'p')->join('p.categories', 'pc')->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY); // šetří výkon dump($products); die; }
public function __invoke(User $user) { $qb = $this->em->createQueryBuilder(); $qb2 = $this->em->createQueryBuilder(); $ids = $qb2->select('IDENTITY(u.group)')->from(User::class, 'u')->where('u.login = :login')->setParameter('login', $user->getLogin())->getQuery()->getArrayResult(); return $qb->select('ug')->from(UserGroup::class, 'ug')->where($qb->expr()->in('ug.id', array_map(function ($el) { return current($el); }, $ids)))->orderBy('ug.name')->getQuery()->getResult(); }
public function __construct(EntityManagerInterface $entityManager, $entityName = null, Query $query = null) { $this->entityManager = $entityManager; $this->entityName = $entityName; if (is_null($query) && is_string($entityName) && class_exists($entityName)) { $query = $this->entityManager->createQueryBuilder()->select('o')->from($this->entityName, 'o')->getQuery(); } $this->query = $query; }
/** * @param FormBuilderInterface $builder * @param array $options */ public function buildForm(FormBuilderInterface $builder, array $options) { $types = [Notice::DEFAULT_TYPE]; // add user-defined types $result = $this->em->createQueryBuilder()->select('n.type')->from('AnimeDbAppBundle:Notice', 'n')->where('n.type IS NOT NULL')->groupBy('n.type')->getQuery()->getResult(); foreach ($result as $row) { $types[] = $row['type']; } $builder->add('status', 'choice', ['choices' => [Notice::STATUS_CREATED => 'New', Notice::STATUS_SHOWN => 'Shown', Notice::STATUS_CLOSED => 'Closed'], 'required' => false])->add('type', 'choice', ['choices' => $this->getNormalLabels($types), 'required' => false])->setMethod('GET'); }
/** * Method used to count leads for a specified day * @param $forms Array of forms * @param $minDate DateTime object * @return mixed */ protected function getLeadsCountForForms($forms, $minDate) { foreach ($forms as $form) { $formIds[] = $form->getId(); } // formated date time $formatedMinDate = $minDate->format('Y-m-d'); // Count leads for the specified day $querybuilder = $this->entity_manager->createQueryBuilder(); $querybuilder->select('count(l)')->from('TellawLeadsFactoryBundle:Leads', 'l')->where('l.form IN (:formIds)')->andWhere('l.createdAt BETWEEN :minDate AND :maxDate')->setParameter('formIds', $formIds)->setParameter('minDate', $formatedMinDate . " 00:00:00")->setParameter('maxDate', $formatedMinDate . " 23:59:59"); $querybuilder = $this->excludeInternalLeads($querybuilder); return $querybuilder->getQuery()->getSingleScalarResult(); }
/** * {@inheritdoc} * * @return FamilyInterface[] */ public function findBySearch($search = null, array $options = []) { $qb = $this->entityManager->createQueryBuilder()->select('f')->from($this->entityName, 'f'); if (null !== $search && '' !== $search) { $qb->where('f.code like :search')->setParameter('search', '%' . $search . '%'); if (isset($options['locale'])) { $qb->leftJoin('f.translations', 'ft'); $qb->orWhere('ft.label like :search AND ft.locale = :locale'); $qb->setParameter('search', '%' . $search . '%'); $qb->setParameter('locale', $options['locale']); } } $qb = $this->applyQueryOptions($qb, $options); return $qb->getQuery()->getResult(); }
/** * Count the number of objects in a collection with the given values. * * @param string $collection * @param string $column * @param array $values * @param array $extra * * @return int */ public function getMultiCount($collection, $column, array $values, array $extra = []) { $builder = $this->em->createQueryBuilder(); $builder->select('count(e)')->from($collection, 'e'); $builder->where($builder->expr()->in(":{$column}", ":{$column}")); foreach ($extra as $key => $extraValue) { $builder->andWhere("e.{$key} = :{$key}"); } $query = $builder->getQuery(); $query->setParameter($column, $values); foreach ($extra as $key => $extraValue) { $query->setParameter($key, $extraValue); } return $query->presence(); }
/** * @param string $search * @param array $options * * @return QueryBuilder */ protected function findBySearchQb($search, array $options) { //TODO: refactor on master because this is exactly the same that FamilySearchableRepository //TODO: and should be put in Akeneo\Bundle\StorageUtilsBundle\Doctrine\ORM\Repository\SearchableRepository $qb = $this->entityManager->createQueryBuilder()->select('a')->from($this->entityName, 'a'); $options = $this->resolveOptions($options); if (null !== $search) { $qb->leftJoin('a.translations', 'at'); $qb->where('a.code like :search')->setParameter('search', '%' . $search . '%'); if (null !== ($localeCode = $options['locale'])) { $qb->orWhere('at.label like :search AND at.locale like :locale'); $qb->setParameter('search', '%' . $search . '%'); $qb->setParameter('locale', $localeCode); } } if (!empty($options['identifiers'])) { $qb->andWhere('a.code in (:codes)'); $qb->setParameter('codes', $options['identifiers']); } if (!empty($options['excluded_identifiers'])) { $qb->andWhere('a.code not in (:codes)'); $qb->setParameter('codes', $options['excluded_identifiers']); } if (null !== $options['limit']) { $qb->setMaxResults($options['limit']); if (null !== $options['page']) { $qb->setFirstResult($options['limit'] * ($options['page'] - 1)); } } //TODO: this part is specific to attributes if ($options['exclude_unique']) { $qb->andWhere('a.unique = 0'); } if (null !== $options['types']) { $qb->andWhere('a.attributeType in (:types)'); $qb->setParameter('types', $options['types']); } $qb->leftJoin('a.group', 'ag'); if (!empty($options['attribute_groups'])) { $qb->andWhere('ag.code in (:groups)'); $qb->setParameter('groups', $options['attribute_groups']); } $qb->orderBy('ag.code'); $qb->orderBy('ag.sortOrder'); $qb->groupBy('a.id'); return $qb; }
public function search() { //$finder = $this->container->get('fos_elastica.finder.search.articles'); $bool = new Query\Bool(); $multiMatch = new Query\MultiMatch(); $multiMatch->setFields(['subjects', 'title', 'keywords', 'subtitle', 'citations.raw', 'journal.title', 'journal.subtitle']); $multiMatch->setType('phrase_prefix'); $multiMatch->setQuery($this->getParam('term')); $bool->addMust($multiMatch); if ($this->filter) { foreach ($this->filter as $key => $filter) { $filterObj = new Query\Match(); $this->applyFilter($filterObj, $key, $filter); $bool->addMust($filterObj); } } $missing = new Filter\Missing("issue"); $not = new Filter\BoolNot($missing); $notQ = new Query\Filtered(); $notQ->setFilter($not); $bool->addMust($notQ); $query = new Query(); $query->setQuery($bool); $query->setFrom($this->getPage() * $this->getLimit()); $query->setSize($this->getLimit()); $aggregation = new Terms('journals'); $aggregation->setField('journal.id'); $aggregation->setOrder('_count', 'desc'); $qb = $this->em->createQueryBuilder(); $qb->select('count(r.id)')->from('OjsJournalBundle:Journal', 'r')->where($qb->expr()->eq('r.status', 3)); $aggregation->setSize($qb->getQuery()->getSingleScalarResult()); $query->addAggregation($aggregation); $aggregation = new Terms('authors'); $aggregation->setField('articleAuthors.author.id'); $aggregation->setOrder('_count', 'desc'); $qb = $this->em->createQueryBuilder(); $qb->select('count(r.id)')->from('OjsJournalBundle:Author', 'r'); $aggregation->setSize($qb->getQuery()->getSingleScalarResult()); $query->addAggregation($aggregation); $elasticaAdapter = new ElasticaAdapter($this->index, $query); $pagerFanta = new Pagerfanta($elasticaAdapter); $pagerFanta->setMaxPerPage($this->getLimit()); $pagerFanta->setCurrentPage($this->getPage()); /** @var ResultSet $search */ $search = $pagerFanta->getCurrentPageResults(); $result = $search->getResults(); //$search->getResults(); $this->pager = $pagerFanta; $transformer = new ElasticaToModelTransformer($this->registry, 'OjsJournalBundle:Article'); $transformer->setPropertyAccessor($this->propertyAccessor); $this->result = $transformer->transform($result); $this->setCount($pagerFanta->getNbResults()); $this->addAggregation('journal', $this->transform($search->getAggregation('journals')['buckets'], 'OjsJournalBundle:Journal')); $this->addAggregation('author', $this->transform($search->getAggregation('authors')['buckets'], 'OjsJournalBundle:Author')); return $this; }
/** * @param string $class * @param mixed $fit * @return \Doctrine\ORM\QueryBuilder */ public function build($class, $fit = null) { $alias = uniqid("A"); /** @var \Doctrine\Orm\QueryBuilder $qb */ $qb = $this->em->createQueryBuilder(); if ($fit instanceof SelectFitInterface && $fit->getSelect($alias)) { $qb->select($fit->getSelect($alias)); } else { $qb->select($alias); } $qb->from($class, $alias); if ($fit instanceof WhereWithJoinFitInterface) { $join = $fit->getJoin($alias); if ($join) { $qb->join($alias, $join->getJoin(), $join->getAlias(), $join->getCondition()); } $where = $fit->getWhere($alias); if ($where) { $qb->andWhere($where); } } if ($fit instanceof WhereFitInterface) { $where = $fit->getWhere($alias); if ($where) { $qb->andWhere($where); $qb->setParameters($fit->getParameters()); } } if ($fit instanceof OrderFitInterface) { $order = $fit->getOrder($alias); if ($order) { $qb->orderBy($order); } } if ($fit instanceof LimitFitInterface) { $limit = $fit->getLimit(); if ($limit) { $qb->setMaxResults($limit); } } return $qb; }
/** * @param array $selectedIds * @param string $value * * @return int */ protected function finishBatch(array &$selectedIds, $value) { $qBuilder = $this->entityManager->createQueryBuilder(); $entitiesCount = $qBuilder->update($this->entityName, 'e')->set('e.' . $this->fieldName, ':value')->where($qBuilder->expr()->in('e.' . $this->identifierName, $selectedIds))->getQuery()->setParameter('value', $value)->execute(); $this->entityManager->flush(); if ($this->entityManager->getConnection()->getTransactionNestingLevel() == 1) { $this->entityManager->clear(); } $selectedIds = []; return $entitiesCount; }
/** * @param string $entityName * @param int $userId * * @return \DateTime|null */ protected function getMaxDateFromEntityByUserId($entityName, $userId) { $max = null; $query = $this->entityManager->createQueryBuilder(); $query->select('MAX(e.date) AS max_date')->from($entityName, 'e')->where('e.user = :user_id')->setParameter('user_id', $userId)->setMaxResults(1); $result = $query->getQuery()->getResult(); if ($result && isset($result[0]['max_date'])) { $max = DateUtil::dateTimeFromMySqlFormat($result[0]['max_date']); } return $max; }
/** * {@inheritdoc} * * @return FamilyInterface[] */ public function findBySearch($search = null, array $options = []) { $qb = $this->entityManager->createQueryBuilder()->select('f')->from($this->entityName, 'f'); $qb->leftJoin('f.translations', 'ft'); if (null !== $search && '' !== $search) { $qb->where('f.code like :search')->setParameter('search', "%{$search}%"); $qb->orWhere('ft.label like :search')->setParameter('search', "%{$search}%"); } if (isset($options['identifiers']) && is_array($options['identifiers']) && !empty($options['identifiers'])) { $qb->andWhere('f.code in (:codes)'); $qb->setParameter('codes', $options['identifiers']); } if (isset($options['limit'])) { $qb->setMaxResults((int) $options['limit']); if (isset($options['page'])) { $qb->setFirstResult((int) $options['limit'] * ((int) $options['page'] - 1)); } } return $qb->getQuery()->getResult(); }
/** * Build iterator * * NOTE : The submission fields are added as export fields as well ... */ public function buildIterator() { $qb = $this->em->createQueryBuilder(); $qb->select('fs')->from('KunstmaanFormBundle:FormSubmission', 'fs')->innerJoin('fs.node', 'n', 'WITH', 'fs.node = n.id')->andWhere('n.id = :node')->andWhere('fs.lang = :lang')->setParameter('node', $this->nodeTranslation->getNode()->getId())->setParameter('lang', $this->nodeTranslation->getLang())->addOrderBy('fs.created', 'DESC'); $iterableResult = $qb->getQuery()->iterate(); $isHeaderWritten = false; $collection = new ArrayCollection(); foreach ($iterableResult as $row) { /* @var FormSubmission $submission */ $submission = $row[0]; // Write row data $data = array('id' => $submission->getId(), 'date' => $submission->getCreated()->format('d/m/Y H:i:s'), 'language' => $submission->getLang()); foreach ($submission->getFields() as $field) { $header = $this->translator->trans($field->getLabel()); if (!$isHeaderWritten) { $this->addExportField($header, $header); } $data[$header] = $field->__toString(); } $isHeaderWritten = true; $collection->add(array($data)); } $this->iterator = $collection->getIterator(); }
public function __invoke() { $qb = $this->em->createQueryBuilder(); return $qb->select('t.title', 'COUNT(p.id) as countOfPosts')->from(Tag::class, 't')->leftJoin('t.posts', 'p')->groupBy('t.id')->orderBy('t.title')->getQuery()->getResult(); }
public function __invoke() { $qb = $this->em->createQueryBuilder(); return $qb->select('p', 't')->from(Post::class, 'p')->leftJoin('p.tags', 't')->orderBy('p.publishedAt')->getQuery()->getResult(); }
/** * {@inheritdoc} */ public function createQueryBuilder() { return $this->wrapped->createQueryBuilder(); }
/** * Get a query builder for the migration table. * @return QueryBuilder */ protected function query() { return $this->em->createQueryBuilder()->select('o')->from(Migration::class, 'o'); }
/** * Queries for existing users. * * @param string[] $suggestions * * @return string[] */ private function queryExistingUsersBySuggestedNames(array $suggestions) { $qb = $this->entityManager->createQueryBuilder(); $qb->select('user.username')->from('Account:User', 'user')->where($qb->expr()->in('user.username', ':nameList'))->setParameter(':nameList', $suggestions); return array_column($qb->getQuery()->getResult(), 'username'); }
public function __invoke() { $qb = $this->em->createQueryBuilder(); return $qb->select('ug.name', 'COUNT(u.id) as countOfUsers')->from(UserGroup::class, 'ug')->leftJoin('ug.users', 'u')->groupBy('ug.id')->orderBy('ug.name')->getQuery()->getResult(); }
/** * @param int $limit * @return array */ public function getLastUsers($limit = 5) { $qb = $this->em->createQueryBuilder(); $qb->select('u')->from('NFQUserBundle:User', 'u')->orderBy('u.id', 'DESC')->setMaxResults($limit); return $qb->getQuery()->getArrayResult(); }
/** * @return array */ public function getList() { $qb = $this->em->createQueryBuilder(); $qb->select('e')->from($this->options->getEntityClass(), 'e')->where('e.deleted = 0')->orderBy('e.id', 'ASC'); return $qb->getQuery()->getResult(); }
/** * {@inheritdoc} */ public function find($identifier) { $queryBuilder = $this->entityManager->createQueryBuilder(); $queryBuilder->select('s')->from('AppMainBundle:Submission', 's')->where('s.identifier = :identifier')->setParameter('identifier', $identifier); return $queryBuilder->getQuery()->getOneOrNullResult(); }
/** * Returns an array with all instances of a recurring event. If an event was detached it's not part of the series' instances. * * todo: Refactor - combining eluceo/ical and sabre/vobject this way seems very expensive. * @param Event $event * @param \DateTime|null $dateFrom * @param \DateTime|null $dateTo * * @return Event[] * * @throws \Exception */ public function getInstances(Event $event, \DateTime $dateFrom = null, \DateTime $dateTo = null, $findOnlyOne = false) { $eventInstances = array(); // this is necessary for using nthOccurrence, otherwise the maximum recurrence entities will result in an error // todo: Refactor - solve recurrence count for nthOccurrence VObject\Settings::$maxRecurrences = -1; if (!$event->getRecurrenceRule()) { return array($event); } $dateFrom = $dateFrom ? $dateFrom : $event->getDtStart(); if (!$dateFrom) { return $eventInstances; } if (!$dateTo) { //default to one year if no end is set $dateTo = clone $event->getDtStart(); $dateTo->add(new \DateInterval('P10Y')); } if (!$dateFrom || !$dateTo) { throw new \Exception('Trying to get instances of a recurring event without dateFrom and/or dateTo being set.'); } //create the calendar $vCalendar = new Calendar($event->getUniqueId()); if (self::isValidDateTime($event->getDtStart()) && self::isValidDateTime($event->getDtEnd())) { $vCalendar->addComponent($event); } //get edited events: depending on $includeEditedEvents to mark these events as deleted or to replace them by their edited event $editedEventsByTimestamp = array(); $qb = $this->em->createQueryBuilder(); $qb->select('e')->from('Xima\\ICalBundle\\Entity\\Component\\Event', 'e')->where('e.uniqueId = :uniqueId')->andWhere($qb->expr()->isNotNull('e.recurrenceId'))->setParameter('uniqueId', $event->getUniqueId()); $editedEvents = $qb->getQuery()->getResult(); foreach ($editedEvents as $editedEvent) { /* @var $editedEvent \Xima\ICalBundle\Entity\Component\Event */ if (self::isValidDateTime($editedEvent->getDtStart()) && self::isValidDateTime($editedEvent->getDtEnd()) && self::isValidDateTime($editedEvent->getRecurrenceId()->getDatetime())) { $editedEventsByTimestamp[$editedEvent->getDtStart()->getTimestamp()] = $editedEvent; $vCalendar->addComponent($editedEvent); } } //render the calendar and parse it to get all recurrences of the event $vCalendarExpandedData = $vCalendar->render(); $vCalendarExpanded = VObject\Reader::read($vCalendarExpandedData); /* @var $vCalendarExpanded \Sabre\VObject\Component\VCalendar */ $vCalendarExpanded = $vCalendarExpanded->expand($dateFrom, $dateTo); foreach ($vCalendarExpanded->getComponents() as $instanceComp) { /* @var $instanceComp \Sabre\VObject\Component\VEvent */ // It's basically the same event, but with the new calculated dates and times... // @todo: refactor so that dtStart is set when dateFrom gets set and so on... $dtStart = new \DateTime(); $dtStart->setTimestamp($instanceComp->DTSTART->getDateTime()->getTimestamp()); $dtEnd = new \DateTime(); $dtEnd->setTimestamp($instanceComp->DTEND->getDateTime()->getTimestamp()); $eventInstance = clone $event; /* @var $eventInstance Event */ $eventInstance->setDtStart($dtStart); $eventInstance->setDateFrom($dtStart); $eventInstance->setTimeFrom($dtStart); $eventInstance->setDtEnd($dtEnd); $eventInstance->setDateTo($dtEnd); $eventInstance->setTimeTo($dtEnd); if ($findOnlyOne) { return array($eventInstance); } if (isset($editedEventsByTimestamp[$instanceComp->DTSTART->getDateTime()->getTimestamp()])) { // if the instance was detached, it's not part of the series' instances continue; } else { $eventInstances[] = $eventInstance; } } return $eventInstances; }
public function __invoke(Tag $tag) { $qb = $this->em->createQueryBuilder(); return $qb->select('p', 't')->from(Post::class, 'p')->leftJoin('p.tags', 't')->where(':tag MEMBER OF p.tags')->setParameter('tag', $tag)->orderBy('p.publishedAt')->getQuery()->getResult(); }
/** * @return \Doctrine\ORM\QueryBuilder */ protected function makeDelete() { return $this->em->createQueryBuilder()->delete(PasswordReminder::class, 'o'); }
/** * {@inheritdoc} */ public function createQueryBuilder($alias, $indexBy = null) { return $this->entityManager->createQueryBuilder()->select($alias)->from($this->mediaEntityName, $alias, $indexBy); }
/** * Get all Entities * * @param Includes $includes * @param Sorting $sort * @param Filters $filters * @return array */ public function all(Includes $includes, Sorting $sort, Filters $filters) { $qb = $this->em->createQueryBuilder(); $qb->select('e')->from($this->class, 'e'); $qb = $this->processSorting($qb, $sort); $qb = $this->processIncludes($qb, $includes); if (isset($this->filters)) { $qb = $this->filters->process($qb, $filters); } return new ArrayCollection($qb->getQuery()->getResult()); }