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\Entity\EntityTypeManagerInterface;
9 use Drupal\Core\Form\FormStateInterface;
10 use Drupal\views\Plugin\views\display\DisplayPluginBase;
11 use Drupal\Core\Database\DatabaseExceptionWrapper;
12 use Drupal\views\Plugin\views\join\JoinPluginBase;
13 use Drupal\views\Plugin\views\HandlerBase;
14 use Drupal\views\ResultRow;
15 use Drupal\views\ViewExecutable;
16 use Drupal\views\Views;
17 use Symfony\Component\DependencyInjection\ContainerInterface;
20 * Views query plugin for an SQL query.
22 * @ingroup views_query_plugins
26 * title = @Translation("SQL Query"),
27 * help = @Translation("Query will be generated and run using the Drupal database API.")
30 class Sql extends QueryPluginBase {
33 * A list of tables in the order they should be added, keyed by alias.
35 protected $tableQueue = [];
38 * Holds an array of tables and counts added so that we can create aliases
43 * Holds an array of relationships, which are aliases of the primary
44 * table that represent different ways to join the same table in.
46 public $relationships = [];
49 * An array of sections of the WHERE query. Each section is in itself
50 * an array of pieces and a flag as to whether or not it should be AND
55 * An array of sections of the HAVING query. Each section is in itself
56 * an array of pieces and a flag as to whether or not it should be AND
61 * The default operator to use when connecting the WHERE groups. May be
64 protected $groupOperator = 'AND';
67 * A simple array of order by clauses.
72 * A simple array of group by clauses.
83 * A flag as to whether or not to make the primary field distinct.
85 public $distinct = FALSE;
87 protected $hasAggregate = FALSE;
90 * Should this query be optimized for counts, for example no sorts.
92 protected $getCountOptimized = NULL;
95 * An array mapping table aliases and field names to field aliases.
97 protected $fieldAliases = [];
100 * Query tags which will be passed over to the dbtng query object.
105 * Is the view marked as not distinct.
109 protected $noDistinct;
112 * The entity type manager.
114 * @var \Drupal\Core\Entity\EntityTypeManagerInterface
116 protected $entityTypeManager;
119 * Constructs a Sql object.
121 * @param array $configuration
122 * A configuration array containing information about the plugin instance.
123 * @param string $plugin_id
124 * The plugin_id for the plugin instance.
125 * @param mixed $plugin_definition
126 * The plugin implementation definition.
127 * @param \Drupal\Core\Entity\EntityTypeManagerInterface $entity_type_manager
128 * The entity type manager.
130 public function __construct(array $configuration, $plugin_id, $plugin_definition, EntityTypeManagerInterface $entity_type_manager) {
131 parent::__construct($configuration, $plugin_id, $plugin_definition);
133 $this->entityTypeManager = $entity_type_manager;
136 public static function create(ContainerInterface $container, array $configuration, $plugin_id, $plugin_definition) {
141 $container->get('entity_type.manager')
148 public function init(ViewExecutable $view, DisplayPluginBase $display, array &$options = NULL) {
149 parent::init($view, $display, $options);
151 $base_table = $this->view->storage->get('base_table');
152 $base_field = $this->view->storage->get('base_field');
153 $this->relationships[$base_table] = [
155 'table' => $base_table,
156 'alias' => $base_table,
157 'base' => $base_table
160 // init the table queue with our primary table.
161 $this->tableQueue[$base_table] = [
162 'alias' => $base_table,
163 'table' => $base_table,
164 'relationship' => $base_table,
168 // init the tables with our primary table
169 $this->tables[$base_table][$base_table] = [
171 'alias' => $base_table,
174 $this->count_field = [
175 'table' => $base_table,
176 'field' => $base_field,
177 'alias' => $base_field,
183 * Set the view to be distinct (per base field).
186 * Should the view be distincted.
188 protected function setDistinct($value = TRUE) {
189 if (!(isset($this->noDistinct) && $value)) {
190 $this->distinct = $value;
195 * Set what field the query will count() on for paging.
197 public function setCountField($table, $field, $alias = NULL) {
199 $alias = $table . '_' . $field;
201 $this->count_field = [
209 protected function defineOptions() {
210 $options = parent::defineOptions();
211 $options['disable_sql_rewrite'] = [
214 $options['distinct'] = [
217 $options['replica'] = [
220 $options['query_comment'] = [
223 $options['query_tags'] = [
231 * Add settings for the ui.
233 public function buildOptionsForm(&$form, FormStateInterface $form_state) {
234 parent::buildOptionsForm($form, $form_state);
236 $form['disable_sql_rewrite'] = [
237 '#title' => $this->t('Disable SQL rewriting'),
238 '#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.'),
239 '#type' => 'checkbox',
240 '#default_value' => !empty($this->options['disable_sql_rewrite']),
241 '#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>',
243 $form['distinct'] = [
244 '#type' => 'checkbox',
245 '#title' => $this->t('Distinct'),
246 '#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.'),
247 '#default_value' => !empty($this->options['distinct']),
250 '#type' => 'checkbox',
251 '#title' => $this->t('Use Secondary Server'),
252 '#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.'),
253 '#default_value' => !empty($this->options['replica']),
255 $form['query_comment'] = [
256 '#type' => 'textfield',
257 '#title' => $this->t('Query Comment'),
258 '#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.'),
259 '#default_value' => $this->options['query_comment'],
261 $form['query_tags'] = [
262 '#type' => 'textfield',
263 '#title' => $this->t('Query Tags'),
264 '#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.'),
265 '#default_value' => implode(', ', $this->options['query_tags']),
266 '#element_validate' => ['views_element_validate_tags'],
271 * Special submit handling.
273 public function submitOptionsForm(&$form, FormStateInterface $form_state) {
274 $element = ['#parents' => ['query', 'options', 'query_tags']];
275 $value = explode(',', NestedArray::getValue($form_state->getValues(), $element['#parents']));
276 $value = array_filter(array_map('trim', $value));
277 $form_state->setValueForElement($element, $value);
281 * A relationship is an alternative endpoint to a series of table
282 * joins. Relationships must be aliases of the primary table and
283 * they must join either to the primary table or to a pre-existing
286 * An example of a relationship would be a nodereference table.
287 * If you have a nodereference named 'book_parent' which links to a
288 * parent node, you could set up a relationship 'node_book_parent'
289 * to 'node'. Then, anything that links to 'node' can link to
290 * 'node_book_parent' instead, thus allowing all properties of
291 * both nodes to be available in the query.
294 * What this relationship will be called, and is also the alias
296 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
297 * A Join object (or derived object) to join the alias in.
299 * The name of the 'base' table this relationship represents; this
300 * tells the join search which path to attempt to use when finding
301 * the path to this relationship.
303 * If this relationship links to something other than the primary
304 * table, specify that table here. For example, a 'track' node
305 * might have a relationship to an 'album' node, which might
306 * have a relationship to an 'artist' node.
308 public function addRelationship($alias, JoinPluginBase $join, $base, $link_point = NULL) {
309 if (empty($link_point)) {
310 $link_point = $this->view->storage->get('base_table');
312 elseif (!array_key_exists($link_point, $this->relationships)) {
316 // Make sure $alias isn't already used; if it, start adding stuff.
317 $alias_base = $alias;
319 while (!empty($this->relationships[$alias])) {
320 $alias = $alias_base . '_' . $count++;
323 // Make sure this join is adjusted for our relationship.
324 if ($link_point && isset($this->relationships[$link_point])) {
325 $join = $this->adjustJoin($join, $link_point);
328 // Add the table directly to the queue to avoid accidentally marking
330 $this->tableQueue[$alias] = [
331 'table' => $join->table,
335 'relationship' => $link_point,
338 $this->relationships[$alias] = [
339 'link' => $link_point,
340 'table' => $join->table,
344 $this->tables[$this->view->storage->get('base_table')][$alias] = [
353 * Add a table to the query, ensuring the path exists.
355 * This function will test to ensure that the path back to the primary
356 * table is valid and exists; if you do not wish for this testing to
357 * occur, use $query->queueTable() instead.
360 * The name of the table to add. It needs to exist in the global table
362 * @param $relationship
363 * An alias of a table; if this is set, the path back to this table will
364 * be tested prior to adding the table, making sure that all intermediary
365 * tables exist and are properly aliased. If set to NULL the path to
366 * the primary table will be ensured. If the path cannot be made, the
367 * table will NOT be added.
368 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
369 * In some join configurations this table may actually join back through
370 * a different method; this is most likely to be used when tracing
371 * a hierarchy path. (node->parent->parent2->parent3). This parameter
372 * will specify how this table joins if it is not the default.
374 * A specific alias to use, rather than the default alias.
377 * The alias of the table; this alias can be used to access information
378 * about the table and should always be used to refer to the table when
379 * adding parts to the query. Or FALSE if the table was not able to be
382 public function addTable($table, $relationship = NULL, JoinPluginBase $join = NULL, $alias = NULL) {
383 if (!$this->ensurePath($table, $relationship, $join)) {
387 if ($join && $relationship) {
388 $join = $this->adjustJoin($join, $relationship);
391 return $this->queueTable($table, $relationship, $join, $alias);
395 * Add a table to the query without ensuring the path.
397 * This is a pretty internal function to Views and addTable() or
398 * ensureTable() should be used instead of this one, unless you are
399 * absolutely sure this is what you want.
402 * The name of the table to add. It needs to exist in the global table
404 * @param $relationship
405 * The primary table alias this table is related to. If not set, the
406 * primary table will be used.
407 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
408 * In some join configurations this table may actually join back through
409 * a different method; this is most likely to be used when tracing
410 * a hierarchy path. (node->parent->parent2->parent3). This parameter
411 * will specify how this table joins if it is not the default.
413 * A specific alias to use, rather than the default alias.
416 * The alias of the table; this alias can be used to access information
417 * about the table and should always be used to refer to the table when
418 * adding parts to the query. Or FALSE if the table was not able to be
421 public function queueTable($table, $relationship = NULL, JoinPluginBase $join = NULL, $alias = NULL) {
422 // If the alias is set, make sure it doesn't already exist.
423 if (isset($this->tableQueue[$alias])) {
427 if (empty($relationship)) {
428 $relationship = $this->view->storage->get('base_table');
431 if (!array_key_exists($relationship, $this->relationships)) {
435 if (!$alias && $join && $relationship && !empty($join->adjusted) && $table != $join->table) {
436 if ($relationship == $this->view->storage->get('base_table')) {
440 $alias = $relationship . '_' . $table;
444 // Check this again to make sure we don't blow up existing aliases for already
446 if (isset($this->tableQueue[$alias])) {
450 $alias = $this->markTable($table, $relationship, $alias);
452 // If no alias is specified, give it the default.
453 if (!isset($alias)) {
454 $alias = $this->tables[$relationship][$table]['alias'] . $this->tables[$relationship][$table]['count'];
457 // If this is a relationship based table, add a marker with
458 // the relationship as a primary table for the alias.
459 if ($table != $alias) {
460 $this->markTable($alias, $this->view->storage->get('base_table'), $alias);
463 // If no join is specified, pull it from the table data.
465 $join = $this->getJoinData($table, $this->relationships[$relationship]['base']);
470 $join = $this->adjustJoin($join, $relationship);
473 $this->tableQueue[$alias] = [
475 'num' => $this->tables[$relationship][$table]['count'],
478 'relationship' => $relationship,
484 protected function markTable($table, $relationship, $alias) {
485 // Mark that this table has been added.
486 if (empty($this->tables[$relationship][$table])) {
487 if (!isset($alias)) {
489 if ($relationship != $this->view->storage->get('base_table')) {
490 // double underscore will help prevent accidental name
492 $alias = $relationship . '__';
496 $this->tables[$relationship][$table] = [
502 $this->tables[$relationship][$table]['count']++;
509 * Ensure a table exists in the queue; if it already exists it won't
510 * do anything, but if it doesn't it will add the table queue. It will ensure
511 * a path leads back to the relationship table.
514 * The unaliased name of the table to ensure.
515 * @param $relationship
516 * The relationship to ensure the table links to. Each relationship will
517 * get a unique instance of the table being added. If not specified,
518 * will be the primary table.
519 * @param \Drupal\views\Plugin\views\join\JoinPluginBase $join
520 * A Join object (or derived object) to join the alias in.
523 * The alias used to refer to this specific table, or NULL if the table
526 public function ensureTable($table, $relationship = NULL, JoinPluginBase $join = NULL) {
527 // ensure a relationship
528 if (empty($relationship)) {
529 $relationship = $this->view->storage->get('base_table');
532 // If the relationship is the primary table, this actually be a relationship
533 // link back from an alias. We store all aliases along with the primary table
534 // to detect this state, because eventually it'll hit a table we already
535 // have and that's when we want to stop.
536 if ($relationship == $this->view->storage->get('base_table') && !empty($this->tables[$relationship][$table])) {
537 return $this->tables[$relationship][$table]['alias'];
540 if (!array_key_exists($relationship, $this->relationships)) {
544 if ($table == $this->relationships[$relationship]['base']) {
545 return $relationship;
548 // If we do not have join info, fetch it.
550 $join = $this->getJoinData($table, $this->relationships[$relationship]['base']);
553 // If it can't be fetched, this won't work.
558 // Adjust this join for the relationship, which will ensure that the 'base'
559 // table it links to is correct. Tables adjoined to a relationship
560 // join to a link point, not the base table.
561 $join = $this->adjustJoin($join, $relationship);
563 if ($this->ensurePath($table, $relationship, $join)) {
564 // Attempt to eliminate redundant joins. If this table's
565 // relationship and join exactly matches an existing table's
566 // relationship and join, we do not have to join to it again;
567 // just return the existing table's alias. See
568 // http://groups.drupal.org/node/11288 for details.
570 // This can be done safely here but not lower down in
571 // queueTable(), because queueTable() is also used by
572 // addTable() which requires the ability to intentionally add
573 // the same table with the same join multiple times. For
574 // example, a view that filters on 3 taxonomy terms using AND
575 // needs to join taxonomy_term_data 3 times with the same join.
577 // scan through the table queue to see if a matching join and
578 // relationship exists. If so, use it instead of this join.
580 // TODO: Scanning through $this->tableQueue results in an
581 // O(N^2) algorithm, and this code runs every time the view is
582 // instantiated (Views 2 does not currently cache queries).
583 // There are a couple possible "improvements" but we should do
584 // some performance testing before picking one.
585 foreach ($this->tableQueue as $queued_table) {
586 // In PHP 4 and 5, the == operation returns TRUE for two objects
587 // if they are instances of the same class and have the same
588 // attributes and values.
589 if ($queued_table['relationship'] == $relationship && $queued_table['join'] == $join) {
590 return $queued_table['alias'];
594 return $this->queueTable($table, $relationship, $join);
599 * Make sure that the specified table can be properly linked to the primary
600 * table in the JOINs. This function uses recursion. If the tables
601 * needed to complete the path back to the primary table are not in the
602 * query they will be added, but additional copies will NOT be added
603 * if the table is already there.
605 protected function ensurePath($table, $relationship = NULL, $join = NULL, $traced = [], $add = []) {
606 if (!isset($relationship)) {
607 $relationship = $this->view->storage->get('base_table');
610 if (!array_key_exists($relationship, $this->relationships)) {
614 // If we do not have join info, fetch it.
616 $join = $this->getJoinData($table, $this->relationships[$relationship]['base']);
619 // If it can't be fetched, this won't work.
624 // Does a table along this path exist?
625 if (isset($this->tables[$relationship][$table]) ||
626 ($join && $join->leftTable == $relationship) ||
627 ($join && $join->leftTable == $this->relationships[$relationship]['table'])) {
629 // Make sure that we're linking to the correct table for our relationship.
630 foreach (array_reverse($add) as $table => $path_join) {
631 $this->queueTable($table, $relationship, $this->adjustJoin($path_join, $relationship));
636 // Have we been this way?
637 if (isset($traced[$join->leftTable])) {
638 // We looped. Broken.
642 // Do we have to add this table?
643 $left_join = $this->getJoinData($join->leftTable, $this->relationships[$relationship]['base']);
644 if (!isset($this->tables[$relationship][$join->leftTable])) {
645 $add[$join->leftTable] = $left_join;
649 $traced[$join->leftTable] = TRUE;
650 return $this->ensurePath($join->leftTable, $relationship, $left_join, $traced, $add);
654 * Fix a join to adhere to the proper relationship; the left table can vary
655 * based upon what relationship items are joined in on.
657 protected function adjustJoin($join, $relationship) {
658 if (!empty($join->adjusted)) {
662 if (empty($relationship) || empty($this->relationships[$relationship])) {
666 // Adjusts the left table for our relationship.
667 if ($relationship != $this->view->storage->get('base_table')) {
668 // If we're linking to the primary table, the relationship to use will
669 // be the prior relationship. Unless it's a direct link.
671 // Safety! Don't modify an original here.
674 // Do we need to try to ensure a path?
675 if ($join->leftTable != $this->relationships[$relationship]['table'] &&
676 $join->leftTable != $this->relationships[$relationship]['base'] &&
677 !isset($this->tables[$relationship][$join->leftTable]['alias'])) {
678 $this->ensureTable($join->leftTable, $relationship);
681 // First, if this is our link point/anchor table, just use the relationship
682 if ($join->leftTable == $this->relationships[$relationship]['table']) {
683 $join->leftTable = $relationship;
685 // then, try the base alias.
686 elseif (isset($this->tables[$relationship][$join->leftTable]['alias'])) {
687 $join->leftTable = $this->tables[$relationship][$join->leftTable]['alias'];
689 // But if we're already looking at an alias, use that instead.
690 elseif (isset($this->tableQueue[$relationship]['alias'])) {
691 $join->leftTable = $this->tableQueue[$relationship]['alias'];
695 $join->adjusted = TRUE;
700 * Retrieve join data from the larger join data cache.
703 * The table to get the join information for.
705 * The path we're following to get this join.
707 * @return \Drupal\views\Plugin\views\join\JoinPluginBase
708 * A Join object or child object, if one exists.
710 public function getJoinData($table, $base_table) {
711 // Check to see if we're linking to a known alias. If so, get the real
712 // table's data instead.
713 if (!empty($this->tableQueue[$table])) {
714 $table = $this->tableQueue[$table]['table'];
716 return HandlerBase::getTableJoin($table, $base_table);
720 * Get the information associated with a table.
722 * If you need the alias of a table with a particular relationship, use
725 public function getTableInfo($table) {
726 if (!empty($this->tableQueue[$table])) {
727 return $this->tableQueue[$table];
730 // In rare cases we might *only* have aliased versions of the table.
731 if (!empty($this->tables[$this->view->storage->get('base_table')][$table])) {
732 $alias = $this->tables[$this->view->storage->get('base_table')][$table]['alias'];
733 if (!empty($this->tableQueue[$alias])) {
734 return $this->tableQueue[$alias];
740 * Add a field to the query table, possibly with an alias. This will
741 * automatically call ensureTable to make sure the required table
742 * exists, *unless* $table is unset.
745 * The table this field is attached to. If NULL, it is assumed this will
746 * be a formula; otherwise, ensureTable is used to make sure the
749 * The name of the field to add. This may be a real field or a formula.
751 * The alias to create. If not specified, the alias will be $table_$field
752 * unless $table is NULL. When adding formulae, it is recommended that an
755 * An array of parameters additional to the field that will control items
756 * such as aggregation functions and DISTINCT. Some values that are
758 * - function: An aggregation function to apply, such as SUM.
759 * - aggregate: Set to TRUE to indicate that this value should be
760 * aggregated in a GROUP BY.
763 * The name that this field can be referred to as. Usually this is the alias.
765 public function addField($table, $field, $alias = '', $params = []) {
766 // We check for this specifically because it gets a special alias.
767 if ($table == $this->view->storage->get('base_table') && $field == $this->view->storage->get('base_field') && empty($alias)) {
768 $alias = $this->view->storage->get('base_field');
771 if ($table && empty($this->tableQueue[$table])) {
772 $this->ensureTable($table);
775 if (!$alias && $table) {
776 $alias = $table . '_' . $field;
779 // Make sure an alias is assigned
780 $alias = $alias ? $alias : $field;
782 // PostgreSQL truncates aliases to 63 characters:
783 // https://www.drupal.org/node/571548.
785 // We limit the length of the original alias up to 60 characters
786 // to get a unique alias later if its have duplicates
787 $alias = strtolower(substr($alias, 0, 60));
789 // Create a field info array.
796 // Test to see if the field is actually the same or not. Due to
797 // differing parameters changing the aggregation function, we need
798 // to do some automatic alias collision detection:
801 while (!empty($this->fields[$alias]) && $this->fields[$alias] != $field_info) {
802 $field_info['alias'] = $alias = $base . '_' . ++$counter;
805 if (empty($this->fields[$alias])) {
806 $this->fields[$alias] = $field_info;
809 // Keep track of all aliases used.
810 $this->fieldAliases[$table][$field] = $alias;
816 * Remove all fields that may've been added; primarily used for summary
817 * mode where we're changing the query because we didn't get data we needed.
819 public function clearFields() {
824 * Add a simple WHERE clause to the query. The caller is responsible for
825 * ensuring that all fields are fully qualified (TABLE.FIELD) and that
826 * the table already exists in the query.
828 * The $field, $value and $operator arguments can also be passed in with a
829 * single DatabaseCondition object, like this:
831 * $this->query->addWhere(
832 * $this->options['group'],
834 * ->condition($field, $value, 'NOT IN')
835 * ->condition($field, $value, 'IS NULL')
840 * The WHERE group to add these to; groups are used to create AND/OR
841 * sections. Groups cannot be nested. Use 0 as the default group.
842 * If the group does not yet exist it will be created as an AND group.
844 * The name of the field to check.
846 * The value to test the field against. In most cases, this is a scalar. For more
847 * complex options, it is an array. The meaning of each element in the array is
848 * dependent on the $operator.
850 * The comparison operator, such as =, <, or >=. It also accepts more
851 * complex options such as IN, LIKE, LIKE BINARY, or BETWEEN. Defaults to =.
852 * If $field is a string you have to use 'formula' here.
854 * @see \Drupal\Core\Database\Query\ConditionInterface::condition()
855 * @see \Drupal\Core\Database\Query\Condition
857 public function addWhere($group, $field, $value = NULL, $operator = NULL) {
858 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
859 // the default group.
864 // Check for a group.
865 if (!isset($this->where[$group])) {
866 $this->setWhereGroup('AND', $group);
869 $this->where[$group]['conditions'][] = [
872 'operator' => $operator,
877 * Add a complex WHERE clause to the query.
879 * The caller is responsible for ensuring that all fields are fully qualified
880 * (TABLE.FIELD) and that the table already exists in the query.
881 * Internally the dbtng method "where" is used.
884 * The WHERE group to add these to; groups are used to create AND/OR
885 * sections. Groups cannot be nested. Use 0 as the default group.
886 * If the group does not yet exist it will be created as an AND group.
888 * The snippet to check. This can be either a column or
889 * a complex expression like "UPPER(table.field) = 'value'"
891 * An associative array of arguments.
893 * @see QueryConditionInterface::where()
895 public function addWhereExpression($group, $snippet, $args = []) {
896 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
897 // the default group.
902 // Check for a group.
903 if (!isset($this->where[$group])) {
904 $this->setWhereGroup('AND', $group);
907 $this->where[$group]['conditions'][] = [
910 'operator' => 'formula',
915 * Add a complex HAVING clause to the query.
916 * The caller is responsible for ensuring that all fields are fully qualified
917 * (TABLE.FIELD) and that the table and an appropriate GROUP BY already exist in the query.
918 * Internally the dbtng method "having" is used.
921 * The HAVING group to add these to; groups are used to create AND/OR
922 * sections. Groups cannot be nested. Use 0 as the default group.
923 * If the group does not yet exist it will be created as an AND group.
925 * The snippet to check. This can be either a column or
926 * a complex expression like "COUNT(table.field) > 3"
928 * An associative array of arguments.
930 * @see QueryConditionInterface::having()
932 public function addHavingExpression($group, $snippet, $args = []) {
933 // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
934 // the default group.
939 // Check for a group.
940 if (!isset($this->having[$group])) {
941 $this->setWhereGroup('AND', $group, 'having');
944 // Add the clause and the args.
945 $this->having[$group]['conditions'][] = [
948 'operator' => 'formula',
953 * Add an ORDER BY clause to the query.
956 * The table this field is part of. If a formula, enter NULL.
957 * If you want to orderby random use "rand" as table and nothing else.
959 * The field or formula to sort on. If already a field, enter NULL
960 * and put in the alias.
962 * Either ASC or DESC.
964 * The alias to add the field as. In SQL, all fields in the order by
965 * must also be in the SELECT portion. If an $alias isn't specified
966 * one will be generated for from the $field; however, if the
967 * $field is a formula, this alias will likely fail.
969 * Any params that should be passed through to the addField.
971 public function addOrderBy($table, $field = NULL, $order = 'ASC', $alias = '', $params = []) {
972 // Only ensure the table if it's not the special random key.
973 // @todo: Maybe it would make sense to just add an addOrderByRand or something similar.
974 if ($table && $table != 'rand') {
975 $this->ensureTable($table);
978 // Only fill out this aliasing if there is a table;
979 // otherwise we assume it is a formula.
980 if (!$alias && $table) {
981 $as = $table . '_' . $field;
988 $as = $this->addField($table, $field, $as, $params);
993 'direction' => strtoupper($order)
998 * Add a simple GROUP BY clause to the query. The caller is responsible
999 * for ensuring that the fields are fully qualified and the table is properly
1002 public function addGroupBy($clause) {
1003 // Only add it if it's not already in there.
1004 if (!in_array($clause, $this->groupby)) {
1005 $this->groupby[] = $clause;
1010 * Returns the alias for the given field added to $table.
1014 * @see \Drupal\views\Plugin\views\query\Sql::addField
1016 protected function getFieldAlias($table_alias, $field) {
1017 return isset($this->fieldAliases[$table_alias][$field]) ? $this->fieldAliases[$table_alias][$field] : FALSE;
1021 * Adds a query tag to the sql object.
1023 * @see SelectQuery::addTag()
1025 public function addTag($tag) {
1026 $this->tags[] = $tag;
1030 * Generates a unique placeholder used in the db query.
1032 public function placeholder($base = 'views') {
1033 static $placeholders = [];
1034 if (!isset($placeholders[$base])) {
1035 $placeholders[$base] = 0;
1039 return ':' . $base . ++$placeholders[$base];
1044 * Construct the "WHERE" or "HAVING" part of the query.
1046 * As views has to wrap the conditions from arguments with AND, a special
1047 * group is wrapped around all conditions. This special group has the ID 0.
1048 * There is other code in filters which makes sure that the group IDs are
1052 * 'where' or 'having'.
1054 protected function buildCondition($where = 'where') {
1055 $has_condition = FALSE;
1056 $has_arguments = FALSE;
1057 $has_filter = FALSE;
1059 $main_group = db_and();
1060 $filter_group = $this->groupOperator == 'OR' ? db_or() : db_and();
1062 foreach ($this->$where as $group => $info) {
1064 if (!empty($info['conditions'])) {
1065 $sub_group = $info['type'] == 'OR' ? db_or() : db_and();
1066 foreach ($info['conditions'] as $clause) {
1067 if ($clause['operator'] == 'formula') {
1068 $has_condition = TRUE;
1069 $sub_group->where($clause['field'], $clause['value']);
1072 $has_condition = TRUE;
1073 $sub_group->condition($clause['field'], $clause['value'], $clause['operator']);
1077 // Add the item to the filter group.
1080 $filter_group->condition($sub_group);
1083 $has_arguments = TRUE;
1084 $main_group->condition($sub_group);
1090 $main_group->condition($filter_group);
1093 if (!$has_arguments && $has_condition) {
1094 return $filter_group;
1096 if ($has_arguments && $has_condition) {
1102 * Returns a list of non-aggregates to be added to the "group by" clause.
1104 * Non-aggregates are fields that have no aggregation function (count, sum,
1105 * etc) applied. Since the SQL standard requires all fields to either have
1106 * an aggregation function applied, or to be in the GROUP BY clause, Views
1107 * gathers those fields and adds them to the GROUP BY clause.
1110 * An array of the fieldnames which are non-aggregates.
1112 protected function getNonAggregates() {
1113 $non_aggregates = [];
1114 foreach ($this->fields as $field) {
1116 if (!empty($field['table'])) {
1117 $string .= $field['table'] . '.';
1119 $string .= $field['field'];
1120 $fieldname = (!empty($field['alias']) ? $field['alias'] : $string);
1122 if (!empty($field['count'])) {
1123 // Retained for compatibility.
1124 $field['function'] = 'count';
1127 if (!empty($field['function'])) {
1128 $this->hasAggregate = TRUE;
1130 // This is a formula, using no tables.
1131 elseif (empty($field['table'])) {
1132 $non_aggregates[] = $fieldname;
1134 elseif (empty($field['aggregate'])) {
1135 $non_aggregates[] = $fieldname;
1138 if ($this->getCountOptimized) {
1139 // We only want the first field in this case.
1144 return $non_aggregates;
1148 * Adds fields to the query.
1150 * @param \Drupal\Core\Database\Query\SelectInterface $query
1151 * The drupal query object.
1153 protected function compileFields($query) {
1154 foreach ($this->fields as $field) {
1156 if (!empty($field['table'])) {
1157 $string .= $field['table'] . '.';
1159 $string .= $field['field'];
1160 $fieldname = (!empty($field['alias']) ? $field['alias'] : $string);
1162 if (!empty($field['count'])) {
1163 // Retained for compatibility.
1164 $field['function'] = 'count';
1167 if (!empty($field['function'])) {
1168 $info = $this->getAggregationInfo();
1169 if (!empty($info[$field['function']]['method']) && is_callable([$this, $info[$field['function']]['method']])) {
1170 $string = $this::{$info[$field['function']]['method']}($field['function'], $string);
1171 $placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
1172 $query->addExpression($string, $fieldname, $placeholders);
1175 $this->hasAggregate = TRUE;
1177 // This is a formula, using no tables.
1178 elseif (empty($field['table'])) {
1179 $placeholders = !empty($field['placeholders']) ? $field['placeholders'] : [];
1180 $query->addExpression($string, $fieldname, $placeholders);
1182 elseif ($this->distinct && !in_array($fieldname, $this->groupby)) {
1183 $query->addField(!empty($field['table']) ? $field['table'] : $this->view->storage->get('base_table'), $field['field'], $fieldname);
1185 elseif (empty($field['aggregate'])) {
1186 $query->addField(!empty($field['table']) ? $field['table'] : $this->view->storage->get('base_table'), $field['field'], $fieldname);
1189 if ($this->getCountOptimized) {
1190 // We only want the first field in this case.
1197 * Generate a query and a countquery from all of the information supplied
1201 * Provide a countquery if this is true, otherwise provide a normal query.
1203 public function query($get_count = FALSE) {
1204 // Check query distinct value.
1205 if (empty($this->noDistinct) && $this->distinct && !empty($this->fields)) {
1206 $base_field_alias = $this->addField($this->view->storage->get('base_table'), $this->view->storage->get('base_field'));
1207 $this->addGroupBy($base_field_alias);
1212 * An optimized count query includes just the base field instead of all the fields.
1213 * Determine of this query qualifies by checking for a groupby or distinct.
1215 if ($get_count && !$this->groupby) {
1216 foreach ($this->fields as $field) {
1217 if (!empty($field['distinct']) || !empty($field['function'])) {
1218 $this->getCountOptimized = FALSE;
1224 $this->getCountOptimized = FALSE;
1226 if (!isset($this->getCountOptimized)) {
1227 $this->getCountOptimized = TRUE;
1231 $target = 'default';
1233 // Detect an external database and set the
1234 if (isset($this->view->base_database)) {
1235 $key = $this->view->base_database;
1238 // Set the replica target if the replica option is set
1239 if (!empty($this->options['replica'])) {
1240 $target = 'replica';
1243 // Go ahead and build the query.
1244 // db_select doesn't support to specify the key, so use getConnection directly.
1245 $query = Database::getConnection($target, $key)
1246 ->select($this->view->storage->get('base_table'), $this->view->storage->get('base_table'), $options)
1248 ->addTag('views_' . $this->view->storage->id());
1250 // Add the tags added to the view itself.
1251 foreach ($this->tags as $tag) {
1252 $query->addTag($tag);
1255 if (!empty($distinct)) {
1259 // Add all the tables to the query via joins. We assume all LEFT joins.
1260 foreach ($this->tableQueue as $table) {
1261 if (is_object($table['join'])) {
1262 $table['join']->buildJoin($query, $table, $this);
1266 // Assemble the groupby clause, if any.
1267 $this->hasAggregate = FALSE;
1268 $non_aggregates = $this->getNonAggregates();
1269 if (count($this->having)) {
1270 $this->hasAggregate = TRUE;
1272 elseif (!$this->hasAggregate) {
1273 // Allow 'GROUP BY' even no aggregation function has been set.
1274 $this->hasAggregate = $this->view->display_handler->getOption('group_by');
1277 if ($this->hasAggregate && (!empty($this->groupby) || !empty($non_aggregates))) {
1278 $groupby = array_unique(array_merge($this->groupby, $non_aggregates));
1281 // Make sure each entity table has the base field added so that the
1282 // entities can be loaded.
1283 $entity_information = $this->getEntityTableInfo();
1284 if ($entity_information) {
1287 // Handle grouping, by retrieving the minimum entity_id.
1289 'function' => 'min',
1293 foreach ($entity_information as $entity_type_id => $info) {
1294 $entity_type = \Drupal::entityManager()->getDefinition($info['entity_type']);
1295 $base_field = !$info['revision'] ? $entity_type->getKey('id') : $entity_type->getKey('revision');
1296 $this->addField($info['alias'], $base_field, '', $params);
1300 // Add all fields to the query.
1301 $this->compileFields($query);
1305 foreach ($groupby as $field) {
1306 // Handle group by of field without table alias to avoid ambiguous
1308 if ($field == $this->view->storage->get('base_field')) {
1309 $field = $this->view->storage->get('base_table') . '.' . $field;
1311 $query->groupBy($field);
1313 if (!empty($this->having) && $condition = $this->buildCondition('having')) {
1314 $query->havingCondition($condition);
1318 if (!$this->getCountOptimized) {
1319 // we only add the orderby if we're not counting.
1320 if ($this->orderby) {
1321 foreach ($this->orderby as $order) {
1322 if ($order['field'] == 'rand_') {
1323 $query->orderRandom();
1326 $query->orderBy($order['field'], $order['direction']);
1332 if (!empty($this->where) && $condition = $this->buildCondition('where')) {
1333 $query->condition($condition);
1336 // Add a query comment.
1337 if (!empty($this->options['query_comment'])) {
1338 $query->comment($this->options['query_comment']);
1341 // Add the query tags.
1342 if (!empty($this->options['query_tags'])) {
1343 foreach ($this->options['query_tags'] as $tag) {
1344 $query->addTag($tag);
1348 // Add all query substitutions as metadata.
1349 $query->addMetaData('views_substitutions', \Drupal::moduleHandler()->invokeAll('views_query_substitutions', [$this->view]));
1355 * Get the arguments attached to the WHERE and HAVING clauses of this query.
1357 public function getWhereArgs() {
1359 foreach ($this->where as $where) {
1360 $args = array_merge($args, $where['args']);
1362 foreach ($this->having as $having) {
1363 $args = array_merge($args, $having['args']);
1369 * Let modules modify the query just prior to finalizing it.
1371 public function alter(ViewExecutable $view) {
1372 \Drupal::moduleHandler()->invokeAll('views_query_alter', [$view, $this]);
1376 * Builds the necessary info to execute the query.
1378 public function build(ViewExecutable $view) {
1379 // Make the query distinct if the option was set.
1380 if (!empty($this->options['distinct'])) {
1381 $this->setDistinct(TRUE);
1384 // Store the view in the object to be able to use it later.
1385 $this->view = $view;
1389 // Let the pager modify the query to add limits.
1390 $view->pager->query();
1392 $view->build_info['query'] = $this->query();
1393 $view->build_info['count_query'] = $this->query(TRUE);
1397 * Executes the query and fills the associated view object with according
1400 * Values to set: $view->result, $view->total_rows, $view->execute_time,
1401 * $view->current_page.
1403 public function execute(ViewExecutable $view) {
1404 $query = $view->build_info['query'];
1405 $count_query = $view->build_info['count_query'];
1407 $query->addMetaData('view', $view);
1408 $count_query->addMetaData('view', $view);
1410 if (empty($this->options['disable_sql_rewrite'])) {
1411 $base_table_data = Views::viewsData()->get($this->view->storage->get('base_table'));
1412 if (isset($base_table_data['table']['base']['access query tag'])) {
1413 $access_tag = $base_table_data['table']['base']['access query tag'];
1414 $query->addTag($access_tag);
1415 $count_query->addTag($access_tag);
1418 if (isset($base_table_data['table']['base']['query metadata'])) {
1419 foreach ($base_table_data['table']['base']['query metadata'] as $key => $value) {
1420 $query->addMetaData($key, $value);
1421 $count_query->addMetaData($key, $value);
1427 $additional_arguments = \Drupal::moduleHandler()->invokeAll('views_query_substitutions', [$view]);
1429 // Count queries must be run through the preExecute() method.
1430 // If not, then hook_query_node_access_alter() may munge the count by
1431 // adding a distinct against an empty query string
1432 // (e.g. COUNT DISTINCT(1) ...) and no pager will return.
1433 // See pager.inc > PagerDefault::execute()
1434 // http://api.drupal.org/api/drupal/includes--pager.inc/function/PagerDefault::execute/7
1435 // See https://www.drupal.org/node/1046170.
1436 $count_query->preExecute();
1438 // Build the count query.
1439 $count_query = $count_query->countQuery();
1441 // Add additional arguments as a fake condition.
1442 // XXX: this doesn't work, because PDO mandates that all bound arguments
1443 // are used on the query. TODO: Find a better way to do this.
1444 if (!empty($additional_arguments)) {
1445 // $query->where('1 = 1', $additional_arguments);
1446 // $count_query->where('1 = 1', $additional_arguments);
1449 $start = microtime(TRUE);
1452 if ($view->pager->useCountQuery() || !empty($view->get_total_rows)) {
1453 $view->pager->executeCountQuery($count_query);
1456 // Let the pager modify the query to add limits.
1457 $view->pager->preExecute($query);
1459 if (!empty($this->limit) || !empty($this->offset)) {
1460 // We can't have an offset without a limit, so provide a very large limit instead.
1461 $limit = intval(!empty($this->limit) ? $this->limit : 999999);
1462 $offset = intval(!empty($this->offset) ? $this->offset : 0);
1463 $query->range($offset, $limit);
1466 $result = $query->execute();
1467 $result->setFetchMode(\PDO::FETCH_CLASS, 'Drupal\views\ResultRow');
1469 // Setup the result row objects.
1470 $view->result = iterator_to_array($result);
1471 array_walk($view->result, function(ResultRow $row, $index) {
1472 $row->index = $index;
1475 $view->pager->postExecute($view->result);
1476 $view->pager->updatePageInfo();
1477 $view->total_rows = $view->pager->getTotalItems();
1479 // Load all entities contained in the results.
1480 $this->loadEntities($view->result);
1482 catch (DatabaseExceptionWrapper $e) {
1484 if (!empty($view->live_preview)) {
1485 drupal_set_message($e->getMessage(), 'error');
1488 throw new DatabaseExceptionWrapper("Exception in {$view->storage->label()}[{$view->storage->id()}]: {$e->getMessage()}");
1494 $start = microtime(TRUE);
1496 $view->execute_time = microtime(TRUE) - $start;
1500 * Loads all entities contained in the passed-in $results.
1502 * If the entity belongs to the base table, then it gets stored in
1503 * $result->_entity. Otherwise, it gets stored in
1504 * $result->_relationship_entities[$relationship_id];
1506 * @param \Drupal\views\ResultRow[] $results
1507 * The result of the SQL query.
1509 public function loadEntities(&$results) {
1510 $entity_information = $this->getEntityTableInfo();
1511 // No entity tables found, nothing else to do here.
1512 if (empty($entity_information)) {
1516 // Extract all entity types from entity_information.
1518 foreach ($entity_information as $info) {
1519 $entity_type = $info['entity_type'];
1520 if (!isset($entity_types[$entity_type])) {
1521 $entity_types[$entity_type] = $this->entityTypeManager->getDefinition($entity_type);
1525 // Assemble a list of entities to load.
1526 $entity_ids_by_type = [];
1527 $revision_ids_by_type = [];
1528 foreach ($entity_information as $info) {
1529 $relationship_id = $info['relationship_id'];
1530 $entity_type = $info['entity_type'];
1531 /** @var \Drupal\Core\Entity\EntityTypeInterface $entity_info */
1532 $entity_info = $entity_types[$entity_type];
1533 $revision = $info['revision'];
1534 $id_key = !$revision ? $entity_info->getKey('id') : $entity_info->getKey('revision');
1535 $id_alias = $this->getFieldAlias($info['alias'], $id_key);
1537 foreach ($results as $index => $result) {
1538 // Store the entity id if it was found.
1539 if (isset($result->{$id_alias}) && $result->{$id_alias} != '') {
1541 $revision_ids_by_type[$entity_type][$index][$relationship_id] = $result->$id_alias;
1544 $entity_ids_by_type[$entity_type][$index][$relationship_id] = $result->$id_alias;
1550 // Load all entities and assign them to the correct result row.
1551 foreach ($entity_ids_by_type as $entity_type => $ids) {
1552 $entity_storage = $this->entityTypeManager->getStorage($entity_type);
1553 $flat_ids = iterator_to_array(new \RecursiveIteratorIterator(new \RecursiveArrayIterator($ids)), FALSE);
1555 $entities = $entity_storage->loadMultiple(array_unique($flat_ids));
1556 $results = $this->assignEntitiesToResult($ids, $entities, $results);
1559 // Now load all revisions.
1560 foreach ($revision_ids_by_type as $entity_type => $revision_ids) {
1561 $entity_storage = $this->entityTypeManager->getStorage($entity_type);
1564 foreach ($revision_ids as $index => $revision_id_by_relationship) {
1565 foreach ($revision_id_by_relationship as $revision => $revision_id) {
1566 // Drupal core currently has no way to load multiple revisions.
1567 $entity = $entity_storage->loadRevision($revision_id);
1568 $entities[$revision_id] = $entity;
1572 $results = $this->assignEntitiesToResult($revision_ids, $entities, $results);
1577 * Sets entities onto the view result row objects.
1579 * This method takes into account the relationship in which the entity was
1580 * needed in the first place.
1582 * @param mixed[][] $ids
1583 * A two dimensional array of identifiers (entity ID / revision ID) keyed by
1585 * @param \Drupal\Core\Entity\EntityInterface[] $entities
1586 * An array of entities keyed by their identified (entity ID / revision ID).
1587 * @param \Drupal\views\ResultRow[] $results
1588 * The entire views result.
1590 * @return \Drupal\views\ResultRow[]
1591 * The changed views results.
1593 protected function assignEntitiesToResult($ids, array $entities, array $results) {
1594 foreach ($ids as $index => $relationships) {
1595 foreach ($relationships as $relationship_id => $id) {
1596 if (isset($entities[$id])) {
1597 $entity = $entities[$id];
1603 if ($relationship_id == 'none') {
1604 $results[$index]->_entity = $entity;
1607 $results[$index]->_relationship_entities[$relationship_id] = $entity;
1617 public function getCacheTags() {
1619 // Add cache tags for each row, if there is an entity associated with it.
1620 if (!$this->hasAggregate) {
1621 foreach ($this->getAllEntities() as $entity) {
1622 $tags = Cache::mergeTags($entity->getCacheTags(), $tags);
1632 public function getCacheMaxAge() {
1633 $max_age = parent::getCacheMaxAge();
1634 foreach ($this->getAllEntities() as $entity) {
1635 $max_age = Cache::mergeMaxAges($max_age, $entity->getCacheMaxAge());
1642 * Gets all the involved entities of the view.
1644 * @return \Drupal\Core\Entity\EntityInterface[]
1646 protected function getAllEntities() {
1648 foreach ($this->view->result as $row) {
1649 if ($row->_entity) {
1650 $entities[] = $row->_entity;
1652 foreach ($row->_relationship_entities as $entity) {
1653 $entities[] = $entity;
1660 public function addSignature(ViewExecutable $view) {
1661 $view->query->addField(NULL, "'" . $view->storage->id() . ':' . $view->current_display . "'", 'view_name');
1664 public function getAggregationInfo() {
1665 // @todo -- need a way to get database specific and customized aggregation
1666 // functions into here.
1669 'title' => $this->t('Group results together'),
1670 'is aggregate' => FALSE,
1673 'title' => $this->t('Count'),
1674 'method' => 'aggregationMethodSimple',
1676 'argument' => 'groupby_numeric',
1677 'field' => 'numeric',
1678 'filter' => 'groupby_numeric',
1679 'sort' => 'groupby_numeric',
1682 'count_distinct' => [
1683 'title' => $this->t('Count DISTINCT'),
1684 'method' => 'aggregationMethodDistinct',
1686 'argument' => 'groupby_numeric',
1687 'field' => 'numeric',
1688 'filter' => 'groupby_numeric',
1689 'sort' => 'groupby_numeric',
1693 'title' => $this->t('Sum'),
1694 'method' => 'aggregationMethodSimple',
1696 'argument' => 'groupby_numeric',
1697 'field' => 'numeric',
1698 'filter' => 'groupby_numeric',
1699 'sort' => 'groupby_numeric',
1703 'title' => $this->t('Average'),
1704 'method' => 'aggregationMethodSimple',
1706 'argument' => 'groupby_numeric',
1707 'field' => 'numeric',
1708 'filter' => 'groupby_numeric',
1709 'sort' => 'groupby_numeric',
1713 'title' => $this->t('Minimum'),
1714 'method' => 'aggregationMethodSimple',
1716 'argument' => 'groupby_numeric',
1717 'field' => 'numeric',
1718 'filter' => 'groupby_numeric',
1719 'sort' => 'groupby_numeric',
1723 'title' => $this->t('Maximum'),
1724 'method' => 'aggregationMethodSimple',
1726 'argument' => 'groupby_numeric',
1727 'field' => 'numeric',
1728 'filter' => 'groupby_numeric',
1729 'sort' => 'groupby_numeric',
1733 'title' => $this->t('Standard deviation'),
1734 'method' => 'aggregationMethodSimple',
1736 'argument' => 'groupby_numeric',
1737 'field' => 'numeric',
1738 'filter' => 'groupby_numeric',
1739 'sort' => 'groupby_numeric',
1745 public function aggregationMethodSimple($group_type, $field) {
1746 return strtoupper($group_type) . '(' . $field . ')';
1749 public function aggregationMethodDistinct($group_type, $field) {
1750 $group_type = str_replace('_distinct', '', $group_type);
1751 return strtoupper($group_type) . '(DISTINCT ' . $field . ')';
1757 public function getDateField($field) {
1758 $db_type = Database::getConnection()->databaseType();
1759 $offset = $this->setupTimezone();
1760 if (isset($offset) && !is_numeric($offset)) {
1761 $dtz = new \DateTimeZone($offset);
1762 $dt = new \DateTime('now', $dtz);
1763 $offset_seconds = $dtz->getOffset($dt);
1768 $field = "DATE_ADD('19700101', INTERVAL $field SECOND)";
1769 if (!empty($offset)) {
1770 $field = "($field + INTERVAL $offset_seconds SECOND)";
1774 $field = "TO_TIMESTAMP($field)";
1775 if (!empty($offset)) {
1776 $field = "($field + INTERVAL '$offset_seconds SECONDS')";
1780 if (!empty($offset)) {
1781 $field = "($field + $offset_seconds)";
1792 public function setupTimezone() {
1793 $timezone = drupal_get_user_timezone();
1795 // set up the database timezone
1796 $db_type = Database::getConnection()->databaseType();
1797 if (in_array($db_type, ['mysql', 'pgsql'])) {
1799 static $already_set = FALSE;
1800 if (!$already_set) {
1801 if ($db_type == 'pgsql') {
1802 Database::getConnection()->query("SET TIME ZONE INTERVAL '$offset' HOUR TO MINUTE");
1804 elseif ($db_type == 'mysql') {
1805 Database::getConnection()->query("SET @@session.time_zone = '$offset'");
1808 $already_set = TRUE;
1818 public function getDateFormat($field, $format, $string_date = FALSE) {
1819 $db_type = Database::getConnection()->databaseType();
1840 $format = strtr($format, $replace);
1841 return "DATE_FORMAT($field, '$format')";
1848 // No format for Numeric representation of a month, without leading
1855 // No format for Day of the month without leading zeros.
1864 $format = strtr($format, $replace);
1865 if (!$string_date) {
1866 return "TO_CHAR($field, '$format')";
1868 // In order to allow for partials (eg, only the year), transform to a
1869 // date, back to a string again.
1870 return "TO_CHAR(TO_TIMESTAMP($field, 'YYYY-MM-DD HH24:MI:SS'), '$format')";
1874 // No format for 2 digit year number.
1876 // No format for 3 letter month name.
1879 // No format for month number without leading zeros.
1881 // No format for full month name.
1883 // No format for 3 letter day name.
1886 // No format for full day name.
1888 // no format for day of month number without leading zeros.
1892 // No format for 12 hour hour with leading zeros.
1896 // No format for AM/PM.
1899 $format = strtr($format, $replace);
1901 // Don't use the 'unixepoch' flag for string date comparisons.
1902 $unixepoch = $string_date ? '' : ", 'unixepoch'";
1904 // SQLite does not have a ISO week substitution string, so it needs
1905 // special handling.
1906 // @see http://wikipedia.org/wiki/ISO_week_date#Calculation
1907 // @see http://stackoverflow.com/a/15511864/1499564
1908 if ($format === '%W') {
1909 $expression = "((strftime('%j', date(strftime('%Y-%m-%d', $field" . $unixepoch . "), '-3 days', 'weekday 4')) - 1) / 7 + 1)";
1912 $expression = "strftime('$format', $field" . $unixepoch . ")";
1914 // The expression yields a string, but the comparison value is an
1915 // integer in case the comparison value is a float, integer, or numeric.
1916 // All of the above SQLite format tokens only produce integers. However,
1917 // the given $format may contain 'Y-m-d', which results in a string.
1918 // @see \Drupal\Core\Database\Driver\sqlite\Connection::expandArguments()
1919 // @see http://www.sqlite.org/lang_datefunc.html
1920 // @see http://www.sqlite.org/lang_expr.html#castexpr
1921 if (preg_match('/^(?:%\w)+$/', $format)) {
1922 $expression = "CAST($expression AS NUMERIC)";