Creates a new instance of a SQL query builder.
| public createQueryBuilder ( ) : Doctrine\DBAL\Query\QueryBuilder | ||
| return | 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'];
}
