3 namespace Drupal\views\Plugin\views\query;
5 use Drupal\Component\Utility\NestedArray;
6 use Drupal\Core\Cache\Cache;
7 use Drupal\Core\Database\Database;
8 use Drupal\Core\Database\Query\Condition;
9 use Drupal\Core\Entity\EntityTypeManagerInterface;
10 use Drupal\Core\Form\FormStateInterface;
11 use Drupal\views\Plugin\views\display\DisplayPluginBase;
12 use Drupal\Core\Database\DatabaseExceptionWrapper;
13 use Drupal\views\Plugin\views\join\JoinPluginBase;
14 use Drupal\views\Plugin\views\HandlerBase;
15 use Drupal\views\ResultRow;
16 use Drupal\views\ViewExecutable;
17 use Drupal\views\Views;
18 use Symfony\Component\DependencyInjection\ContainerInterface;
21 * Views query plugin for an SQL query.
23 * @ingroup views_query_plugins
27 * title = @Translation("SQL Query"),
28 * help = @Translation("Query will be generated and run using the Drupal database API.")
31 class Sql extends QueryPluginBase {
34 * A list of tables in the order they should be added, keyed by alias.
36 protected $tableQueue = [];
39 * Holds an array of tables and counts added so that we can create aliases
44 * Holds an array of relationships, which are aliases of the primary
45 * table that represent different ways to join the same table in.
47 public $relationships = [];
50 * An array of sections of the WHERE query. Each section is in itself
51 * an array of pieces and a flag as to whether or not it should be AND
56 * An array of sections of the HAVING query. Each section is in itself
57 * an array of pieces and a flag as to whether or not it should be AND
62 * The default operator to use when connecting the WHERE groups. May be
67 protected $groupOperator = 'AND';
70 * A simple array of order by clauses.
75 * A simple array of group by clauses.
86 * A flag as to whether or not to make the primary field distinct.
90 public $distinct = FALSE;
95 protected $hasAggregate = FALSE;
98 * Should this query be optimized for counts, for example no sorts.
100 protected $getCountOptimized = NULL;
103 * An array mapping table aliases and field names to field aliases.
105 protected $fieldAliases = [];
108 * Query tags which will be passed over to the dbtng query object.
113 * Is the view marked as not distinct.
117 protected $noDistinct;
120 * The entity type manager.
122 * @var \Drupal\Core\Entity\EntityTypeManagerInterface
124 protected $entityTypeManager;
127 * Constructs a Sql object.
129 * @param array $configuration
130 * A configuration array containing information about the plugin instance.
131 * @param string $plugin_id
132 * The plugin_id for the plugin instance.
133 * @param mixed $plugin_definition
134 * The plugin implementation definition.
135 * @param \Drupal\Core\Entity\EntityTypeManagerInterface $entity_type_manager
136 * The entity type manager.
138 public function __construct(array $configuration, $plugin_id, $plugin_definition, EntityTypeManagerInterface $entity_type_manager) {
139 parent::__construct($configuration, $plugin_id, $plugin_definition);
141 $this->entityTypeManager = $entity_type_manager;
144 public static function create(ContainerInterface $container, array $configuration, $plugin_id, $plugin_definition) {
149 $container->get('entity_type.manager')
156 public function init(ViewExecutable $view, DisplayPluginBase $display, array &$options = NULL) {
157 parent::init($view, $display, $options);
159 $base_table = $this->view->storage->get('base_table');
160 $base_field = $this->view->storage->get('base_field');
161 $this->relationships[$base_table] = [
163 'table' => $base_table,
164 'alias' => $base_table,
165 'base' => $base_table
168 // init the table queue with our primary table.
169 $this->tableQueue[$base_table] = [
170 'alias' => $base_table,
171 'table' => $base_table,
172 'relationship' => $base_table,
176 // init the tables with our primary table
177 $this->tables[$base_table][$base_table] = [
179 'alias' => $base_table,
182 $this->count_field = [
183 'table' => $base_table,
184 'field' => $base_field,
185 'alias' => $base_field,
191 * Set the view to be distinct (per base field).
194 * Should the view be distincted.
196 protected function setDistinct($value = TRUE) {
197 if (!(isset($this->noDistinct) && $value)) {
198 $this->distinct = $value;
203 * Set what field the query will count() on for paging.
205 public function setCountField($table, $field, $alias = NULL) {
207 $alias = $table . '_' . $field;
209 $this->count_field = [
217 protected function defineOptions() {
218 $options = parent::defineOptions();
219 $options['disable_sql_rewrite'] = [
222 $options['distinct'] = [
225 $options['replica'] = [
228 $options['query_comment'] = [
231 $options['query_tags'] = [
239 * Add settings for the ui.
241 public function buildOptionsForm(&$form, FormStateInterface $form_state) {
242 parent::buildOptionsForm($form, $form_state);
244 $form['disable_sql_rewrite'] = [
245 '#title' => $this->t('Disable SQL rewriting'),
246 '#description' => $this->t('Disabling SQL rewriting will omit all query tags, i. e. disable node access checks as well as override hook_query_alter() implementations in other modules.'),
247 '#type' => 'checkbox',
248 '#default_value' => !empty($this->options['disable_sql_rewrite']),
249 '#suffix' => '<div class="messages messages--warning sql-rewrite-warning js-hide">' . $this->t('WARNING: Disabling SQL rewriting means that node access security is disabled. This may allow users to see data they should not be able to see if your view is misconfigured. Use this option only if you understand and accept this security risk.') . '</div>',
251 $form['distinct'] = [
252 '#type' => 'checkbox',
253 '#title' => $this->t('Distinct'),
254 '#description' => $this->t('This will make the view display only distinct items. If there are multiple identical items, each will be displayed only once. You can use this to try and remove duplicates from a view, though it does not always work. Note that this can slow queries down, so use it with caution.'),
255 '#default_value' => !empty($this->options['distinct']),
258 '#type' => 'checkbox',
259 '#title' => $this->t('Use Secondary Server'),
260 '#description' => $this->t('This will make the query attempt to connect to a replica server if available. If no replica server is defined or available, it will fall back to the default server.'),
261 '#default_value' => !empty($this->options['replica']),
263 $form['query_comment'] = [
264 '#type' => 'textfield',
265 '#title' => $this->t('Query Comment'),
266 '#description' => $this->t('If set, this comment will be embedded in the query and passed to the SQL server. This can be helpful for logging or debugging.'),
267 '#default_value' => $this->options['query_comment'],
269 $form['query_tags'] = [
270 '#type' => 'textfield',
271 '#title' => $this->t('Query Tags'),
272 '#description' => $this->t('If set, these tags will be appended to the query and can be used to identify the query in a module. This can be helpful for altering queries.'),
273 '#default_value' => implode(', ', $this->options['query_tags']),
274 '#element_validate' => ['views_element_validate_tags'],
279 * Special submit handling.
281 public function submitOptionsForm(&$form, FormStateInterface $form_state) {
282 $element = ['#parents' => ['query', 'options', 'query_tags']];
283 $value = explode(',', NestedArray::getValue($form_state->getValues(), $element['#parents']));
284 $value = array_filter(array_map('trim', $value));
285 $form_state->setValueForElement($element, $value);
289 * A relationship is an alternative endpoint to a series of table
290 * joins. Relationships must be aliases of the primary table and
291 * they must join either to the primary table or to a pre-existing
294 * An example of a relationship would be a nodereference table.
295 * If you have a nodereference named 'book_parent' which links to a
296 * parent node, you could set up a relationship 'node_book_parent'
297 * to 'node'. Then, anything that links to 'node' can link to
298 * 'node_book_parent' instead, thus allowing all properties of
299 * both nodes to be available in the query.
302 * What this relationship will be called, and is also the alias
304 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
305 * A Join object (or derived object) to join the alias in.
307 * The name of the 'base' table this relationship represents; this
308 * tells the join search which path to attempt to use when finding
309 * the path to this relationship.
311 * If this relationship links to something other than the primary
312 * table, specify that table here. For example, a 'track' node
313 * might have a relationship to an 'album' node, which might
314 * have a relationship to an 'artist' node.
316 public function addRelationship($alias, JoinPluginBase $join, $base, $link_point = NULL) {
317 if (empty($link_point)) {
318 $link_point = $this->view->storage->get('base_table');
320 elseif (!array_key_exists($link_point, $this->relationships)) {
324 // Make sure $alias isn't already used; if it, start adding stuff.
325 $alias_base = $alias;
327 while (!empty($this->relationships[$alias])) {
328 $alias = $alias_base . '_' . $count++;
331 // Make sure this join is adjusted for our relationship.
332 if ($link_point && isset($this->relationships[$link_point])) {
333 $join = $this->adjustJoin($join, $link_point);
336 // Add the table directly to the queue to avoid accidentally marking
338 $this->tableQueue[$alias] = [
339 'table' => $join->table,
343 'relationship' => $link_point,
346 $this->relationships[$alias] = [
347 'link' => $link_point,
348 'table' => $join->table,
352 $this->tables[$this->view->storage->get('base_table')][$alias] = [
361 * Add a table to the query, ensuring the path exists.
363 * This function will test to ensure that the path back to the primary
364 * table is valid and exists; if you do not wish for this testing to
365 * occur, use $query->queueTable() instead.
368 * The name of the table to add. It needs to exist in the global table
370 * @param $relationship
371 * An alias of a table; if this is set, the path back to this table will
372 * be tested prior to adding the table, making sure that all intermediary
373 * tables exist and are properly aliased. If set to NULL the path to
374 * the primary table will be ensured. If the path cannot be made, the
375 * table will NOT be added.
376 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
377 * In some join configurations this table may actually join back through
378 * a different method; this is most likely to be used when tracing
379 * a hierarchy path. (node->parent->parent2->parent3). This parameter
380 * will specify how this table joins if it is not the default.
382 * A specific alias to use, rather than the default alias.
385 * The alias of the table; this alias can be used to access information
386 * about the table and should always be used to refer to the table when
387 * adding parts to the query. Or FALSE if the table was not able to be
390 public function addTable($table, $relationship = NULL, JoinPluginBase $join = NULL, $alias = NULL) {
391 if (!$this->ensurePath($table, $relationship, $join)) {
395 if ($join && $relationship) {
396 $join = $this->adjustJoin($join, $relationship);
399 return $this->queueTable($table, $relationship, $join, $alias);
403 * Add a table to the query without ensuring the path.
405 * This is a pretty internal function to Views and addTable() or
406 * ensureTable() should be used instead of this one, unless you are
407 * absolutely sure this is what you want.
410 * The name of the table to add. It needs to exist in the global table
412 * @param $relationship
413 * The primary table alias this table is related to. If not set, the
414 * primary table will be used.
415 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
416 * In some join configurations this table may actually join back through
417 * a different method; this is most likely to be used when tracing
418 * a hierarchy path. (node->parent->parent2->parent3). This parameter
419 * will specify how this table joins if it is not the default.
421 * A specific alias to use, rather than the default alias.
424 * The alias of the table; this alias can be used to access information
425 * about the table and should always be used to refer to the table when
426 * adding parts to the query. Or FALSE if the table was not able to be
429 public function queueTable($table, $relationship = NULL, JoinPluginBase $join = NULL, $alias = NULL) {
430 // If the alias is set, make sure it doesn't already exist.
431 if (isset($this->tableQueue[$alias])) {
435 if (empty($relationship)) {
436 $relationship = $this->view->storage->get('base_table');
439 if (!array_key_exists($relationship, $this->relationships)) {
443 if (!$alias && $join && $relationship && !empty($join->adjusted) && $table != $join->table) {
444 if ($relationship == $this->view->storage->get('base_table')) {
448 $alias = $relationship . '_' . $table;
452 // Check this again to make sure we don't blow up existing aliases for already
454 if (isset($this->tableQueue[$alias])) {
458 $alias = $this->markTable($table, $relationship, $alias);
460 // If no alias is specified, give it the default.
461 if (!isset($alias)) {
462 $alias = $this->tables[$relationship][$table]['alias'] . $this->tables[$relationship][$table]['count'];
465 // If this is a relationship based table, add a marker with
466 // the relationship as a primary table for the alias.
467 if ($table != $alias) {
468 $this->markTable($alias, $this->view->storage->get('base_table'), $alias);
471 // If no join is specified, pull it from the table data.
473 $join = $this->getJoinData($table, $this->relationships[$relationship]['base']);
478 $join = $this->adjustJoin($join, $relationship);
481 $this->tableQueue[$alias] = [
483 'num' => $this->tables[$relationship][$table]['count'],
486 'relationship' => $relationship,
492 protected function markTable($table, $relationship, $alias) {
493 // Mark that this table has been added.
494 if (empty($this->tables[$relationship][$table])) {
495 if (!isset($alias)) {
497 if ($relationship != $this->view->storage->get('base_table')) {
498 // double underscore will help prevent accidental name
500 $alias = $relationship . '__';
504 $this->tables[$relationship][$table] = [
510 $this->tables[$relationship][$table]['count']++;
517 * Ensure a table exists in the queue; if it already exists it won't
518 * do anything, but if it doesn't it will add the table queue. It will ensure
519 * a path leads back to the relationship table.
522 * The unaliased name of the table to ensure.
523 * @param $relationship
524 * The relationship to ensure the table links to. Each relationship will
525 * get a unique instance of the table being added. If not specified,
526 * will be the primary table.
527 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
528 * A Join object (or derived object) to join the alias in.
531 * The alias used to refer to this specific table, or NULL if the table
534 public function ensureTable($table, $relationship = NULL, JoinPluginBase $join = NULL) {
535 // ensure a relationship
536 if (empty($relationship)) {
537 $relationship = $this->view->storage->get('base_table');
540 // If the relationship is the primary table, this actually be a relationship
541 // link back from an alias. We store all aliases along with the primary table
542 // to detect this state, because eventually it'll hit a table we already
543 // have and that's when we want to stop.
544 if ($relationship == $this->view->storage->get('base_table') && !empty($this->tables[$relationship][$table])) {
545 return $this->tables[$relationship][$table]['alias'];
548 if (!array_key_exists($relationship, $this->relationships)) {
552 if ($table == $this->relationships[$relationship]['base']) {
553 return $relationship;
556 // If we do not have join info, fetch it.
558 $join = $this->getJoinData($table, $this->relationships[$relationship]['base']);
561 // If it can't be fetched, this won't work.
566 // Adjust this join for the relationship, which will ensure that the 'base'
567 // table it links to is correct. Tables adjoined to a relationship
568 // join to a link point, not the base table.
569 $join = $this->adjustJoin($join, $relationship);
571 if ($this->ensurePath($table, $relationship, $join)) {
572 // Attempt to eliminate redundant joins. If this table's
573 // relationship and join exactly matches an existing table's
574 // relationship and join, we do not have to join to it again;
575 // just return the existing table's alias. See
576 // http://groups.drupal.org/node/11288 for details.
578 // This can be done safely here but not lower down in
579 // queueTable(), because queueTable() is also used by
580 // addTable() which requires the ability to intentionally add
581 // the same table with the same join multiple times. For
582 // example, a view that filters on 3 taxonomy terms using AND
583 // needs to join taxonomy_term_data 3 times with the same join.
585 // scan through the table queue to see if a matching join and
586 // relationship exists. If so, use it instead of this join.
588 // TODO: Scanning through $this->tableQueue results in an
589 // O(N^2) algorithm, and this code runs every time the view is
590 // instantiated (Views 2 does not currently cache queries).
591 // There are a couple possible "improvements" but we should do
592 // some performance testing before picking one.
593 foreach ($this->tableQueue as $queued_table) {
594 // In PHP 4 and 5, the == operation returns TRUE for two objects
595 // if they are instances of the same class and have the same
596 // attributes and values.
597 if ($queued_table['relationship'] == $relationship && $queued_table['join'] == $join) {
598 return $queued_table['alias'];
602 return $this->queueTable($table, $relationship, $join);
607 * Make sure that the specified table can be properly linked to the primary
608 * table in the JOINs. This function uses recursion. If the tables
609 * needed to complete the path back to the primary table are not in the
610 * query they will be added, but additional copies will NOT be added
611 * if the table is already there.
613 protected function ensurePath($table, $relationship = NULL, $join = NULL, $traced = [], $add = []) {
614 if (!isset($relationship)) {
615 $relationship = $this->view->storage->get('base_table');
618 if (!array_key_exists($relationship, $this->relationships)) {
622 // If we do not have join info, fetch it.
624 $join = $this->getJoinData($table, $this->relationships[$relationship]['base']);
627 // If it can't be fetched, this won't work.
632 // Does a table along this path exist?
633 if (isset($this->tables[$relationship][$table]) ||
634 ($join && $join->leftTable == $relationship) ||
635 ($join && $join->leftTable == $this->relationships[$relationship]['table'])) {
637 // Make sure that we're linking to the correct table for our relationship.
638 foreach (array_reverse($add) as $table => $path_join) {
639 $this->queueTable($table, $relationship, $this->adjustJoin($path_join, $relationship));
644 // Have we been this way?
645 if (isset($traced[$join->leftTable])) {
646 // We looped. Broken.
650 // Do we have to add this table?
651 $left_join = $this->getJoinData($join->leftTable, $this->relationships[$relationship]['base']);
652 if (!isset($this->tables[$relationship][$join->leftTable])) {
653 $add[$join->leftTable] = $left_join;
657 $traced[$join->leftTable] = TRUE;
658 return $this->ensurePath($join->leftTable, $relationship, $left_join, $traced, $add);
662 * Fix a join to adhere to the proper relationship; the left table can vary
663 * based upon what relationship items are joined in on.
665 protected function adjustJoin($join, $relationship) {
666 if (!empty($join->adjusted)) {
670 if (empty($relationship) || empty($this->relationships[$relationship])) {
674 // Adjusts the left table for our relationship.
675 if ($relationship != $this->view->storage->get('base_table')) {
676 // If we're linking to the primary table, the relationship to use will
677 // be the prior relationship. Unless it's a direct link.
679 // Safety! Don't modify an original here.
682 // Do we need to try to ensure a path?
683 if ($join->leftTable != $this->relationships[$relationship]['table'] &&
684 $join->leftTable != $this->relationships[$relationship]['base'] &&
685 !isset($this->tables[$relationship][$join->leftTable]['alias'])) {
686 $this->ensureTable($join->leftTable, $relationship);
689 // First, if this is our link point/anchor table, just use the relationship
690 if ($join->leftTable == $this->relationships[$relationship]['table']) {
691 $join->leftTable = $relationship;
693 // then, try the base alias.
694 elseif (isset($this->tables[$relationship][$join->leftTable]['alias'])) {
695 $join->leftTable = $this->tables[$relationship][$join->leftTable]['alias'];
697 // But if we're already looking at an alias, use that instead.
698 elseif (isset($this->tableQueue[$relationship]['alias'])) {
699 $join->leftTable = $this->tableQueue[$relationship]['alias'];
703 $join->adjusted = TRUE;
708 * Retrieve join data from the larger join data cache.
711 * The table to get the join information for.
713 * The path we're following to get this join.
715 * @return \Drupal\views\Plugin\views\join\JoinPluginBase
716 * A Join object or child object, if one exists.
718 public function getJoinData($table, $base_table) {
719 // Check to see if we're linking to a known alias. If so, get the real
720 // table's data instead.
721 if (!empty($this->tableQueue[$table])) {
722 $table = $this->tableQueue[$table]['table'];
724 return HandlerBase::getTableJoin($table, $base_table);
728 * Get the information associated with a table.
730 * If you need the alias of a table with a particular relationship, use
733 public function getTableInfo($table) {
734 if (!empty($this->tableQueue[$table])) {
735 return $this->tableQueue[$table];
738 // In rare cases we might *only* have aliased versions of the table.
739 if (!empty($this->tables[$this->view->storage->get('base_table')][$table])) {
740 $alias = $this->tables[$this->view->storage->get('base_table')][$table]['alias'];
741 if (!empty($this->tableQueue[$alias])) {
742 return $this->tableQueue[$alias];
748 * Add a field to the query table, possibly with an alias. This will
749 * automatically call ensureTable to make sure the required table
750 * exists, *unless* $table is unset.
753 * The table this field is attached to. If NULL, it is assumed this will
754 * be a formula; otherwise, ensureTable is used to make sure the
757 * The name of the field to add. This may be a real field or a formula.
759 * The alias to create. If not specified, the alias will be $table_$field
760 * unless $table is NULL. When adding formulae, it is recommended that an
763 * An array of parameters additional to the field that will control items
764 * such as aggregation functions and DISTINCT. Some values that are
766 * - function: An aggregation function to apply, such as SUM.
767 * - aggregate: Set to TRUE to indicate that this value should be
768 * aggregated in a GROUP BY.
771 * The name that this field can be referred to as. Usually this is the alias.
773 public function addField($table, $field, $alias = '', $params = []) {
774 // We check for this specifically because it gets a special alias.
775 if ($table == $this->view->storage->get('base_table') && $field == $this->view->storage->get('base_field') && empty($alias)) {
776 $alias = $this->view->storage->get('base_field');
779 if ($table && empty($this->tableQueue[$table])) {
780 $this->ensureTable($table);
783 if (!$alias && $table) {
784 $alias = $table . '_' . $field;
787 // Make sure an alias is assigned
788 $alias = $alias ? $alias : $field;
790 // PostgreSQL truncates aliases to 63 characters:
791 // https://www.drupal.org/node/571548.
793 // We limit the length of the original alias up to 60 characters
794 // to get a unique alias later if its have duplicates
795 $alias = strtolower(substr($alias, 0, 60));
797 // Create a field info array.
804 // Test to see if the field is actually the same or not. Due to
805 // differing parameters changing the aggregation function, we need
806 // to do some automatic alias collision detection:
809 while (!empty($this->fields[$alias]) && $this->fields[$alias] != $field_info) {
810 $field_info['alias'] = $alias = $base . '_' . ++$counter;
813 if (empty($this->fields[$alias])) {
814 $this->fields[$alias] = $field_info;
817 // Keep track of all aliases used.
818 $this->fieldAliases[$table][$field] = $alias;
824 * Remove all fields that may've been added; primarily used for summary
825 * mode where we're changing the query because we didn't get data we needed.
827 public function clearFields() {
832 * Add a simple WHERE clause to the query. The caller is responsible for
833 * ensuring that all fields are fully qualified (TABLE.FIELD) and that
834 * the table already exists in the query.
836 * The $field, $value and $operator arguments can also be passed in with a
837 * single DatabaseCondition object, like this:
839 * $this->query->addWhere(
840 * $this->options['group'],
841 * (new Condition('OR'))
842 * ->condition($field, $value, 'NOT IN')
843 * ->condition($field, $value, 'IS NULL')
848 * The WHERE group to add these to; groups are used to create AND/OR
849 * sections. Groups cannot be nested. Use 0 as the default group.
850 * If the group does not yet exist it will be created as an AND group.
852 * The name of the field to check.
854 * The value to test the field against. In most cases, this is a scalar. For more
855 * complex options, it is an array. The meaning of each element in the array is
856 * dependent on the $operator.
858 * The comparison operator, such as =, <, or >=. It also accepts more
859 * complex options such as IN, LIKE, LIKE BINARY, or BETWEEN. Defaults to =.
860 * If $field is a string you have to use 'formula' here.
862 * @see \Drupal\Core\Database\Query\ConditionInterface::condition()
863 * @see \Drupal\Core\Database\Query\Condition
865 public function addWhere($group, $field, $value = NULL, $operator = NULL) {
866 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
867 // the default group.
872 // Check for a group.
873 if (!isset($this->where[$group])) {
874 $this->setWhereGroup('AND', $group);
877 $this->where[$group]['conditions'][] = [
880 'operator' => $operator,
885 * Add a complex WHERE clause to the query.
887 * The caller is responsible for ensuring that all fields are fully qualified
888 * (TABLE.FIELD) and that the table already exists in the query.
889 * Internally the dbtng method "where" is used.
892 * The WHERE group to add these to; groups are used to create AND/OR
893 * sections. Groups cannot be nested. Use 0 as the default group.
894 * If the group does not yet exist it will be created as an AND group.
896 * The snippet to check. This can be either a column or
897 * a complex expression like "UPPER(table.field) = 'value'"
899 * An associative array of arguments.
901 * @see QueryConditionInterface::where()
903 public function addWhereExpression($group, $snippet, $args = []) {
904 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
905 // the default group.
910 // Check for a group.
911 if (!isset($this->where[$group])) {
912 $this->setWhereGroup('AND', $group);
915 $this->where[$group]['conditions'][] = [
918 'operator' => 'formula',
923 * Add a complex HAVING clause to the query.
924 * The caller is responsible for ensuring that all fields are fully qualified
925 * (TABLE.FIELD) and that the table and an appropriate GROUP BY already exist in the query.
926 * Internally the dbtng method "having" is used.
929 * The HAVING group to add these to; groups are used to create AND/OR
930 * sections. Groups cannot be nested. Use 0 as the default group.
931 * If the group does not yet exist it will be created as an AND group.
933 * The snippet to check. This can be either a column or
934 * a complex expression like "COUNT(table.field) > 3"
936 * An associative array of arguments.
938 * @see QueryConditionInterface::having()
940 public function addHavingExpression($group, $snippet, $args = []) {
941 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
942 // the default group.
947 // Check for a group.
948 if (!isset($this->having[$group])) {
949 $this->setWhereGroup('AND', $group, 'having');
952 // Add the clause and the args.
953 $this->having[$group]['conditions'][] = [
956 'operator' => 'formula',
961 * Add an ORDER BY clause to the query.
964 * The table this field is part of. If a formula, enter NULL.
965 * If you want to orderby random use "rand" as table and nothing else.
967 * The field or formula to sort on. If already a field, enter NULL
968 * and put in the alias.
970 * Either ASC or DESC.
972 * The alias to add the field as. In SQL, all fields in the order by
973 * must also be in the SELECT portion. If an $alias isn't specified
974 * one will be generated for from the $field; however, if the
975 * $field is a formula, this alias will likely fail.
977 * Any params that should be passed through to the addField.
979 public function addOrderBy($table, $field = NULL, $order = 'ASC', $alias = '', $params = []) {
980 // Only ensure the table if it's not the special random key.
981 // @todo: Maybe it would make sense to just add an addOrderByRand or something similar.
982 if ($table && $table != 'rand') {
983 $this->ensureTable($table);
986 // Only fill out this aliasing if there is a table;
987 // otherwise we assume it is a formula.
988 if (!$alias && $table) {
989 $as = $table . '_' . $field;
996 $as = $this->addField($table, $field, $as, $params);
1001 'direction' => strtoupper($order)
1006 * Add a simple GROUP BY clause to the query. The caller is responsible
1007 * for ensuring that the fields are fully qualified and the table is properly
1010 public function addGroupBy($clause) {
1011 // Only add it if it's not already in there.
1012 if (!in_array($clause, $this->groupby)) {
1013 $this->groupby[] = $clause;
1018 * Returns the alias for the given field added to $table.
1022 * @see \Drupal\views\Plugin\views\query\Sql::addField
1024 protected function getFieldAlias($table_alias, $field) {
1025 return isset($this->fieldAliases[$table_alias][$field]) ? $this->fieldAliases[$table_alias][$field] : FALSE;
1029 * Adds a query tag to the sql object.
1031 * @see SelectQuery::addTag()
1033 public function addTag($tag) {
1034 $this->tags[] = $tag;
1038 * Generates a unique placeholder used in the db query.
1040 public function placeholder($base = 'views') {
1041 static $placeholders = [];
1042 if (!isset($placeholders[$base])) {
1043 $placeholders[$base] = 0;
1047 return ':' . $base . ++$placeholders[$base];
1052 * Construct the "WHERE" or "HAVING" part of the query.
1054 * As views has to wrap the conditions from arguments with AND, a special
1055 * group is wrapped around all conditions. This special group has the ID 0.
1056 * There is other code in filters which makes sure that the group IDs are
1060 * 'where' or 'having'.
1062 protected function buildCondition($where = 'where') {
1063 $has_condition = FALSE;
1064 $has_arguments = FALSE;
1065 $has_filter = FALSE;
1067 $main_group = new Condition('AND');
1068 $filter_group = $this->groupOperator == 'OR' ? new Condition('OR') : new Condition('AND');
1070 foreach ($this->$where as $group => $info) {
1072 if (!empty($info['conditions'])) {
1073 $sub_group = $info['type'] == 'OR' ? new Condition('OR') : new Condition('AND');
1074 foreach ($info['conditions'] as $clause) {
1075 if ($clause['operator'] == 'formula') {
1076 $has_condition = TRUE;
1077 $sub_group->where($clause['field'], $clause['value']);
1080 $has_condition = TRUE;
1081 $sub_group->condition($clause['field'], $clause['value'], $clause['operator']);
1085 // Add the item to the filter group.
1088 $filter_group->condition($sub_group);
1091 $has_arguments = TRUE;
1092 $main_group->condition($sub_group);
1098 $main_group->condition($filter_group);
1101 if (!$has_arguments && $has_condition) {
1102 return $filter_group;
1104 if ($has_arguments && $has_condition) {
1110 * Returns a list of non-aggregates to be added to the "group by" clause.
1112 * Non-aggregates are fields that have no aggregation function (count, sum,
1113 * etc) applied. Since the SQL standard requires all fields to either have
1114 * an aggregation function applied, or to be in the GROUP BY clause, Views
1115 * gathers those fields and adds them to the GROUP BY clause.
1118 * An array of the fieldnames which are non-aggregates.
1120 protected function getNonAggregates() {
1121 $non_aggregates = [];
1122 foreach ($this->fields as $field) {
1124 if (!empty($field['table'])) {
1125 $string .= $field['table'] . '.';
1127 $string .= $field['field'];
1128 $fieldname = (!empty($field['alias']) ? $field['alias'] : $string);
1130 if (!empty($field['count'])) {
1131 // Retained for compatibility.
1132 $field['function'] = 'count';
1135 if (!empty($field['function'])) {
1136 $this->hasAggregate = TRUE;
1138 // This is a formula, using no tables.
1139 elseif (empty($field['table'])) {
1140 $non_aggregates[] = $fieldname;
1142 elseif (empty($field['aggregate'])) {
1143 $non_aggregates[] = $fieldname;
1146 if ($this->getCountOptimized) {
1147 // We only want the first field in this case.
1152 return $non_aggregates;
1156 * Adds fields to the query.
1158 * @param \Drupal\Core\Database\Query\SelectInterface $query
1159 * The drupal query object.
1161 protected function compileFields($query) {
1162 foreach ($this->fields as $field) {
1164 if (!empty($field['table'])) {
1165 $string .= $field['table'] . '.';
1167 $string .= $field['field'];
1168 $fieldname = (!empty($field['alias']) ? $field['alias'] : $string);
1170 if (!empty($field['count'])) {
1171 // Retained for compatibility.
1172 $field['function'] = 'count';
1175 if (!empty($field['function'])) {
1176 $info = $this->getAggregationInfo();
1177 if (!empty($info[$field['function']]['method']) && is_callable([$this, $info[$field['function']]['method']])) {
1178 $string = $this::{$info[$field['function']]['method']}($field['function'], $string);
1179 $placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
1180 $query->addExpression($string, $fieldname, $placeholders);
1183 $this->hasAggregate = TRUE;
1185 // This is a formula, using no tables.
1186 elseif (empty($field['table'])) {
1187 $placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
1188 $query->addExpression($string, $fieldname, $placeholders);
1190 elseif ($this->distinct && !in_array($fieldname, $this->groupby)) {
1191 $query->addField(!empty($field['table']) ? $field['table'] : $this->view->storage->get('base_table'), $field['field'], $fieldname);
1193 elseif (empty($field['aggregate'])) {
1194 $query->addField(!empty($field['table']) ? $field['table'] : $this->view->storage->get('base_table'), $field['field'], $fieldname);
1197 if ($this->getCountOptimized) {
1198 // We only want the first field in this case.
1205 * Generate a query and a countquery from all of the information supplied
1209 * Provide a countquery if this is true, otherwise provide a normal query.
1211 public function query($get_count = FALSE) {
1212 // Check query distinct value.
1213 if (empty($this->noDistinct) && $this->distinct && !empty($this->fields)) {
1214 $base_field_alias = $this->addField($this->view->storage->get('base_table'), $this->view->storage->get('base_field'));
1215 $this->addGroupBy($base_field_alias);
1220 * An optimized count query includes just the base field instead of all the fields.
1221 * Determine of this query qualifies by checking for a groupby or distinct.
1223 if ($get_count && !$this->groupby) {
1224 foreach ($this->fields as $field) {
1225 if (!empty($field['distinct']) || !empty($field['function'])) {
1226 $this->getCountOptimized = FALSE;
1232 $this->getCountOptimized = FALSE;
1234 if (!isset($this->getCountOptimized)) {
1235 $this->getCountOptimized = TRUE;
1239 $target = 'default';
1241 // Detect an external database and set the
1242 if (isset($this->view->base_database)) {
1243 $key = $this->view->base_database;
1246 // Set the replica target if the replica option is set
1247 if (!empty($this->options['replica'])) {
1248 $target = 'replica';
1251 // Go ahead and build the query.
1252 // db_select doesn't support to specify the key, so use getConnection directly.
1253 $query = Database::getConnection($target, $key)
1254 ->select($this->view->storage->get('base_table'), $this->view->storage->get('base_table'), $options)
1256 ->addTag('views_' . $this->view->storage->id());
1258 // Add the tags added to the view itself.
1259 foreach ($this->tags as $tag) {
1260 $query->addTag($tag);
1263 if (!empty($distinct)) {
1267 // Add all the tables to the query via joins. We assume all LEFT joins.
1268 foreach ($this->tableQueue as $table) {
1269 if (is_object($table['join'])) {
1270 $table['join']->buildJoin($query, $table, $this);
1274 // Assemble the groupby clause, if any.
1275 $this->hasAggregate = FALSE;
1276 $non_aggregates = $this->getNonAggregates();
1277 if (count($this->having)) {
1278 $this->hasAggregate = TRUE;
1280 elseif (!$this->hasAggregate) {
1281 // Allow 'GROUP BY' even no aggregation function has been set.
1282 $this->hasAggregate = $this->view->display_handler->getOption('group_by');
1285 if ($this->hasAggregate && (!empty($this->groupby) || !empty($non_aggregates))) {
1286 $groupby = array_unique(array_merge($this->groupby, $non_aggregates));
1289 // Make sure each entity table has the base field added so that the
1290 // entities can be loaded.
1291 $entity_information = $this->getEntityTableInfo();
1292 if ($entity_information) {
1295 // Handle grouping, by retrieving the minimum entity_id.
1297 'function' => 'min',
1301 foreach ($entity_information as $entity_type_id => $info) {
1302 $entity_type = \Drupal::entityManager()->getDefinition($info['entity_type']);
1303 $base_field = !$info['revision'] ? $entity_type->getKey('id') : $entity_type->getKey('revision');
1304 $this->addField($info['alias'], $base_field, '', $params);
1308 // Add all fields to the query.
1309 $this->compileFields($query);
1313 foreach ($groupby as $field) {
1314 // Handle group by of field without table alias to avoid ambiguous
1316 if ($field == $this->view->storage->get('base_field')) {
1317 $field = $this->view->storage->get('base_table') . '.' . $field;
1319 $query->groupBy($field);
1321 if (!empty($this->having) && $condition = $this->buildCondition('having')) {
1322 $query->havingCondition($condition);
1326 if (!$this->getCountOptimized) {
1327 // we only add the orderby if we're not counting.
1328 if ($this->orderby) {
1329 foreach ($this->orderby as $order) {
1330 if ($order['field'] == 'rand_') {
1331 $query->orderRandom();
1334 $query->orderBy($order['field'], $order['direction']);
1340 if (!empty($this->where) && $condition = $this->buildCondition('where')) {
1341 $query->condition($condition);
1344 // Add a query comment.
1345 if (!empty($this->options['query_comment'])) {
1346 $query->comment($this->options['query_comment']);
1349 // Add the query tags.
1350 if (!empty($this->options['query_tags'])) {
1351 foreach ($this->options['query_tags'] as $tag) {
1352 $query->addTag($tag);
1356 // Add all query substitutions as metadata.
1357 $query->addMetaData('views_substitutions', \Drupal::moduleHandler()->invokeAll('views_query_substitutions', [$this->view]));
1363 * Get the arguments attached to the WHERE and HAVING clauses of this query.
1365 public function getWhereArgs() {
1367 foreach ($this->where as $where) {
1368 $args = array_merge($args, $where['args']);
1370 foreach ($this->having as $having) {
1371 $args = array_merge($args, $having['args']);
1377 * Let modules modify the query just prior to finalizing it.
1379 public function alter(ViewExecutable $view) {
1380 \Drupal::moduleHandler()->invokeAll('views_query_alter', [$view, $this]);
1384 * Builds the necessary info to execute the query.
1386 public function build(ViewExecutable $view) {
1387 // Make the query distinct if the option was set.
1388 if (!empty($this->options['distinct'])) {
1389 $this->setDistinct(TRUE);
1392 // Store the view in the object to be able to use it later.
1393 $this->view = $view;
1397 // Let the pager modify the query to add limits.
1398 $view->pager->query();
1400 $view->build_info['query'] = $this->query();
1401 $view->build_info['count_query'] = $this->query(TRUE);
1405 * Executes the query and fills the associated view object with according
1408 * Values to set: $view->result, $view->total_rows, $view->execute_time,
1409 * $view->current_page.
1411 public function execute(ViewExecutable $view) {
1412 $query = $view->build_info['query'];
1413 $count_query = $view->build_info['count_query'];
1415 $query->addMetaData('view', $view);
1416 $count_query->addMetaData('view', $view);
1418 if (empty($this->options['disable_sql_rewrite'])) {
1419 $base_table_data = Views::viewsData()->get($this->view->storage->get('base_table'));
1420 if (isset($base_table_data['table']['base']['access query tag'])) {
1421 $access_tag = $base_table_data['table']['base']['access query tag'];
1422 $query->addTag($access_tag);
1423 $count_query->addTag($access_tag);
1426 if (isset($base_table_data['table']['base']['query metadata'])) {
1427 foreach ($base_table_data['table']['base']['query metadata'] as $key => $value) {
1428 $query->addMetaData($key, $value);
1429 $count_query->addMetaData($key, $value);
1435 $additional_arguments = \Drupal::moduleHandler()->invokeAll('views_query_substitutions', [$view]);
1437 // Count queries must be run through the preExecute() method.
1438 // If not, then hook_query_node_access_alter() may munge the count by
1439 // adding a distinct against an empty query string
1440 // (e.g. COUNT DISTINCT(1) ...) and no pager will return.
1441 // See pager.inc > PagerDefault::execute()
1442 // http://api.drupal.org/api/drupal/includes--pager.inc/function/PagerDefault::execute/7
1443 // See https://www.drupal.org/node/1046170.
1444 $count_query->preExecute();
1446 // Build the count query.
1447 $count_query = $count_query->countQuery();
1449 // Add additional arguments as a fake condition.
1450 // XXX: this doesn't work, because PDO mandates that all bound arguments
1451 // are used on the query. TODO: Find a better way to do this.
1452 if (!empty($additional_arguments)) {
1453 // $query->where('1 = 1', $additional_arguments);
1454 // $count_query->where('1 = 1', $additional_arguments);
1457 $start = microtime(TRUE);
1460 if ($view->pager->useCountQuery() || !empty($view->get_total_rows)) {
1461 $view->pager->executeCountQuery($count_query);
1464 // Let the pager modify the query to add limits.
1465 $view->pager->preExecute($query);
1467 if (!empty($this->limit) || !empty($this->offset)) {
1468 // We can't have an offset without a limit, so provide a very large limit instead.
1469 $limit = intval(!empty($this->limit) ? $this->limit : 999999);
1470 $offset = intval(!empty($this->offset) ? $this->offset : 0);
1471 $query->range($offset, $limit);
1474 $result = $query->execute();
1475 $result->setFetchMode(\PDO::FETCH_CLASS, 'Drupal\views\ResultRow');
1477 // Setup the result row objects.
1478 $view->result = iterator_to_array($result);
1479 array_walk($view->result, function (ResultRow $row, $index) {
1480 $row->index = $index;
1483 $view->pager->postExecute($view->result);
1484 $view->pager->updatePageInfo();
1485 $view->total_rows = $view->pager->getTotalItems();
1487 // Load all entities contained in the results.
1488 $this->loadEntities($view->result);
1490 catch (DatabaseExceptionWrapper $e) {
1492 if (!empty($view->live_preview)) {
1493 drupal_set_message($e->getMessage(), 'error');
1496 throw new DatabaseExceptionWrapper("Exception in {$view->storage->label()}[{$view->storage->id()}]: {$e->getMessage()}");
1502 $start = microtime(TRUE);
1504 $view->execute_time = microtime(TRUE) - $start;
1508 * Loads all entities contained in the passed-in $results.
1510 * If the entity belongs to the base table, then it gets stored in
1511 * $result->_entity. Otherwise, it gets stored in
1512 * $result->_relationship_entities[$relationship_id];
1514 * @param \Drupal\views\ResultRow[] $results
1515 * The result of the SQL query.
1517 public function loadEntities(&$results) {
1518 $entity_information = $this->getEntityTableInfo();
1519 // No entity tables found, nothing else to do here.
1520 if (empty($entity_information)) {
1524 // Extract all entity types from entity_information.
1526 foreach ($entity_information as $info) {
1527 $entity_type = $info['entity_type'];
1528 if (!isset($entity_types[$entity_type])) {
1529 $entity_types[$entity_type] = $this->entityTypeManager->getDefinition($entity_type);
1533 // Assemble a list of entities to load.
1534 $entity_ids_by_type = [];
1535 $revision_ids_by_type = [];
1536 foreach ($entity_information as $info) {
1537 $relationship_id = $info['relationship_id'];
1538 $entity_type = $info['entity_type'];
1539 /** @var \Drupal\Core\Entity\EntityTypeInterface $entity_info */
1540 $entity_info = $entity_types[$entity_type];
1541 $revision = $info['revision'];
1542 $id_key = !$revision ? $entity_info->getKey('id') : $entity_info->getKey('revision');
1543 $id_alias = $this->getFieldAlias($info['alias'], $id_key);
1545 foreach ($results as $index => $result) {
1546 // Store the entity id if it was found.
1547 if (isset($result->{$id_alias}) && $result->{$id_alias} != '') {
1549 $revision_ids_by_type[$entity_type][$index][$relationship_id] = $result->$id_alias;
1552 $entity_ids_by_type[$entity_type][$index][$relationship_id] = $result->$id_alias;
1558 // Load all entities and assign them to the correct result row.
1559 foreach ($entity_ids_by_type as $entity_type => $ids) {
1560 $entity_storage = $this->entityTypeManager->getStorage($entity_type);
1561 $flat_ids = iterator_to_array(new \RecursiveIteratorIterator(new \RecursiveArrayIterator($ids)), FALSE);
1563 $entities = $entity_storage->loadMultiple(array_unique($flat_ids));
1564 $results = $this->assignEntitiesToResult($ids, $entities, $results);
1567 // Now load all revisions.
1568 foreach ($revision_ids_by_type as $entity_type => $revision_ids) {
1569 $entity_storage = $this->entityTypeManager->getStorage($entity_type);
1572 foreach ($revision_ids as $index => $revision_id_by_relationship) {
1573 foreach ($revision_id_by_relationship as $revision => $revision_id) {
1574 // Drupal core currently has no way to load multiple revisions.
1575 $entity = $entity_storage->loadRevision($revision_id);
1576 $entities[$revision_id] = $entity;
1580 $results = $this->assignEntitiesToResult($revision_ids, $entities, $results);
1585 * Sets entities onto the view result row objects.
1587 * This method takes into account the relationship in which the entity was
1588 * needed in the first place.
1590 * @param mixed[][] $ids
1591 * A two dimensional array of identifiers (entity ID / revision ID) keyed by
1593 * @param \Drupal\Core\Entity\EntityInterface[] $entities
1594 * An array of entities keyed by their identified (entity ID / revision ID).
1595 * @param \Drupal\views\ResultRow[] $results
1596 * The entire views result.
1598 * @return \Drupal\views\ResultRow[]
1599 * The changed views results.
1601 protected function assignEntitiesToResult($ids, array $entities, array $results) {
1602 foreach ($ids as $index => $relationships) {
1603 foreach ($relationships as $relationship_id => $id) {
1604 if (isset($entities[$id])) {
1605 $entity = $entities[$id];
1611 if ($relationship_id == 'none') {
1612 $results[$index]->_entity = $entity;
1615 $results[$index]->_relationship_entities[$relationship_id] = $entity;
1625 public function getCacheTags() {
1627 // Add cache tags for each row, if there is an entity associated with it.
1628 if (!$this->hasAggregate) {
1629 foreach ($this->getAllEntities() as $entity) {
1630 $tags = Cache::mergeTags($entity->getCacheTags(), $tags);
1640 public function getCacheMaxAge() {
1641 $max_age = parent::getCacheMaxAge();
1642 foreach ($this->getAllEntities() as $entity) {
1643 $max_age = Cache::mergeMaxAges($max_age, $entity->getCacheMaxAge());
1650 * Gets all the involved entities of the view.
1652 * @return \Drupal\Core\Entity\EntityInterface[]
1654 protected function getAllEntities() {
1656 foreach ($this->view->result as $row) {
1657 if ($row->_entity) {
1658 $entities[] = $row->_entity;
1660 foreach ($row->_relationship_entities as $entity) {
1661 $entities[] = $entity;
1668 public function addSignature(ViewExecutable $view) {
1669 $view->query->addField(NULL, "'" . $view->storage->id() . ':' . $view->current_display . "'", 'view_name');
1672 public function getAggregationInfo() {
1673 // @todo -- need a way to get database specific and customized aggregation
1674 // functions into here.
1677 'title' => $this->t('Group results together'),
1678 'is aggregate' => FALSE,
1681 'title' => $this->t('Count'),
1682 'method' => 'aggregationMethodSimple',
1684 'argument' => 'groupby_numeric',
1685 'field' => 'numeric',
1686 'filter' => 'groupby_numeric',
1687 'sort' => 'groupby_numeric',
1690 'count_distinct' => [
1691 'title' => $this->t('Count DISTINCT'),
1692 'method' => 'aggregationMethodDistinct',
1694 'argument' => 'groupby_numeric',
1695 'field' => 'numeric',
1696 'filter' => 'groupby_numeric',
1697 'sort' => 'groupby_numeric',
1701 'title' => $this->t('Sum'),
1702 'method' => 'aggregationMethodSimple',
1704 'argument' => 'groupby_numeric',
1705 'field' => 'numeric',
1706 'filter' => 'groupby_numeric',
1707 'sort' => 'groupby_numeric',
1711 'title' => $this->t('Average'),
1712 'method' => 'aggregationMethodSimple',
1714 'argument' => 'groupby_numeric',
1715 'field' => 'numeric',
1716 'filter' => 'groupby_numeric',
1717 'sort' => 'groupby_numeric',
1721 'title' => $this->t('Minimum'),
1722 'method' => 'aggregationMethodSimple',
1724 'argument' => 'groupby_numeric',
1725 'field' => 'numeric',
1726 'filter' => 'groupby_numeric',
1727 'sort' => 'groupby_numeric',
1731 'title' => $this->t('Maximum'),
1732 'method' => 'aggregationMethodSimple',
1734 'argument' => 'groupby_numeric',
1735 'field' => 'numeric',
1736 'filter' => 'groupby_numeric',
1737 'sort' => 'groupby_numeric',
1741 'title' => $this->t('Standard deviation'),
1742 'method' => 'aggregationMethodSimple',
1744 'argument' => 'groupby_numeric',
1745 'field' => 'numeric',
1746 'filter' => 'groupby_numeric',
1747 'sort' => 'groupby_numeric',
1753 public function aggregationMethodSimple($group_type, $field) {
1754 return strtoupper($group_type) . '(' . $field . ')';
1757 public function aggregationMethodDistinct($group_type, $field) {
1758 $group_type = str_replace('_distinct', '', $group_type);
1759 return strtoupper($group_type) . '(DISTINCT ' . $field . ')';
1765 public function getDateField($field) {
1766 $db_type = Database::getConnection()->databaseType();
1767 $offset = $this->setupTimezone();
1768 if (isset($offset) && !is_numeric($offset)) {
1769 $dtz = new \DateTimeZone($offset);
1770 $dt = new \DateTime('now', $dtz);
1771 $offset_seconds = $dtz->getOffset($dt);
1776 $field = "DATE_ADD('19700101', INTERVAL $field SECOND)";
1777 if (!empty($offset)) {
1778 $field = "($field + INTERVAL $offset_seconds SECOND)";
1782 $field = "TO_TIMESTAMP($field)";
1783 if (!empty($offset)) {
1784 $field = "($field + INTERVAL '$offset_seconds SECONDS')";
1788 if (!empty($offset)) {
1789 $field = "($field + $offset_seconds)";
1800 public function setupTimezone() {
1801 $timezone = drupal_get_user_timezone();
1803 // set up the database timezone
1804 $db_type = Database::getConnection()->databaseType();
1805 if (in_array($db_type, ['mysql', 'pgsql'])) {
1807 static $already_set = FALSE;
1808 if (!$already_set) {
1809 if ($db_type == 'pgsql') {
1810 Database::getConnection()->query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");
1812 elseif ($db_type == 'mysql') {
1813 Database::getConnection()->query("SET @@session.time_zone = '$offset'");
1816 $already_set = TRUE;
1826 public function getDateFormat($field, $format, $string_date = FALSE) {
1827 $db_type = Database::getConnection()->databaseType();
1848 $format = strtr($format, $replace);
1849 return "DATE_FORMAT($field, '$format')";
1856 // No format for Numeric representation of a month, without leading
1863 // No format for Day of the month without leading zeros.
1872 $format = strtr($format, $replace);
1873 if (!$string_date) {
1874 return "TO_CHAR($field, '$format')";
1876 // In order to allow for partials (eg, only the year), transform to a
1877 // date, back to a string again.
1878 return "TO_CHAR(TO_TIMESTAMP($field, 'YYYY-MM-DD HH24:MI:SS'), '$format')";
1882 // No format for 2 digit year number.
1884 // No format for 3 letter month name.
1887 // No format for month number without leading zeros.
1889 // No format for full month name.
1891 // No format for 3 letter day name.
1894 // No format for full day name.
1896 // no format for day of month number without leading zeros.
1900 // No format for 12 hour hour with leading zeros.
1904 // No format for AM/PM.
1907 $format = strtr($format, $replace);
1909 // Don't use the 'unixepoch' flag for string date comparisons.
1910 $unixepoch = $string_date ? '' : ", 'unixepoch'";
1912 // SQLite does not have a ISO week substitution string, so it needs
1913 // special handling.
1914 // @see http://wikipedia.org/wiki/ISO_week_date#Calculation
1915 // @see http://stackoverflow.com/a/15511864/1499564
1916 if ($format === '%W') {
1917 $expression = "((strftime('%j', date(strftime('%Y-%m-%d', $field" . $unixepoch . "), '-3 days', 'weekday 4')) - 1) / 7 + 1)";
1920 $expression = "strftime('$format', $field" . $unixepoch . ")";
1922 // The expression yields a string, but the comparison value is an
1923 // integer in case the comparison value is a float, integer, or numeric.
1924 // All of the above SQLite format tokens only produce integers. However,
1925 // the given $format may contain 'Y-m-d', which results in a string.
1926 // @see \Drupal\Core\Database\Driver\sqlite\Connection::expandArguments()
1927 // @see http://www.sqlite.org/lang_datefunc.html
1928 // @see http://www.sqlite.org/lang_expr.html#castexpr
1929 if (preg_match('/^(?:%\w)+$/', $format)) {
1930 $expression = "CAST($expression AS NUMERIC)";