3 namespace Drupal\Core\Database\Driver\pgsql;
5 use Drupal\Core\Database\Query\Select as QuerySelect;
13 * PostgreSQL implementation of \Drupal\Core\Database\Query\Select.
15 class Select extends QuerySelect {
17 public function orderRandom() {
18 $alias = $this->addExpression('RANDOM()', 'random_field');
19 $this->orderBy($alias);
24 * Overrides SelectQuery::orderBy().
26 * PostgreSQL adheres strictly to the SQL-92 standard and requires that when
27 * using DISTINCT or GROUP BY conditions, fields and expressions that are
28 * ordered on also need to be selected. This is a best effort implementation
29 * to handle the cases that can be automated by adding the field if it is not
33 * $query = db_select('example', 'e');
34 * $query->join('example_revision', 'er', 'e.vid = er.vid');
38 * ->orderBy('timestamp');
41 * In this query, it is not possible (without relying on the schema) to know
42 * whether timestamp belongs to example_revision and needs to be added or
43 * belongs to node and is already selected. Queries like this will need to be
44 * corrected in the original query by adding an explicit call to
45 * SelectQuery::addField() or SelectQuery::fields().
47 * Since this has a small performance impact, both by the additional
48 * processing in this function and in the database that needs to return the
49 * additional fields, this is done as an override instead of implementing it
50 * directly in SelectQuery::orderBy().
52 public function orderBy($field, $direction = 'ASC') {
53 // Only allow ASC and DESC, default to ASC.
54 // Emulate MySQL default behavior to sort NULL values first for ascending,
55 // and last for descending.
56 // @see http://www.postgresql.org/docs/9.3/static/queries-order.html
57 $direction = strtoupper($direction) == 'DESC' ? 'DESC NULLS LAST' : 'ASC NULLS FIRST';
58 $this->order[$field] = $direction;
60 if ($this->hasTag('entity_query')) {
64 // If there is a table alias specified, split it up.
65 if (strpos($field, '.') !== FALSE) {
66 list($table, $table_field) = explode('.', $field);
68 // Figure out if the field has already been added.
69 foreach ($this->fields as $existing_field) {
71 // If table alias is given, check if field and table exists.
72 if ($existing_field['table'] == $table && $existing_field['field'] == $table_field) {
77 // If there is no table, simply check if the field exists as a field or
79 if ($existing_field['alias'] == $field) {
85 // Also check expression aliases.
86 foreach ($this->expressions as $expression) {
87 if ($expression['alias'] == $this->connection->escapeAlias($field)) {
92 // If a table loads all fields, it can not be added again. It would
93 // result in an ambiguous alias error because that field would be loaded
94 // twice: Once through table_alias.* and once directly. If the field
95 // actually belongs to a different table, it must be added manually.
96 foreach ($this->tables as $table) {
97 if (!empty($table['all_fields'])) {
102 // If $field contains an characters which are not allowed in a field name
103 // it is considered an expression, these can't be handled automatically
105 if ($this->connection->escapeField($field) != $field) {
109 // This is a case that can be handled automatically, add the field.
110 $this->addField(NULL, $field);
117 public function addExpression($expression, $alias = NULL, $arguments = []) {
119 $alias = 'expression';
122 // This implements counting in the same manner as the parent method.
123 $alias_candidate = $alias;
125 while (!empty($this->expressions[$alias_candidate])) {
126 $alias_candidate = $alias . '_' . $count++;
128 $alias = $alias_candidate;
130 $this->expressions[$alias] = [
131 'expression' => $expression,
132 'alias' => $this->connection->escapeAlias($alias_candidate),
133 'arguments' => $arguments,
142 public function execute() {
143 $this->connection->addSavepoint();
145 $result = parent::execute();
147 catch (\Exception $e) {
148 $this->connection->rollbackSavepoint();
151 $this->connection->releaseSavepoint();
159 * @} End of "addtogroup database".