CodeExplorer
You are a developer and looking for Shopware projects?
Apply Now!
addExpression example
private function runCron() {
$connection = Database::getConnection();
// Get last ID to compare against; log entries get deleted, so we can't // reliably add the number of newly created log entries to the current count // to measure number of log entries created by cron. $query = $connection->select('watchdog');
$query->addExpression('MAX([wid])');
$last_id = $query->execute()->fetchField();
// Run a cron job. $this->container->get('cron')->run();
// Get last ID after cron was run. $query = $connection->select('watchdog');
$query->addExpression('MAX([wid])');
$current_id = $query->execute()->fetchField();
return $current_id - $last_id;
}
// WHERE tt2.pid IN (SELECT tt.pid AS pid FROM test_task tt WHERE tt.priority=1)
$people = $select->execute()->fetchCol();
$this->assertCount(5, $people, 'Returned the correct number of rows.');
}
/** * Tests we can use a subquery with a relational operator in a WHERE clause. */
public function testConditionSubquerySelect2() {
// Create a subquery, which is just a normal query object. $subquery = $this->connection->select('test', 't2');
$subquery->addExpression('AVG([t2].[age])');
// Create another query that adds a clause using the subquery. $select = $this->connection->select('test', 't');
$select->addField('t', 'name');
$select->condition('t.age', $subquery, '<');
// The resulting query should be equivalent to: // SELECT t.name // FROM test t // WHERE t.age < (SELECT AVG(t2.age) FROM test t2) $people = $select->execute()->fetchCol();
$this->assertCount(5, $people, 'Returned the correct number of rows.');
}
/** * Tests we can use a subquery with a relational operator in a WHERE clause. */
public function testConditionSubquerySelect2() {
// Create a subquery, which is just a normal query object. $subquery = $this->connection->select('test', 't2');
$subquery->addExpression('AVG([t2].[age])');
// Create another query that adds a clause using the subquery. $select = $this->connection->select('test', 't');
$select->addField('t', 'name');
$select->condition('t.age', $subquery, '<');
// The resulting query should be equivalent to: // SELECT t.name // FROM test t // WHERE t.age < (SELECT AVG(t2.age) FROM test t2) $people = $select->execute()->fetchCol();
// simple, we do not need them for normalization.
if (!$this->simple) {
$normalize_query->join('search_dataset', 'd', '[i].[sid] = [d].[sid] AND [i].[type] = [d].[type] AND [i].[langcode] = [d].[langcode]');
if (count($this->conditions)) {
$normalize_query->condition($this->conditions);
}
}
// Calculate normalization, which is the max of all the search scores for // positive keywords in the query. And note that the query could have other // fields added to it by the user of this extension. $normalize_query->addExpression('SUM([i].[score] * [t].[count])', 'calculated_score');
$result = $normalize_query
->range(0, 1)
->orderBy('calculated_score', 'DESC')
->execute()
->fetchObject();
if (isset($result->calculated_score)) {
$this->normalize = (float) $result->calculated_score;
}
if ($this->normalize) {
return TRUE;
}
$normalize_query->join('search_dataset', 'd', '[i].[sid] = [d].[sid] AND [i].[type] = [d].[type] AND [i].[langcode] = [d].[langcode]');
if (count($this->conditions)) {
$normalize_query->condition($this->conditions);
}
}
// Calculate normalization, which is the max of all the search scores for // positive keywords in the query. And note that the query could have other // fields added to it by the user of this extension. $normalize_query->addExpression('SUM([i].[score] * [t].[count])', 'calculated_score');
$result = $normalize_query
->range(0, 1)
->orderBy('calculated_score', 'DESC')
->execute()
->fetchObject();
if (isset($result->calculated_score)) {
$this->normalize = (float) $result->calculated_score;
}
if ($this->normalize) {
return TRUE;
}
/**
* {@inheritdoc}
*/
public function updateIndex() {
// Interpret the cron limit setting as the maximum number of nodes to index // per cron run. $limit = (int) $this->searchSettings->get('index.cron_limit');
$query = $this->databaseReplica->select('node', 'n');
$query->addField('n', 'nid');
$query->leftJoin('search_dataset', 'sd', '[sd].[sid] = [n].[nid] AND [sd].[type] = :type', [':type' => $this->getPluginId()]);
$query->addExpression('CASE MAX([sd].[reindex]) WHEN NULL THEN 0 ELSE 1 END', 'ex');
$query->addExpression('MAX([sd].[reindex])', 'ex2');
$query->condition(
$query->orConditionGroup()
->where('[sd].[sid] IS NULL')
->condition('sd.reindex', 0, '<>')
);
$query->orderBy('ex', 'DESC')
->orderBy('ex2')
->orderBy('n.nid')
->groupBy('n.nid')
->range(0, $limit);
public function updateIndex() {
// Interpret the cron limit setting as the maximum number of nodes to index // per cron run. $limit = (int) $this->searchSettings->get('index.cron_limit');
$query = $this->databaseReplica->select('node', 'n');
$query->addField('n', 'nid');
$query->leftJoin('search_dataset', 'sd', '[sd].[sid] = [n].[nid] AND [sd].[type] = :type', [':type' => $this->getPluginId()]);
$query->addExpression('CASE MAX([sd].[reindex]) WHEN NULL THEN 0 ELSE 1 END', 'ex');
$query->addExpression('MAX([sd].[reindex])', 'ex2');
$query->condition(
$query->orConditionGroup()
->where('[sd].[sid] IS NULL')
->condition('sd.reindex', 0, '<>')
);
$query->orderBy('ex', 'DESC')
->orderBy('ex2')
->orderBy('n.nid')
->groupBy('n.nid')
->range(0, $limit);
$query->fields('f');
$query
->condition('f.tid', $tid)
->addTag('node_access')
->addMetaData('base_table', 'forum_index')
->orderBy('f.sticky', 'DESC')
->orderByHeader($header)
->limit($forum_per_page);
$count_query = $this->connection->select('forum_index', 'f');
$count_query->condition('f.tid', $tid);
$count_query->addExpression('COUNT(*)');
$count_query->addTag('node_access');
$count_query->addMetaData('base_table', 'forum_index');
$query->setCountQuery($count_query);
$result = $query->execute();
$nids = [];
foreach ($result as $record) {
$nids[] = $record->nid;
}
if ($nids) {
$nodes = $this->entityTypeManager->getStorage('node')->loadMultiple($nids);
$query
->condition('f.tid', $tid)
->addTag('node_access')
->addMetaData('base_table', 'forum_index')
->orderBy('f.sticky', 'DESC')
->orderByHeader($header)
->limit($forum_per_page);
$count_query = $this->connection->select('forum_index', 'f');
$count_query->condition('f.tid', $tid);
$count_query->addExpression('COUNT(*)');
$count_query->addTag('node_access');
$count_query->addMetaData('base_table', 'forum_index');
$query->setCountQuery($count_query);
$result = $query->execute();
$nids = [];
foreach ($result as $record) {
$nids[] = $record->nid;
}
if ($nids) {
$nodes = $this->entityTypeManager->getStorage('node')->loadMultiple($nids);
$record = $result->fetch();
$this->assertEquals('George', $record->{$name_field}, 'Fetched name is correct.');
$this->assertEquals(27, $record->{$age_field}, 'Fetched age is correct.');
}
/** * Tests SELECT statements with expressions. */
public function testSimpleSelectExpression() {
$query = $this->connection->select('test');
$name_field = $query->addField('test', 'name');
$age_field = $query->addExpression("[age]*2", 'double_age');
$query->condition('age', 27);
$result = $query->execute();
// Check that the aliases are being created the way we want. $this->assertEquals('name', $name_field, 'Name field alias is correct.');
$this->assertEquals('double_age', $age_field, 'Age field alias is correct.');
// Ensure that we got the right record. $record = $result->fetch();
$this->assertEquals('George', $record->{$name_field}, 'Fetched name is correct.');
$this->assertEquals(27 * 2, $record->{$age_field}, 'Fetched age expression is correct.');
}
$this->assertEquals('George', $record->{$name_field}, 'Fetched name is correct.');
$this->assertEquals(27, $record->{$age_field}, 'Fetched age is correct.');
}
/** * Tests SELECT statements with expressions. */
public function testSimpleSelectExpression() {
$query = $this->connection->select('test');
$name_field = $query->addField('test', 'name');
$age_field = $query->addExpression("[age]*2", 'double_age');
$query->condition('age', 27);
$result = $query->execute();
// Check that the aliases are being created the way we want. $this->assertEquals('name', $name_field, 'Name field alias is correct.');
$this->assertEquals('double_age', $age_field, 'Age field alias is correct.');
// Ensure that we got the right record. $record = $result->fetch();
$this->assertEquals('George', $record->{$name_field}, 'Fetched name is correct.');
$this->assertEquals(27 * 2, $record->{$age_field}, 'Fetched age expression is correct.');
}
public function fields($table_alias, array $fields = []) {
$this->query->fields($table_alias, $fields);
return $this;
}
/** * {@inheritdoc} */
public function addExpression($expression, $alias = NULL, $arguments = []) {
return $this->query->addExpression($expression, $alias, $arguments);
}
/** * {@inheritdoc} */
public function join($table, $alias = NULL, $condition = NULL, $arguments = []) {
return $this->query->join($table, $alias, $condition, $arguments);
}
/** * {@inheritdoc} */
return $this;
}
/** * Overrides \Drupal\Core\Entity\Query\Sql\Query::finish(). * * Adds the sql expressions to the query. */
protected function finish() {
foreach ($this->sqlExpressions as $alias => $expression) {
$this->sqlQuery->addExpression($expression, $alias);
}
return parent::finish();
}
/** * Builds a sql alias as expected in the result. * * @param string $field * The field as passed in by the caller. * @param string $sql_field * The sql field as returned by getSqlField. * * @return string * The SQL alias expected in the return value. The dots in $sql_field are * replaced with underscores and if a default fallback to .value happened, * the _value is stripped. */
/**
* Gets the number of rows in the test cache bin database table.
*
* @return int
* The number of rows in the test cache bin database table.
*/
protected function getNumRows() {
$table = 'cache_' . $this->testBin;
$connection = $this->container->get('database');
$query = $connection->select($table);
$query->addExpression('COUNT([cid])', 'cid');
return (int) $query->execute()->fetchField();
}
/** * Test that the service "cache_tags.invalidator.checksum" is backend overridable. */
public function testCacheTagsInvalidatorChecksumIsBackendOverridable() {
$definition = $this->container->getDefinition('cache_tags.invalidator.checksum');
$this->assertTrue($definition->hasTag('backend_overridable'));
}
protected function getNumRows() {
$table = 'cache_' . $this->testBin;
$connection = $this->container->get('database');
$query = $connection->select($table);
$query->addExpression('COUNT([cid])', 'cid');
return (int) $query->execute()->fetchField();
}
/** * Test that the service "cache_tags.invalidator.checksum" is backend overridable. */
public function testCacheTagsInvalidatorChecksumIsBackendOverridable() {
$definition = $this->container->getDefinition('cache_tags.invalidator.checksum');
$this->assertTrue($definition->hasTag('backend_overridable'));
}
/**
* {@inheritdoc}
*/
public function __construct(Connection $connection, $table, $alias = NULL, array $options = []) {
// @todo Remove the __construct in Drupal 11. // @see https://www.drupal.org/project/drupal/issues/3256524 parent::__construct($connection, $table, $alias, $options);
unset($this->queryOptions['return']);
}
public function orderRandom() {
$alias = $this->addExpression('RANDOM()', 'random_field');
$this->orderBy($alias);
return $this;
}
/** * Overrides SelectQuery::orderBy(). * * PostgreSQL adheres strictly to the SQL-92 standard and requires that when * using DISTINCT or GROUP BY conditions, fields and expressions that are * ordered on also need to be selected. This is a best effort implementation * to handle the cases that can be automated by adding the field if it is not * yet selected. * * @code * $query = \Drupal::database()->select('example', 'e'); * $query->join('example_revision', 'er', '[e].[vid] = [er].[vid]'); * $query * ->distinct() * ->fields('e') * ->orderBy('timestamp'); * @endcode * * In this query, it is not possible (without relying on the schema) to know * whether timestamp belongs to example_revision and needs to be added or * belongs to node and is already selected. Queries like this will need to be * corrected in the original query by adding an explicit call to * SelectQuery::addField() or SelectQuery::fields(). * * Since this has a small performance impact, both by the additional * processing in this function and in the database that needs to return the * additional fields, this is done as an override instead of implementing it * directly in SelectQuery::orderBy(). */
public function __construct(Connection $connection, $table, $alias = NULL, array $options = []) {
// @todo Remove the __construct in Drupal 11. // @see https://www.drupal.org/project/drupal/issues/3256524 parent::__construct($connection, $table, $alias, $options);
unset($this->queryOptions['return']);
}
public function orderRandom() {
$alias = $this->addExpression('RANDOM()', 'random_field');
$this->orderBy($alias);
return $this;
}
/** * Overrides SelectQuery::orderBy(). * * PostgreSQL adheres strictly to the SQL-92 standard and requires that when * using DISTINCT or GROUP BY conditions, fields and expressions that are * ordered on also need to be selected. This is a best effort implementation * to handle the cases that can be automated by adding the field if it is not * yet selected. * * @code * $query = \Drupal::database()->select('example', 'e'); * $query->join('example_revision', 'er', '[e].[vid] = [er].[vid]'); * $query * ->distinct() * ->fields('e') * ->orderBy('timestamp'); * @endcode * * In this query, it is not possible (without relying on the schema) to know * whether timestamp belongs to example_revision and needs to be added or * belongs to node and is already selected. Queries like this will need to be * corrected in the original query by adding an explicit call to * SelectQuery::addField() or SelectQuery::fields(). * * Since this has a small performance impact, both by the additional * processing in this function and in the database that needs to return the * additional fields, this is done as an override instead of implementing it * directly in SelectQuery::orderBy(). */
$select->orderBy('base_table.id', 'DESC');
return $select->execute()->fetchAssoc() ?: NULL;
}
/** * {@inheritdoc} */
public function pathHasMatchingAlias($initial_substring) {
$query = $this->getBaseQuery();
$query->addExpression(1);
return (bool) $query
->condition('base_table.path', $this->connection->escapeLike($initial_substring) . '%', 'LIKE')
->range(0, 1)
->execute()
->fetchField();
}
/** * Returns a SELECT query for the path_alias base table. * * @return \Drupal\Core\Database\Query\SelectInterface * A Select query object. */
return $select->execute()->fetchAssoc() ?: NULL;
}
/** * {@inheritdoc} */
public function pathHasMatchingAlias($initial_substring) {
$query = $this->getBaseQuery();
$query->addExpression(1);
return (bool) $query
->condition('base_table.path', $this->connection->escapeLike($initial_substring) . '%', 'LIKE')
->range(0, 1)
->execute()
->fetchField();
}
/** * Returns a SELECT query for the path_alias base table. * * @return \Drupal\Core\Database\Query\SelectInterface * A Select query object. */
if (!empty($field['count'])) {
// Retained for compatibility. $field['function'] = 'count';
}
if (!empty($field['function'])) {
$info = $this->getAggregationInfo();
if (!empty($info[$field['function']]['method']) && is_callable([$this, $info[$field['function']]['method']])) {
$string = $this::{$info[$field['function']]['method']}($field['function'], $string);
$placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
$query->addExpression($string, $fieldname, $placeholders);
}
$this->hasAggregate = TRUE;
}
// This is a formula, using no tables. elseif (empty($field['table'])) {
$placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
$query->addExpression($string, $fieldname, $placeholders);
}
elseif ($this->distinct && !in_array($fieldname, $this->groupby)) {
$query->addField(!empty($field['table']) ? $field['table'] : $this->view->storage->get('base_table'), $field['field'], $fieldname);
}
// Retained for compatibility. $field['function'] = 'count';
}
if (!empty($field['function'])) {
$info = $this->getAggregationInfo();
if (!empty($info[$field['function']]['method']) && is_callable([$this, $info[$field['function']]['method']])) {
$string = $this::{$info[$field['function']]['method']}($field['function'], $string);
$placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
$query->addExpression($string, $fieldname, $placeholders);
}
$this->hasAggregate = TRUE;
}
// This is a formula, using no tables. elseif (empty($field['table'])) {
$placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
$query->addExpression($string, $fieldname, $placeholders);
}
elseif ($this->distinct && !in_array($fieldname, $this->groupby)) {
$query->addField(!empty($field['table']) ? $field['table'] : $this->view->storage->get('base_table'), $field['field'], $fieldname);
}
/** * {@inheritdoc} */
public function getMaxThread(CommentInterface $comment) {
$query = $this->database->select($this->getDataTable(), 'c')
->condition('entity_id', $comment->getCommentedEntityId())
->condition('field_name', $comment->getFieldName())
->condition('entity_type', $comment->getCommentedEntityTypeId())
->condition('default_langcode', 1);
$query->addExpression('MAX([thread])', 'thread');
return $query->execute()
->fetchField();
}
/** * {@inheritdoc} */
public function getMaxThreadPerThread(CommentInterface $comment) {
$query = $this->database->select($this->getDataTable(), 'c')
->condition('entity_id', $comment->getCommentedEntityId())
->condition('field_name', $comment->getFieldName())
// self::writeDefault().
if ($operation === 'view') {
return AccessResult::allowedIf($node->isPublished());
}
else {
return AccessResult::neutral();
}
}
// Check the database for potential access grants. $query = $this->database->select('node_access');
$query->addExpression('1');
// Only interested for granting in the current operation. $query->condition('grant_' . $operation, 1, '>=');
// Check for grants for this node and the correct langcode. $nids = $query->andConditionGroup()
->condition('nid', $node->id())
->condition('langcode', $node->language()->getId());
// If the node is published, also take the default grant into account. The // default is saved with a node ID of 0. $status = $node->isPublished();
if ($status) {
$nids = $query->orConditionGroup()
return AccessResult::allowedIf($node->isPublished());
}
else {
return AccessResult::neutral();
}
}
// Check the database for potential access grants. $query = $this->database->select('node_access');
$query->addExpression('1');
// Only interested for granting in the current operation. $query->condition('grant_' . $operation, 1, '>=');
// Check for grants for this node and the correct langcode. $nids = $query->andConditionGroup()
->condition('nid', $node->id())
->condition('langcode', $node->language()->getId());
// If the node is published, also take the default grant into account. The // default is saved with a node ID of 0. $status = $node->isPublished();
if ($status) {
$nids = $query->orConditionGroup()
// Remove remaining items from the index.
$this->removeItemsFromIndex($sids_to_remove);
}
/** * Updates the 'help_search_unindexed_count' state variable. * * The state variable is a count of help topics that have never been indexed. */
public function updateIndexState() {
$query = $this->database->select('help_search_items', 'hsi');
$query->addExpression('COUNT(DISTINCT(hsi.sid))');
$query->leftJoin('search_dataset', 'sd', 'hsi.sid = sd.sid AND sd.type = :type', [':type' => $this->getType()]);
$query->isNull('sd.sid');
$never_indexed = $query->execute()->fetchField();
$this->state->set('help_search_unindexed_count', $never_indexed);
}
/** * {@inheritdoc} */
public function markForReindex() {
$this->updateTopicList();
}
/** * Updates the 'help_search_unindexed_count' state variable. * * The state variable is a count of help topics that have never been indexed. */
public function updateIndexState() {
$query = $this->database->select('help_search_items', 'hsi');
$query->addExpression('COUNT(DISTINCT(hsi.sid))');
$query->leftJoin('search_dataset', 'sd', 'hsi.sid = sd.sid AND sd.type = :type', [':type' => $this->getType()]);
$query->isNull('sd.sid');
$never_indexed = $query->execute()->fetchField();
$this->state->set('help_search_unindexed_count', $never_indexed);
}
/** * {@inheritdoc} */
public function markForReindex() {
$this->updateTopicList();