Creates a new instance of a SQL query builder.
public createQueryBuilder ( ) : Doctrine\DBAL\Query\QueryBuilder | ||
리턴 | Doctrine\DBAL\Query\QueryBuilder |
public function count() { $qb = $this->dbal->createQueryBuilder(); $qb->select('count(*) c')->from('(' . $this->qb->getSQL() . ')', 'xxx'); $row = $this->dbal->fetchAssoc($qb->getSQL()); return $this->count = intval($row['c']); }
/** * Performs an authentication attempt * * @return \Zend\Authentication\Result * @throws \Zend\Authentication\Adapter\Exception\ExceptionInterface If authentication cannot be performed */ public function authenticate() { $qb = $this->db->createQueryBuilder(); if (strpos($this->options['credential_treatment'], '?') === false) { $this->options['credential_treatment'] = '?'; } $expression = '(CASE WHEN ? = ' . $this->options['credential_treatment'] . ' THEN 1 ELSE 0 END) AS ?'; $qb->createPositionalParameter($this->options['credential_column']); $qb->createPositionalParameter($this->credential); $qb->createPositionalParameter('zend_auth_credential_match'); $qb->select(['*', $expression])->from($this->options['table_name'])->where($this->options['identity_column'] . ' = ' . $qb->createPositionalParameter($this->identity)); //TODO check fetch type $resultIdentities = $qb->execute()->fetchAll(); if (count($resultIdentities) < 1) { return new Result(Result::FAILURE_IDENTITY_NOT_FOUND, $this->identity, ['A record with the supplied identity could not be found.']); } else { if (count($resultIdentities) > 1) { return new Result(Result::FAILURE_IDENTITY_AMBIGUOUS, $this->identity, ['More than one record matches the supplied identity.']); } } $authResult = new Result(Result::FAILURE, $this->identity); foreach ($resultIdentities as $resultIdentity) { if ((int) $resultIdentity['zend_auth_credential_match'] !== 1) { $authResult = new Result(Result::FAILURE_CREDENTIAL_INVALID, $this->identity, ['Supplied credential is invalid.']); } else { unset($resultIdentity['zend_auth_credential_match']); $this->resultRow = $resultIdentity; $authResult = new Result(Result::SUCCESS, $this->identity, ['Authentication successful.']); } if ($authResult->isValid()) { break; } } return $authResult; }
/** * Builds the raw query * * @return void */ protected function buildQuery() { $this->queryBuilder = $this->connection->createQueryBuilder(); $this->queryBuilder->select((array) $this->conf->select); /* * Main table */ $this->queryBuilder->from($this->conf->from->name, $this->conf->from->alias); /* * Inner join, right join, left join */ $joinTypes = ['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); } } }
/** * @inheritdoc */ public function getList($products, Struct\ShopContextInterface $context) { $ids = []; foreach ($products as $product) { $ids[] = $product->getId(); } $ids = array_unique($ids); $query = $this->connection->createQueryBuilder(); $query->select(['relation.article_id']); $query->addSelect($this->fieldHelper->getRelatedProductStreamFields()); $query->from('s_product_streams_articles', 'relation'); $query->innerJoin('relation', 's_product_streams', 'stream', 'stream.id = relation.stream_id'); $this->fieldHelper->addProductStreamTranslation($query, $context); $query->where('relation.article_id IN (:ids)')->setParameter(':ids', $ids, Connection::PARAM_INT_ARRAY); /**@var $statement \Doctrine\DBAL\Driver\ResultStatement */ $statement = $query->execute(); $data = $statement->fetchAll(\PDO::FETCH_GROUP); $related = []; foreach ($data as $productId => $data) { $related[$productId] = []; foreach ($data as $row) { $related[$productId][] = $this->hydrator->hydrate($row); } } return $related; }
public function fetchAll() { $stmt = $this->connection->createQueryBuilder()->select('uri, data')->from($this->table)->execute(); foreach ($stmt as $item) { (yield $item['uri'] => $this->prepareRowForRead($item['uri'], $item['data'])); } }
/** * Sync the filesystem scan and the Bundles table * This is a 'dumb' scan - there is no state management here * state management occurs in the module and theme management * and is checked in Bundle/Bootstrap * * @param $array array of extensions * obtained from filesystem scan * key is bundle name and value an instance of \Zikula\Bundle\CoreBundle\Bundle\MetaData */ private function sync($array) { // add what is in array but missing from db /** @var $metadata MetaData */ foreach ($array as $name => $metadata) { $qb = $this->conn->createQueryBuilder(); $qb->select('b.id', 'b.bundlename', 'b.bundleclass', 'b.autoload', 'b.bundletype', 'b.bundlestate')->from('bundles', 'b')->where('b.bundlename = :name')->setParameter('name', $name); $result = $qb->execute(); $row = $result->fetch(); if (!$row) { // bundle doesn't exist $this->insert($metadata); } elseif ($metadata->getClass() != $row['bundleclass'] || serialize($metadata->getAutoload()) != $row['autoload']) { // bundle json has been updated $updatedMeta = array("bundleclass" => $metadata->getClass(), "autoload" => serialize($metadata->getAutoload())); $this->conn->update('bundles', $updatedMeta, array('id' => $row['id'])); } } // remove what is in db but missing from array $qb = $this->conn->createQueryBuilder(); $qb->select('b.id', 'b.bundlename', 'b.bundleclass', 'b.autoload', 'b.bundletype', 'b.bundlestate')->from('bundles', 'b'); $res = $qb->execute(); foreach ($res->fetchAll() as $row) { if (!in_array($row['bundlename'], array_keys($array))) { $this->removeById($row['id']); } } // clear the cache /** @var $cacheClearer \Zikula\Bundle\CoreBundle\CacheClearer */ $cacheClearer = \ServiceUtil::getManager()->get('zikula.cache_clearer'); $cacheClearer->clear('symfony.config'); }
/** * @return string */ private function getSQLForAllRows() { static $sql; if (!$sql) { $sql = $this->connection->createQueryBuilder()->select($this->getQuotedFields())->from('metadatas_structure', 's')->orderBy('sorter')->getSQL(); } return $sql; }
/** * @param $categoryId * @param null|int $limit * @return LastIdQuery */ public function createCategoryQuery($categoryId, $limit = null) { $query = $this->connection->createQueryBuilder()->select(['categories.articleID', 'categories.articleID'])->from('s_articles_categories_ro', 'categories')->andWhere('categories.articleID > :lastId')->andWhere('categories.categoryID = :categoryId')->setParameter(':categoryId', $categoryId, \PDO::PARAM_INT)->setParameter(':lastId', 0, \PDO::PARAM_INT)->orderBy('categories.articleID'); if ($limit !== null) { $query->setMaxResults($limit); } return new LastIdQuery($query); }
/** * Save a snapshot * * @param Snapshot $snapshot * @return void */ public function save(Snapshot $snapshot) { $table = $this->getTable($snapshot->aggregateType()); $this->connection->insert($table, ['aggregate_type' => $snapshot->aggregateType()->toString(), 'aggregate_id' => $snapshot->aggregateId(), 'last_version' => $snapshot->lastVersion(), 'created_at' => $snapshot->createdAt()->format('Y-m-d\\TH:i:s.u'), 'aggregate_root' => serialize($snapshot->aggregateRoot())], ['string', 'string', 'integer', 'string', 'blob']); $queryBuilder = $this->connection->createQueryBuilder(); $table = $this->getTable($snapshot->aggregateType()); $queryBuilder->delete($table)->where('aggregate_type = :aggregate_type')->andWhere('aggregate_id = :aggregate_id')->andWhere('last_version < :last_version')->setParameter('aggregate_type', $snapshot->aggregateType()->toString())->setParameter('aggregate_id', $snapshot->aggregateId())->setParameter('last_version', $snapshot->lastVersion()); $queryBuilder->execute(); }
/** * @param string $version * @return boolean */ public function isApplied($version) { if ($this->isExistTable() == false) { return false; } /* @var $stmt Statement*/ $stmt = $this->doctrine->createQueryBuilder()->select('version')->from(self::TABLE_NAME, 'M')->where('version = :version')->setParameter('version', $version)->execute(); return !!$stmt->fetch(); }
/** * @param MaintenanceEvent $event */ public function onDataCleanup(MaintenanceEvent $event) { $qb = $this->db->createQueryBuilder()->setParameter('date', $event->getDate()->format('Y-m-d H:i:s')); if ($event->isDryRun()) { $rows = $qb->select('count(*) as records')->from(MAUTIC_TABLE_PREFIX . 'leads', 'l')->where($qb->expr()->andX($qb->expr()->lte('l.last_active', ':date'), $qb->expr()->isNull('l.date_identified')))->execute()->fetchColumn(); } else { $rows = $qb->delete(MAUTIC_TABLE_PREFIX . 'leads')->where($qb->expr()->andX($qb->expr()->lte('last_active', ':date'), $qb->expr()->isNull('date_identified')))->execute(); } $event->setStat($this->translator->trans('mautic.maintenance.visitors'), $rows, $qb->getSQL(), $qb->getParameters()); }
/** * {@inheritdoc} */ public function fetchOneById($id) { $qb = $this->db->createQueryBuilder(); $qb->select('*')->from('album', 'a')->where('id = :id')->setParameter('id', $id); $result = $qb->execute()->fetch(); if (!$result) { throw new \Exception('Row with id: ' . $id . ' not found.'); } return $result; }
/** * @param SKU $sku * @return Product * @throws ProductNotFoundException */ public function getBySku(SKU $sku) : Product { $queryBuilder = $this->connection->createQueryBuilder(); $queryBuilder->select('sku', 'is_in_stock', 'price_amount', 'price_currency', 'price_precision')->from('dumplie_inventory_product')->where('sku = :sku')->setParameter('sku', (string) $sku); $result = $this->connection->fetchAssoc($queryBuilder->getSQL(), $queryBuilder->getParameters()); if (!$result) { throw ProductNotFoundException::bySku($sku); } return new Product(new SKU($result['sku']), new Price((int) $result['price_amount'], $result['price_currency'], (int) $result['price_precision']), (bool) $result['is_in_stock']); }
/** * @param int $id * @return Album */ public function fetchOne($id) { $qb = $this->db->createQueryBuilder(); $qb->select('*')->from('album', 'a')->where('id = :id')->setParameter('id', $id); $result = $qb->execute()->fetch(); if (!$result) { throw new \Exception("Could not find row {$id}"); } return new Album($result); }
/** * @param $isDryRun * @param $date * * @return int */ private function cleanupData(MaintenanceEvent $event, $table) { $qb = $this->db->createQueryBuilder()->setParameter('date', $event->getDate()->format('Y-m-d H:i:s')); if ($event->isDryRun()) { $rows = (int) $qb->select('count(*) as records')->from(MAUTIC_TABLE_PREFIX . $table, 'log')->where($qb->expr()->lte('log.date_added', ':date'))->execute()->fetchColumn(); } else { $rows = (int) $qb->delete(MAUTIC_TABLE_PREFIX . $table)->where($qb->expr()->lte('date_added', ':date'))->execute(); } $event->setStat($this->translator->trans('mautic.maintenance.' . $table), $rows, $qb->getSQL(), $qb->getParameters()); }
/** * @param $id * @return DirectoryView * @throws \Doctrine\DBAL\DBALException */ public function getById($id) : DirectoryView { $statement = $this->conn->createQueryBuilder()->select('*')->from($this->tableName)->where('id = :id')->setParameter(':id', $id)->execute(); $statement->setFetchMode(\PDO::FETCH_CLASS, $this->viewClass); $result = $statement->fetch(); if (!$result) { throw new EntityNotFoundException(); } return $result; }
public function testDb1() { $result = self::$db->fetchAssoc('SELECT * FROM "user" LIMIT 1'); $this->assertArrayHasKey('user_id', $result); $q = self::$db->createQueryBuilder(); /** @var \Doctrine\DBAL\Driver\Statement $stmt */ $stmt = $q->select('*')->from('"user"', 't')->setMaxResults(50)->execute(); $result = $stmt->rowCount(); $this->assertEquals(50, $result); }
/** * @param Connection $db Objeto de la conección de doctrine con la base de datos * @param string $table Nombre de la tabla en la base de datos */ public function __construct(Connection $db, $table = "") { if (!is_null($db)) { $this->_db = $db; $this->_queryBuilder = $this->_db->createQueryBuilder(); } if (!is_null($table)) { $this->_table = $table; } }
/** * {@inheritdoc} */ public function load($username) { $qb = $this->connection->createQueryBuilder(); $qb->select('*')->from($this->table)->where($qb->expr()->eq($this->field, '?'))->setParameter(0, $username); $result = $qb->execute(); if ($result->rowCount() == 1) { $user = $result->fetch(\PDO::FETCH_ASSOC); return new DefaultUser($user[$this->id], $user); } return null; }
/** * Find all message handler with given processing_id * @param string $processingId * @return array of message handlers data */ public function findByProcessingId($processingId) { $handlers = []; $builder = $this->connection->createQueryBuilder(); $builder->select('*')->from(Tables::MESSAGE_HANDLER)->where($builder->expr()->eq('processing_id', ':processing_id'))->setParameter('processing_id', $processingId); foreach ($builder->execute() as $row) { $this->fromDatabase($row); array_push($handlers, $row); } return $handlers; }
/** * @param string $id * @return CustomerView */ public function getById(\string $id) : CustomerView { $query = $this->conn->createQueryBuilder()->select('t.id, t.username, t.country, t.city, t.street, t.zip_code as zipCode')->from('users', 't')->where('t.id = :id')->setParameter(':id', $id, \PDO::PARAM_STR); $statement = $query->execute(); $statement->setFetchMode(\PDO::FETCH_CLASS, CustomerView::class); $result = $statement->fetch(); if (!$result) { throw new EntityNotFoundException(); } return $result; }
/** * @param \DateTime $date * * @return mixed */ public function queryDataForPoolChartUntilDate(\DateTime $date) { $query = $this->dbal->createQueryBuilder()->select('count(p.id) AS totalPoolCount, p.sentdate AS month')->from('Pool', 'p')->where('p.sentdate < :lastDay')->groupBy('year(p.sentdate), month(p.sentdate)')->setParameter('lastDay', $date->format('Y-m-d H:i:s')); $totalPoolChartData = $query->execute()->fetchAll(); $accumulatedPoolCounter = 0; foreach ($totalPoolChartData as &$poolCount) { $accumulatedPoolCounter += $poolCount['totalPoolCount']; $poolCount['totalPoolCount'] = $accumulatedPoolCounter; } return $totalPoolChartData; }
/** * @dataProvider dataStatesQuery */ public function testListenerModifiesQueryAccordingly($states, $expected) { $query = $this->connection->createQueryBuilder(); $query->select('a.*')->from('articles', 'a'); $this->assertEquals('SELECT a.* FROM articles a', $query->getSQL()); $event = new QueryDatabaseEvent(Article::class, $query); $listener = new QueryDatabaseListener(); $listener->allowStates($states); $listener->addStateConstraint($event); $this->assertEquals($expected, $query->getSQL()); }
/** * {@inheritdoc} */ public function read($lastId, $limit) { $query = $this->connection->createQueryBuilder(); $query->select(['id', 'event', 'payload', 'time'])->from('s_es_backlog', 'backlog')->andWhere('backlog.id > :lastId')->orderBy('backlog.id', 'ASC')->setParameter(':lastId', $lastId)->setMaxResults($limit); $data = $query->execute()->fetchAll(\PDO::FETCH_ASSOC); $result = []; foreach ($data as $row) { $backlog = new Backlog($row['event'], json_decode($row['payload'], true), $row['time'], (int) $row['id']); $result[] = $backlog; } return $result; }
/** * {@inheritdoc} */ public function getBySession(SessionEntity $session) { $query = $this->db->createQueryBuilder()->select('c.id', 'c.name')->from('oauth_clients', 'c')->leftJoin('c', 'oauth_sessions', 's', 'c.id = s.client_id')->where('s.id = :sessionId'); $query->createNamedParameter($session->getId(), \PDO::PARAM_STR, ':sessionId'); $stmt = $query->execute(); $result = $stmt->fetchAll(); if (count($result) === 1) { $client = new ClientEntity($this->server); $client->hydrate(['id' => $result[0]['id'], 'name' => $result[0]['name']]); return $client; } }
private function cleanData(MaintenanceEvent $event, $table) { $qb = $this->db->createQueryBuilder()->setParameter('date', $event->getDate()->format('Y-m-d H:i:s')); if ($event->isDryRun()) { $rows = $qb->select('count(*) as records')->from(MAUTIC_TABLE_PREFIX . $table, 'h')->join('h', MAUTIC_TABLE_PREFIX . 'leads', 'l', 'h.lead_id = l.id')->where($qb->expr()->andX($qb->expr()->lte('l.last_active', ':date'), $qb->expr()->isNull('l.date_identified')))->execute()->fetchColumn(); } else { $subQb = $this->db->createQueryBuilder(); $subQb->select('id')->from(MAUTIC_TABLE_PREFIX . 'leads', 'l')->where($qb->expr()->andX($qb->expr()->lte('l.last_active', ':date'), $qb->expr()->isNull('l.date_identified'))); $rows = $qb->delete(MAUTIC_TABLE_PREFIX . $table)->where($qb->expr()->in('lead_id', $subQb->getSQL()))->execute(); } $event->setStat($this->translator->trans('mautic.maintenance.' . $table), $rows, $qb->getSQL(), $qb->getParameters()); }
public function create($values) { // Prepare insert query $query = $this->db->createQueryBuilder()->insert('users'); // Append parameters to insert to the query foreach ($values as $key => $value) { $query = $query->setValue($key, ':' . $key)->setParameter(':' . $key, $value); } $query->execute(); // @TODO: Check if this works in MariaDB return $this->db->lastInsertId('users_id_seq'); }
/** * {@inheritdoc} */ public function getDataSource(array $configuration, Parameters $parameters) { if (!array_key_exists('table', $configuration)) { throw new \InvalidArgumentException('"table" must be configured.'); } $queryBuilder = $this->connection->createQueryBuilder(); $queryBuilder->select('o.*')->from($configuration['table'], 'o'); foreach ($configuration['aliases'] as $column => $alias) { $queryBuilder->addSelect(sprintf('o.%s as %s', $column, $alias)); } return new DataSource($queryBuilder); }
/** * Get the aggregate root if it exists otherwise null * * @param AggregateType $aggregateType * @param string $aggregateId * @return Snapshot */ public function get(AggregateType $aggregateType, $aggregateId) { $queryBuilder = $this->connection->createQueryBuilder(); $table = $this->getTable($aggregateType); $queryBuilder->select('*')->from($table, $table)->where('aggregate_type = :aggregate_type')->andWhere('aggregate_id = :aggregate_id')->orderBy('last_version', 'DESC')->setParameter('aggregate_type', $aggregateType->toString())->setParameter('aggregate_id', $aggregateId)->setMaxResults(1); $stmt = $queryBuilder->execute(); $result = $stmt->fetch(\PDO::FETCH_ASSOC); if (!$result) { return; } return new Snapshot($aggregateType, $aggregateId, unserialize($result['aggregate_root']), (int) $result['last_version'], \DateTimeImmutable::createFromFormat('Y-m-d\\TH:i:s.u', $result['created_at'], new \DateTimeZone('UTC'))); }
/** * {@inheritdoc} */ public function attempt($username, $password) { // Fetch the user $user = $this->db->createQueryBuilder()->select('user.id, user.password')->from('users', 'user')->andWhere('user.username = :username')->setParameter('username', $username)->execute()->fetch(); if ($user === false) { return false; } // Check if the password matches if (!$this->passwordManager->check($password, $user['password'])) { return false; } return $user['id']; }