CodeExplorer
You are a developer and looking for Shopware projects?
Apply Now!
fetchCol example
$customerGroups))
OR ($recipientGroups)
OR ($customerStreams)
)
GROUP BY sc.email
";
$sql = Shopware()->Db()->limit($sql, $limit);
return Shopware()->Db()->fetchCol($sql, [$id, $mailing['languageID']]);
}
/** * @deprecated in 5.6, will be private in 5.8 * * Returns a new voucher code using the voucher id. * * @param int $voucherID * * @return string|false */
$sql = Shopware()->Db()->limit($sql, $limit);
return Shopware()->Db()->fetchCol($sql, [$id, $mailing['languageID']]);
}
/** * @deprecated in 5.6, will be private in 5.8 * * Returns a new voucher code using the voucher id. * * @param int $voucherID * * @return string|false */
protected function findNoLongerExistingLinks(array $definitions) {
if ($definitions) {
$query = $this->connection->select($this->table, NULL, $this->options);
$query->addField($this->table, 'id');
$query->condition('discovered', 1);
$query->condition('id', array_keys($definitions), 'NOT IN');
// Starting from links with the greatest depth will minimize the amount // of re-parenting done by the menu storage. $query->orderBy('depth', 'DESC');
$result = $query->execute()->fetchCol();
}
else {
$result = [];
}
return $result;
}
/** * Purge menu links from the database. * * @param array $ids * A list of menu link IDs to be purged. */
usleep(20000);
// Verify that we are back to the original connection count. $this->assertNoConnection($this->id);
}
/** * Tests Database::closeConnection() with a query and custom prefetch method. */
public function testOpenQueryPrefetchClose(): void {
// Execute a query. Database::getConnection(static::TEST_TARGET_CONNECTION)->query($this->getQuery()['show_tables'])->fetchCol();
// Close the connection. Database::closeConnection(static::TEST_TARGET_CONNECTION);
// Wait 20ms to give the database engine sufficient time to react. usleep(20000);
// Verify that we are back to the original connection count. $this->assertNoConnection($this->id);
}
/** * Tests Database::closeConnection() with a select query. */
// Verify that we are back to the original connection count. $this->assertNoConnection($this->id);
}
/** * Tests Database::closeConnection() with a query and custom prefetch method. */
public function testOpenQueryPrefetchClose(): void {
// Execute a query. Database::getConnection(static::TEST_TARGET_CONNECTION)->query($this->getQuery()['show_tables'])->fetchCol();
// Close the connection. Database::closeConnection(static::TEST_TARGET_CONNECTION);
// Wait 20ms to give the database engine sufficient time to react. usleep(20000);
// Verify that we are back to the original connection count. $this->assertNoConnection($this->id);
}
/** * Tests Database::closeConnection() with a select query. */
->condition('uid', 0, '>')
->execute()
->fetchAll();
foreach ($users as $source) {
// Get roles directly from the source. $rids = Database::getConnection('default', 'migrate')
->select('users_roles', 'ur')
->fields('ur', ['rid'])
->condition('ur.uid', $source->uid)
->execute()
->fetchCol();
$roles = [RoleInterface::AUTHENTICATED_ID];
$id_map = $this->getMigration('d6_user_role')->getIdMap();
foreach ($rids as $rid) {
$role = $id_map->lookupDestinationIds([$rid])[0];
$roles[] = reset($role);
}
/** @var \Drupal\user\UserInterface $user */
$user = User::load($source->uid);
$this->assertSame($source->uid, $user->id());
$this->assertSame($source->name, $user->label());
->execute()
->fetchAll();
foreach ($users as $source) {
// Get roles directly from the source. $rids = Database::getConnection('default', 'migrate')
->select('users_roles', 'ur')
->fields('ur', ['rid'])
->condition('ur.uid', $source->uid)
->execute()
->fetchCol();
$roles = [RoleInterface::AUTHENTICATED_ID];
$id_map = $this->getMigration('d6_user_role')->getIdMap();
foreach ($rids as $rid) {
$role = $id_map->lookupDestinationIds([$rid])[0];
$roles[] = reset($role);
}
/** @var \Drupal\user\UserInterface $user */
$user = User::load($source->uid);
$this->assertSame($source->uid, $user->id());
$this->assertSame($source->name, $user->label());
$query
->fields('t', ['age'])
->orderBy('age')
->limit(5);
$outer_query = $connection->select($query);
$outer_query->addField('subquery', 'age');
$outer_query->orderBy('age');
$ages = $outer_query
->execute()
->fetchCol();
$this->assertEquals([25, 26, 27, 28], $ages, 'Inner pager query returned the correct ages.');
}
/** * Confirms that a paging query results with a having expression are valid. * * This is a regression test for #467984. */
public function testHavingPagerQuery() {
$query = Database::getConnection()->select('test', 't')
->extend(PagerSelectExtender::class);
->fields('t', ['age'])
->orderBy('age')
->limit(5);
$outer_query = $connection->select($query);
$outer_query->addField('subquery', 'age');
$outer_query->orderBy('age');
$ages = $outer_query
->execute()
->fetchCol();
$this->assertEquals([25, 26, 27, 28], $ages, 'Inner pager query returned the correct ages.');
}
/** * Confirms that a paging query results with a having expression are valid. * * This is a regression test for #467984. */
public function testHavingPagerQuery() {
$query = Database::getConnection()->select('test', 't')
->extend(PagerSelectExtender::class);
$query_result = $query->execute()->fetchAll(\PDO::FETCH_COLUMN);
$expected_result = ['George', 'John', 'Paul', 'Ringo'];
$this->assertEquals($expected_result, $query_result, 'Returned the correct result.');
}
/** * Confirms that we can fetch an entire column of a result set at once. */
public function testQueryFetchCol() {
$result = $this->connection->query('SELECT [name] FROM {test} WHERE [age] > :age', [':age' => 25]);
$column = $result->fetchCol();
$this->assertCount(3, $column, 'fetchCol() returns the right number of records.');
$result = $this->connection->query('SELECT [name] FROM {test} WHERE [age] > :age', [':age' => 25]);
$i = 0;
foreach ($result as $record) {
$this->assertSame($column[$i++], $record->name, 'Column matches direct access.');
}
}
/** * Tests ::fetchAllAssoc(). */
$expected_result = ['George', 'John', 'Paul', 'Ringo'];
$this->assertEquals($expected_result, $query_result, 'Returned the correct result.');
}
/** * Confirms that we can fetch an entire column of a result set at once. */
public function testQueryFetchCol() {
$result = $this->connection->query('SELECT [name] FROM {test} WHERE [age] > :age', [':age' => 25]);
$column = $result->fetchCol();
$this->assertCount(3, $column, 'fetchCol() returns the right number of records.');
$result = $this->connection->query('SELECT [name] FROM {test} WHERE [age] > :age', [':age' => 25]);
$i = 0;
foreach ($result as $record) {
$this->assertSame($column[$i++], $record->name, 'Column matches direct access.');
}
}
/** * Tests ::fetchAllAssoc(). */
/**
* Returns a list of the tables in the database.
*
* @return array
*/
public function listTables()
{
$sql = "SELECT name FROM sqlite_master WHERE type='table' "
. "UNION ALL SELECT name FROM sqlite_temp_master "
. "WHERE type='table' ORDER BY name";
return $this->fetchCol($sql);
}
/** * Returns the column descriptions for a table. * * The return value is an associative array keyed by the column name, * as returned by the RDBMS. * * The value of each array element is an associative array * with the following keys: * * SCHEMA_NAME => string; name of database or schema * TABLE_NAME => string; * COLUMN_NAME => string; column name * COLUMN_POSITION => number; ordinal position of column in table * DATA_TYPE => string; SQL datatype name of column * DEFAULT => string; default expression of column, null if none * NULLABLE => boolean; true if column can have nulls * LENGTH => number; length of CHAR/VARCHAR * SCALE => number; scale of NUMERIC/DECIMAL * PRECISION => number; precision of NUMERIC/DECIMAL * UNSIGNED => boolean; unsigned property of an integer type * PRIMARY => boolean; true if column is part of the primary key * PRIMARY_POSITION => integer; position of column in primary key * IDENTITY => integer; true if column is auto-generated with unique values * * @param string $tableName * @param string $schemaName OPTIONAL * @return array */
public function listTables()
{
$sql = "SELECT name FROM sqlite_master WHERE type='table' "
. "UNION ALL SELECT name FROM sqlite_temp_master "
. "WHERE type='table' ORDER BY name";
return $this->fetchCol($sql);
}
/** * Returns the column descriptions for a table. * * The return value is an associative array keyed by the column name, * as returned by the RDBMS. * * The value of each array element is an associative array * with the following keys: * * SCHEMA_NAME => string; name of database or schema * TABLE_NAME => string; * COLUMN_NAME => string; column name * COLUMN_POSITION => number; ordinal position of column in table * DATA_TYPE => string; SQL datatype name of column * DEFAULT => string; default expression of column, null if none * NULLABLE => boolean; true if column can have nulls * LENGTH => number; length of CHAR/VARCHAR * SCALE => number; scale of NUMERIC/DECIMAL * PRECISION => number; precision of NUMERIC/DECIMAL * UNSIGNED => boolean; unsigned property of an integer type * PRIMARY => boolean; true if column is part of the primary key * PRIMARY_POSITION => integer; position of column in primary key * IDENTITY => integer; true if column is auto-generated with unique values * * @param string $tableName * @param string $schemaName OPTIONAL * @return array */
else {
throw new StringStorageException('The string cannot be deleted because it lacks some key fields: ' . $string->getString());
}
return $this;
}
/** * {@inheritdoc} */
public function deleteStrings($conditions) {
$lids = $this->dbStringSelect($conditions, ['fields' => ['lid']])->execute()->fetchCol();
if ($lids) {
$this->dbDelete('locales_target', ['lid' => $lids])->execute();
$this->dbDelete('locales_source', ['lid' => $lids])->execute();
$this->dbDelete('locales_location', ['sid' => $lids])->execute();
}
}
/** * {@inheritdoc} */
public function deleteTranslations($conditions) {
}
else {
// Regular users can only search via usernames, and we do not show them // blocked accounts. $query->condition('name', '%' . $keys . '%', 'LIKE')
->condition('status', 1);
}
$uids = $query
->limit(15)
->execute()
->fetchCol();
$accounts = $this->entityTypeManager->getStorage('user')->loadMultiple($uids);
foreach ($accounts as $account) {
$result = [
'title' => $account->getDisplayName(),
'link' => $account->toUrl('canonical', ['absolute' => TRUE])->toString(),
];
if ($this->currentUser->hasPermission('administer users')) {
$result['title'] .= ' (' . $account->getEmail() . ')';
}
$this->addCacheableDependency($account);
$loaded_terms = $term_storage->loadChildren($terms[1]->id());
// All child terms were loaded. $this->assertCount(1, $loaded_terms);
// TermStorage::loadChildren(). $this->assertQueryTagTestResult(3, 1);
$this->setupQueryTagTestHooks();
$connection = Database::getConnection();
$query = $connection->select('taxonomy_term_data', 't');
$query->addField('t', 'tid');
$query->addTag('taxonomy_term_access');
$tids = $query->execute()->fetchCol();
// All term IDs were retrieved. $this->assertCount(5, $tids);
// Database custom ::select() with 'taxonomy_term_access' tag (preferred). $this->assertQueryTagTestResult(1, 1);
$this->setupQueryTagTestHooks();
$query = $connection->select('taxonomy_term_data', 't');
$query->addField('t', 'tid');
$query->addTag('term_access');
$tids = $query->execute()->fetchCol();
// All term IDs were retrieved.
// All child terms were loaded. $this->assertCount(1, $loaded_terms);
// TermStorage::loadChildren(). $this->assertQueryTagTestResult(3, 1);
$this->setupQueryTagTestHooks();
$connection = Database::getConnection();
$query = $connection->select('taxonomy_term_data', 't');
$query->addField('t', 'tid');
$query->addTag('taxonomy_term_access');
$tids = $query->execute()->fetchCol();
// All term IDs were retrieved. $this->assertCount(5, $tids);
// Database custom ::select() with 'taxonomy_term_access' tag (preferred). $this->assertQueryTagTestResult(1, 1);
$this->setupQueryTagTestHooks();
$query = $connection->select('taxonomy_term_data', 't');
$query->addField('t', 'tid');
$query->addTag('term_access');
$tids = $query->execute()->fetchCol();
// All term IDs were retrieved.
->execute();
}
/** * {@inheritdoc} */
public function getUnused($limit = 100) {
$query = $this->database->select('inline_block_usage', 't');
$query->fields('t', ['block_content_id']);
$query->isNull('layout_entity_id');
$query->isNull('layout_entity_type');
return $query->range(0, $limit)->execute()->fetchCol();
}
/** * {@inheritdoc} */
public function removeByLayoutEntity(EntityInterface $entity) {
$query = $this->database->update('inline_block_usage')
->fields([
'layout_entity_type' => NULL,
'layout_entity_id' => NULL,
]);
}
/** * {@inheritdoc} */
public function getUnused($limit = 100) {
$query = $this->database->select('inline_block_usage', 't');
$query->fields('t', ['block_content_id']);
$query->isNull('layout_entity_id');
$query->isNull('layout_entity_type');
return $query->range(0, $limit)->execute()->fetchCol();
}
/** * {@inheritdoc} */
public function removeByLayoutEntity(EntityInterface $entity) {
$query = $this->database->update('inline_block_usage')
->fields([
'layout_entity_type' => NULL,
'layout_entity_id' => NULL,
]);
$connection->update('node_field_data')
->fields(['changed' => $changed])
->condition('nid', $node->id())
->execute();
}
// Test that the default sort by node.changed DESC actually fires properly. $nodes_query = $connection->select('node_field_data', 'n')
->fields('n', ['title'])
->orderBy('changed', 'DESC')
->execute()
->fetchCol();
$this->drupalGet('admin/content');
foreach ($nodes_query as $delta => $string) {
// Verify that the node was found in the correct order. $this->assertSession()->elementExists('xpath', $this->assertSession()->buildXPathQuery('//table/tbody/tr[' . ($delta + 1) . ']/td[2]/a[normalize-space(text())=:label]', [
':label' => $string,
]));
}
// Compare the rendered HTML node list to a query for the nodes ordered by // title to account for possible database-dependent sort order.
->fields(['changed' => $changed])
->condition('nid', $node->id())
->execute();
}
// Test that the default sort by node.changed DESC actually fires properly. $nodes_query = $connection->select('node_field_data', 'n')
->fields('n', ['title'])
->orderBy('changed', 'DESC')
->execute()
->fetchCol();
$this->drupalGet('admin/content');
foreach ($nodes_query as $delta => $string) {
// Verify that the node was found in the correct order. $this->assertSession()->elementExists('xpath', $this->assertSession()->buildXPathQuery('//table/tbody/tr[' . ($delta + 1) . ']/td[2]/a[normalize-space(text())=:label]', [
':label' => $string,
]));
}
// Compare the rendered HTML node list to a query for the nodes ordered by // title to account for possible database-dependent sort order.
class NodeStorage extends SqlContentEntityStorage implements NodeStorageInterface {
/** * {@inheritdoc} */
public function revisionIds(NodeInterface $node) {
return $this->database->query(
'SELECT [vid] FROM {' . $this->getRevisionTable() . '} WHERE [nid] = :nid ORDER BY [vid]',
[':nid' => $node->id()]
)->fetchCol();
}
/** * {@inheritdoc} */
public function userRevisionIds(AccountInterface $account) {
return $this->database->query(
'SELECT [vid] FROM {' . $this->getRevisionDataTable() . '} WHERE [uid] = :uid ORDER BY [vid]',
[':uid' => $account->id()]
)->fetchCol();
}
/**
* Confirms that a query can be serialized and unserialized.
*/
public function testSerializeQuery() {
$query = $this->connection->select('test');
$query->addField('test', 'age');
$query->condition('name', 'Ringo');
// If this doesn't work, it will throw an exception, so no need for an // assertion. $query = unserialize(serialize($query));
$results = $query->execute()->fetchCol();
$this->assertEquals(28, $results[0], 'Query properly executed after unserialization.');
}
}
public function testSerializeQuery() {
$query = $this->connection->select('test');
$query->addField('test', 'age');
$query->condition('name', 'Ringo');
// If this doesn't work, it will throw an exception, so no need for an // assertion. $query = unserialize(serialize($query));
$results = $query->execute()->fetchCol();
$this->assertEquals(28, $results[0], 'Query properly executed after unserialization.');
}
}
foreach ($options as $option) {
$alias = 'alias' . $option->getId();
$join = $join . ' INNER JOIN s_article_configurator_option_relations alias' . $option->getId() .
' ON ' . $alias . '.option_id = ' . $option->getId() .
' AND ' . $alias . '.article_id = d.id ';
}
$sql = 'SELECT d.id FROM s_articles_details d ' . $join . ' WHERE d.articleID = ' . (int) $productId;
$details = Shopware()->Db()->fetchCol($sql);
foreach ($details as $detailId) {
$detail = $this->get('models')->getReference(ProductVariant::class, $detailId);
$image = new Image();
$image->fromArray($imageData);
$image->setArticleDetail($detail);
$this->get('models')->persist($image);
}
$this->get('models')->flush();
}
$alias = 'alias' . $option->getId();
$join = $join . ' INNER JOIN s_article_configurator_option_relations alias' . $option->getId() .
' ON ' . $alias . '.option_id = ' . $option->getId() .
' AND ' . $alias . '.article_id = d.id ';
}
$sql = 'SELECT d.id FROM s_articles_details d ' . $join . ' WHERE d.articleID = ' . (int) $productId;
$details = Shopware()->Db()->fetchCol($sql);
foreach ($details as $detailId) {
$detail = $this->get('models')->getReference(ProductVariant::class, $detailId);
$image = new Image();
$image->fromArray($imageData);
$image->setArticleDetail($detail);
$this->get('models')->persist($image);
}
$this->get('models')->flush();
}