1 <?php
2 /**
3 * @package Joomla.Platform
4 * @subpackage Database
5 *
6 * @copyright Copyright (C) 2005 - 2017 Open Source Matters, Inc. All rights reserved.
7 * @license GNU General Public License version 2 or later; see LICENSE
8 */
9
10 defined('JPATH_PLATFORM') or die;
11
12 /**
13 * Query Building Class.
14 *
15 * @since 11.1
16 */
17 class JDatabaseQuerySqlsrv extends JDatabaseQuery implements JDatabaseQueryLimitable
18 {
19 /**
20 * The character(s) used to quote SQL statement names such as table names or field names,
21 * etc. The child classes should define this as necessary. If a single character string the
22 * same character is used for both sides of the quoted name, else the first character will be
23 * used for the opening quote and the second for the closing quote.
24 *
25 * @var string
26 * @since 11.1
27 */
28 protected $name_quotes = '`';
29
30 /**
31 * The null or zero representation of a timestamp for the database driver. This should be
32 * defined in child classes to hold the appropriate value for the engine.
33 *
34 * @var string
35 * @since 11.1
36 */
37 protected $null_date = '1900-01-01 00:00:00';
38
39 /**
40 * @var integer The affected row limit for the current SQL statement.
41 * @since 3.2
42 */
43 protected $limit = 0;
44
45 /**
46 * @var integer The affected row offset to apply for the current SQL statement.
47 * @since 3.2
48 */
49 protected $offset = 0;
50
51 /**
52 * Magic function to convert the query to a string.
53 *
54 * @return string The completed query.
55 *
56 * @since 11.1
57 */
58 public function __toString()
59 {
60 $query = '';
61
62 switch ($this->type)
63 {
64 case 'select':
65 // Add required aliases for offset or fixGroupColumns method
66 $columns = $this->fixSelectAliases();
67
68 $query = (string) $this->select;
69
70 if ($this->group)
71 {
72 $this->fixGroupColumns($columns);
73 }
74
75 $query .= (string) $this->from;
76
77 if ($this->join)
78 {
79 // Special case for joins
80 foreach ($this->join as $join)
81 {
82 $query .= (string) $join;
83 }
84 }
85
86 if ($this->where)
87 {
88 $query .= (string) $this->where;
89 }
90
91 if ($this->selectRowNumber === null)
92 {
93 if ($this->group)
94 {
95 $query .= (string) $this->group;
96 }
97
98 if ($this->having)
99 {
100 $query .= (string) $this->having;
101 }
102 }
103
104 if ($this->order)
105 {
106 $query .= (string) $this->order;
107 }
108
109 if ($this instanceof JDatabaseQueryLimitable && ($this->limit > 0 || $this->offset > 0))
110 {
111 $query = $this->processLimit($query, $this->limit, $this->offset);
112 }
113
114 break;
115
116 case 'insert':
117 $query .= (string) $this->insert;
118
119 // Set method
120 if ($this->set)
121 {
122 $query .= (string) $this->set;
123 }
124 // Columns-Values method
125 elseif ($this->values)
126 {
127 if ($this->columns)
128 {
129 $query .= (string) $this->columns;
130 }
131
132 $elements = $this->insert->getElements();
133 $tableName = array_shift($elements);
134
135 $query .= 'VALUES ';
136 $query .= (string) $this->values;
137
138 if ($this->autoIncrementField)
139 {
140 $query = 'SET IDENTITY_INSERT ' . $tableName . ' ON;' . $query . 'SET IDENTITY_INSERT ' . $tableName . ' OFF;';
141 }
142
143 if ($this->where)
144 {
145 $query .= (string) $this->where;
146 }
147 }
148
149 break;
150
151 case 'delete':
152 $query .= (string) $this->delete;
153 $query .= (string) $this->from;
154
155 if ($this->join)
156 {
157 // Special case for joins
158 foreach ($this->join as $join)
159 {
160 $query .= (string) $join;
161 }
162 }
163
164 if ($this->where)
165 {
166 $query .= (string) $this->where;
167 }
168
169 if ($this->order)
170 {
171 $query .= (string) $this->order;
172 }
173
174 break;
175
176 case 'update':
177 if ($this->join)
178 {
179 $tmpUpdate = $this->update;
180 $tmpFrom = $this->from;
181 $this->update = null;
182 $this->from = null;
183
184 $updateElem = $tmpUpdate->getElements();
185 $updateArray = explode(' ', $updateElem[0]);
186
187 // Use table alias if exists
188 $this->update(end($updateArray));
189 $this->from($updateElem[0]);
190
191 $query .= (string) $this->update;
192 $query .= (string) $this->set;
193 $query .= (string) $this->from;
194
195 $this->update = $tmpUpdate;
196 $this->from = $tmpFrom;
197
198 // Special case for joins
199 foreach ($this->join as $join)
200 {
201 $query .= (string) $join;
202 }
203 }
204 else
205 {
206 $query .= (string) $this->update;
207 $query .= (string) $this->set;
208 }
209
210 if ($this->where)
211 {
212 $query .= (string) $this->where;
213 }
214
215 if ($this->order)
216 {
217 $query .= (string) $this->order;
218 }
219
220 break;
221
222 default:
223 $query = parent::__toString();
224
225 break;
226 }
227
228 return $query;
229 }
230
231 /**
232 * Casts a value to a char.
233 *
234 * Ensure that the value is properly quoted before passing to the method.
235 *
236 * @param string $value The value to cast as a char.
237 *
238 * @param string $len The lenght of the char.
239 *
240 * @return string Returns the cast value.
241 *
242 * @since 11.1
243 */
244 public function castAsChar($value, $len = null)
245 {
246 if (!$len)
247 {
248 return 'CAST(' . $value . ' as NVARCHAR(30))';
249 }
250 else
251 {
252 return 'CAST(' . $value . ' as NVARCHAR(' . $len . '))';
253 }
254 }
255
256 /**
257 * Gets the function to determine the length of a character string.
258 *
259 * @param string $field A value.
260 * @param string $operator Comparison operator between charLength integer value and $condition
261 * @param string $condition Integer value to compare charLength with.
262 *
263 * @return string The required char length call.
264 *
265 * @since 11.1
266 */
267 public function charLength($field, $operator = null, $condition = null)
268 {
269 return 'DATALENGTH(' . $field . ')' . (isset($operator) && isset($condition) ? ' ' . $operator . ' ' . $condition : '');
270 }
271
272 /**
273 * Concatenates an array of column names or values.
274 *
275 * @param array $values An array of values to concatenate.
276 * @param string $separator As separator to place between each value.
277 *
278 * @return string The concatenated values.
279 *
280 * @since 11.1
281 */
282 public function concatenate($values, $separator = null)
283 {
284 if ($separator)
285 {
286 return '(' . implode('+' . $this->quote($separator) . '+', $values) . ')';
287 }
288 else
289 {
290 return '(' . implode('+', $values) . ')';
291 }
292 }
293
294 /**
295 * Gets the current date and time.
296 *
297 * @return string
298 *
299 * @since 11.1
300 */
301 public function currentTimestamp()
302 {
303 return 'GETDATE()';
304 }
305
306 /**
307 * Get the length of a string in bytes.
308 *
309 * @param string $value The string to measure.
310 *
311 * @return integer
312 *
313 * @since 11.1
314 */
315 public function length($value)
316 {
317 return 'LEN(' . $value . ')';
318 }
319
320 /**
321 * Add to the current date and time.
322 * Usage:
323 * $query->select($query->dateAdd());
324 * Prefixing the interval with a - (negative sign) will cause subtraction to be used.
325 *
326 * @param datetime $date The date to add to; type may be time or datetime.
327 * @param string $interval The string representation of the appropriate number of units
328 * @param string $datePart The part of the date to perform the addition on
329 *
330 * @return string The string with the appropriate sql for addition of dates
331 *
332 * @since 13.1
333 * @note Not all drivers support all units.
334 * @link http://msdn.microsoft.com/en-us/library/ms186819.aspx for more information
335 */
336 public function dateAdd($date, $interval, $datePart)
337 {
338 return "DATEADD('" . $datePart . "', '" . $interval . "', '" . $date . "'" . ')';
339 }
340
341 /**
342 * Method to modify a query already in string format with the needed
343 * additions to make the query limited to a particular number of
344 * results, or start at a particular offset.
345 *
346 * @param string $query The query in string format
347 * @param integer $limit The limit for the result set
348 * @param integer $offset The offset for the result set
349 *
350 * @return string
351 *
352 * @since 12.1
353 */
354 public function processLimit($query, $limit, $offset = 0)
355 {
356 if ($limit)
357 {
358 $total = $offset + $limit;
359 $query = substr_replace($query, 'SELECT TOP ' . (int) $total, stripos($query, 'SELECT'), 6);
360 }
361
362 if (!$offset)
363 {
364 return $query;
365 }
366
367 return PHP_EOL
368 . 'SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber FROM ('
369 . $query
370 . PHP_EOL . ') AS A) AS A WHERE RowNumber > ' . (int) $offset;
371 }
372
373 /**
374 * Sets the offset and limit for the result set, if the database driver supports it.
375 *
376 * Usage:
377 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
378 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
379 *
380 * @param integer $limit The limit for the result set
381 * @param integer $offset The offset for the result set
382 *
383 * @return JDatabaseQuery Returns this object to allow chaining.
384 *
385 * @since 12.1
386 */
387 public function setLimit($limit = 0, $offset = 0)
388 {
389 $this->limit = (int) $limit;
390 $this->offset = (int) $offset;
391
392 return $this;
393 }
394
395 /**
396 * Split a string of sql expression into an array of individual columns.
397 * Single line or line end comments and multi line comments are stripped off.
398 * Always return at least one column.
399 *
400 * @param string $string Input string of sql expression like select expression.
401 *
402 * @return array[] The columns from the input string separated into an array.
403 *
404 * @since 3.7.0
405 */
406 protected function splitSqlExpression($string)
407 {
408 // Append whitespace as equivalent to the last comma
409 $string .= ' ';
410
411 $colIdx = 0;
412 $start = 0;
413 $open = false;
414 $openC = 0;
415 $comment = false;
416 $endString = '';
417 $length = strlen($string);
418 $columns = array();
419 $column = array();
420 $current = '';
421 $previous = null;
422 $operators = array(
423 '+' => '',
424 '-' => '',
425 '*' => '',
426 '/' => '',
427 '%' => '',
428 '&' => '',
429 '|' => '',
430 '~' => '',
431 '^' => '',
432 );
433
434 $addBlock = function ($block) use (&$column, &$colIdx)
435 {
436 if (isset($column[$colIdx]))
437 {
438 $column[$colIdx] .= $block;
439 }
440 else
441 {
442 $column[$colIdx] = $block;
443 }
444 };
445
446 for ($i = 0; $i < $length; $i++)
447 {
448 $current = substr($string, $i, 1);
449 $current2 = substr($string, $i, 2);
450 $current3 = substr($string, $i, 3);
451 $lenEndString = strlen($endString);
452 $testEnd = substr($string, $i, $lenEndString);
453
454 if ($current == '[' || $current == '"' || $current == "'" || $current2 == '--'
455 || ($current2 == '/*') || ($current == '#' && $current3 != '#__')
456 || ($lenEndString && $testEnd == $endString))
457 {
458 if ($open)
459 {
460 if ($testEnd === $endString)
461 {
462 if ($comment)
463 {
464 if ($lenEndString > 1)
465 {
466 $i += ($lenEndString - 1);
467 }
468
469 // Move cursor after close tag of comment
470 $start = $i + 1;
471 $comment = false;
472 }
473 elseif ($current == "'" || $current == ']' || $current == '"')
474 {
475 // Check for escaped quote like '', ]] or ""
476 $n = 1;
477
478 while ($i + $n < $length && $string[$i + $n] == $current)
479 {
480 $n++;
481 }
482
483 // Jump to the last quote
484 $i += $n - 1;
485
486 if ($n % 2 === 0)
487 {
488 // There is only escaped quote
489 continue;
490 }
491 elseif ($n > 2)
492 {
493 // The last right close quote is not escaped
494 $current = $string[$i];
495 }
496 }
497
498 $open = false;
499 $endString = '';
500 }
501 }
502 else
503 {
504 $open = true;
505
506 if ($current == '#' || $current2 == '--')
507 {
508 $endString = "\n";
509 $comment = true;
510 }
511 elseif ($current2 == '/*')
512 {
513 $endString = '*/';
514 $comment = true;
515 }
516 elseif ($current == '[')
517 {
518 $endString = ']';
519 }
520 else
521 {
522 $endString = $current;
523 }
524
525 if ($comment && $start < $i)
526 {
527 // Add string exists before comment
528 $addBlock(substr($string, $start, $i - $start));
529 $previous = $string[$i - 1];
530 $start = $i;
531 }
532 }
533 }
534 elseif (!$open)
535 {
536 if ($current == '(')
537 {
538 $openC++;
539 $previous = $current;
540 }
541 elseif ($current == ')')
542 {
543 $openC--;
544 $previous = $current;
545 }
546 elseif ($current == '.')
547 {
548 if ($i === $start && $colIdx > 0 && !isset($column[$colIdx]))
549 {
550 // Remove whitepace placed before dot
551 $colIdx--;
552 }
553
554 $previous = $current;
555 }
556 elseif ($openC === 0)
557 {
558 if (ctype_space($current))
559 {
560 // Normalize whitepace
561 $string[$i] = ' ';
562
563 if ($start < $i)
564 {
565 // Add text placed before whitespace
566 $addBlock(substr($string, $start, $i - $start));
567 $colIdx++;
568 $previous = $string[$i - 1];
569 }
570 elseif (isset($column[$colIdx]))
571 {
572 if ($colIdx > 1 || !isset($operators[$previous]))
573 {
574 // There was whitespace after comment
575 $colIdx++;
576 }
577 }
578
579 // Move cursor forward
580 $start = $i + 1;
581 }
582 elseif (isset($operators[$current]) && ($current !== '*' || $previous !== '.'))
583 {
584 if ($start < $i)
585 {
586 // Add text before operator
587 $addBlock(substr($string, $start, $i - $start));
588 $colIdx++;
589 }
590 elseif (!isset($column[$colIdx]) && isset($operators[$previous]))
591 {
592 // Do not create whitespace between operators
593 $colIdx--;
594 }
595
596 // Add operator
597 $addBlock($current);
598 $previous = $current;
599 $colIdx++;
600
601 // Move cursor forward
602 $start = $i + 1;
603 }
604 else
605 {
606 $previous = $current;
607 }
608 }
609 }
610
611 if (($current == ',' && !$open && $openC == 0) || $i == $length - 1)
612 {
613 if ($start < $i && !$comment)
614 {
615 // Save remaining text
616 $addBlock(substr($string, $start, $i - $start));
617 }
618
619 $columns[] = $column;
620
621 // Reset values
622 $column = array();
623 $colIdx = 0;
624 $previous = null;
625
626 // Column saved, move cursor forward after comma
627 $start = $i + 1;
628 }
629 }
630
631 return $columns;
632 }
633
634 /**
635 * Add required aliases to columns for select statement in subquery.
636 *
637 * @return array[] Array of columns with added missing aliases.
638 *
639 * @since 3.7.0
640 */
641 protected function fixSelectAliases()
642 {
643 $operators = array(
644 '+' => '',
645 '-' => '',
646 '*' => '',
647 '/' => '',
648 '%' => '',
649 '&' => '',
650 '|' => '',
651 '~' => '',
652 '^' => '',
653 );
654
655 // Split into array and remove comments
656 $columns = $this->splitSqlExpression(implode(',', $this->select->getElements()));
657
658 foreach ($columns as $i => $column)
659 {
660 $size = count($column);
661
662 if ($size == 0)
663 {
664 continue;
665 }
666
667 if ($size > 2 && strcasecmp($column[$size - 2], 'AS') === 0)
668 {
669 // Alias exists, replace it to uppercase
670 $columns[$i][$size - 2] = 'AS';
671 continue;
672 }
673
674 if ($i == 0 && stripos(' DISTINCT ALL ', " $column[0] ") !== false)
675 {
676 // This words are reserved, they are not column names
677 array_shift($column);
678 $size--;
679 }
680
681 $lastWord = strtoupper($column[$size - 1]);
682 $length = strlen($lastWord);
683 $lastChar = $lastWord[$length - 1];
684
685 if ($lastChar == '*')
686 {
687 // Skip on wildcard
688 continue;
689 }
690
691 if ($lastChar == ')'
692 || ($size == 1 && $lastChar == "'")
693 || $lastWord[0] == '@'
694 || $lastWord == 'NULL'
695 || $lastWord == 'END'
696 || is_numeric($lastWord))
697 {
698 /* Ends with:
699 * - SQL function
700 * - single static value like 'only '+'string'
701 * - @@var
702 * - NULL
703 * - CASE ... END
704 * - Numeric
705 */
706 $columns[$i][] = 'AS';
707 $columns[$i][] = $this->quoteName('columnAlias' . $i);
708 continue;
709 }
710
711 if ($size == 1)
712 {
713 continue;
714 }
715
716 $lastChar2 = substr($column[$size - 2], -1);
717
718 // Check if column ends with '- a.x' or '- a. x'
719 if (isset($operators[$lastChar2])
720 || ($size > 2 && $lastChar2 === '.' && isset($operators[substr($column[$size - 3], -1)])))
721 {
722 // Ignore plus signs if column start with them
723 if ($size != 2 || ltrim($column[0], '+') !== '' || $column[1][0] === "'")
724 {
725 // If operator exists before last word then alias is required for subquery
726 $columns[$i][] = 'AS';
727 $columns[$i][] = $this->quoteName('columnAlias' . $i);
728 continue;
729 }
730 }
731 elseif ($column[$size - 1][0] !== '.' && $lastChar2 !== '.')
732 {
733 // If columns is like name name2 then second word is alias.
734 // Add missing AS before the alias, exception for 'a. x' and 'a .x'
735 array_splice($columns[$i], -1, 0, 'AS');
736 }
737 }
738
739 $selectColumns = array();
740
741 foreach ($columns as $i => $column)
742 {
743 $selectColumns[$i] = implode(' ', $column);
744 }
745
746 $this->select = new JDatabaseQueryElement('SELECT', $selectColumns);
747
748 return $columns;
749 }
750
751 /**
752 * Add missing columns names to GROUP BY clause.
753 *
754 * @param array[] $selectColumns Array of columns from splitSqlExpression method.
755 *
756 * @return JDatabaseQuery Returns this object to allow chaining.
757 *
758 * @since 3.7.0
759 */
760 protected function fixGroupColumns($selectColumns)
761 {
762 // Cache tables columns
763 static $cacheCols = array();
764
765 // Known columns of all included tables
766 $knownColumnsByAlias = array();
767
768 $iquotes = array('"' => '', '[' => '', "'" => '');
769 $nquotes = array('"', '[', ']');
770
771 // Aggregate functions
772 $aFuncs = array(
773 'AVG(',
774 'CHECKSUM_AGG(',
775 'COUNT(',
776 'COUNT_BIG(',
777 'GROUPING(',
778 'GROUPING_ID(',
779 'MIN(',
780 'MAX(',
781 'SUM(',
782 'STDEV(',
783 'STDEVP(',
784 'VAR(',
785 'VARP(',
786 );
787
788 // Aggregated columns
789 $filteredColumns = array();
790
791 // Aliases found in SELECT statement
792 $knownAliases = array();
793 $wildcardTables = array();
794
795 foreach ($selectColumns as $i => $column)
796 {
797 $size = count($column);
798
799 if ($size === 0)
800 {
801 continue;
802 }
803
804 if ($size > 2 && $column[$size - 2] === 'AS')
805 {
806 // Save and remove AS alias
807 $alias = $column[$size - 1];
808
809 if (isset($iquotes[$alias[0]]))
810 {
811 $alias = substr($alias, 1, -1);
812 }
813
814 // Remove alias
815 $selectColumns[$i] = $column = array_slice($column, 0, -2);
816
817 if ($size === 3 || ($size === 4 && strpos('+-*/%&|~^', $column[0][0]) !== false))
818 {
819 $lastWord = $column[$size - 3];
820
821 if ($lastWord[0] === "'" || $lastWord === 'NULL' || is_numeric($lastWord))
822 {
823 unset($selectColumns[$i]);
824
825 continue;
826 }
827 }
828
829 // Remember pair alias => column expression
830 $knownAliases[$alias] = implode(' ', $column);
831 }
832
833 $aggregated = false;
834
835 foreach ($column as $j => $block)
836 {
837 if (substr($block, -2) === '.*')
838 {
839 // Found column ends with .*
840 if (isset($iquotes[$block[0]]))
841 {
842 // Quoted table
843 $wildcardTables[] = substr($block, 1, -3);
844 }
845 else
846 {
847 $wildcardTables[] = substr($block, 0, -2);
848 }
849 }
850 elseif (str_ireplace($aFuncs, '', $block) != $block)
851 {
852 $aggregated = true;
853 }
854
855 if ($block[0] === "'")
856 {
857 // Shrink static strings which could contain column name
858 $column[$j] = "''";
859 }
860 }
861
862 if (!$aggregated)
863 {
864 // Without aggregated columns and aliases
865 $filteredColumns[] = implode(' ', $selectColumns[$i]);
866 }
867
868 // Without aliases and static strings
869 $selectColumns[$i] = implode(' ', $column);
870 }
871
872 // If select statement use table.* expression
873 if ($wildcardTables)
874 {
875 // Split FROM statement into list of tables
876 $tables = $this->splitSqlExpression(implode(',', $this->from->getElements()));
877
878 foreach ($tables as $i => $table)
879 {
880 $table = implode(' ', $table);
881
882 // Exclude subquery from the FROM clause
883 if (strpos($table, '(') === false)
884 {
885 // Unquote
886 $table = str_replace($nquotes, '', $table);
887 $table = str_replace('#__', $this->db->getPrefix(), $table);
888 $table = explode(' ', $table);
889 $alias = end($table);
890 $table = $table[0];
891
892 // Chek if exists a wildcard with current alias table?
893 if (in_array($alias, $wildcardTables, true))
894 {
895 if (!isset($cacheCols[$table]))
896 {
897 $cacheCols[$table] = $this->db->getTableColumns($table);
898 }
899
900 if ($this->join || $table != $alias)
901 {
902 foreach ($cacheCols[$table] as $name => $type)
903 {
904 $knownColumnsByAlias[$alias][] = $alias . '.' . $name;
905 }
906 }
907 else
908 {
909 foreach ($cacheCols[$table] as $name => $type)
910 {
911 $knownColumnsByAlias[$alias][] = $name;
912 }
913 }
914 }
915 }
916 }
917
918 // Now we need to get all tables from any joins
919 // Go through all joins and add them to the tables array
920 if ($this->join)
921 {
922 foreach ($this->join as $join)
923 {
924 // Unquote and replace prefix
925 $joinTbl = str_replace($nquotes, '', (string) $join);
926 $joinTbl = str_replace("#__", $this->db->getPrefix(), $joinTbl);
927
928 // Exclude subquery
929 if (preg_match('/JOIN\s+(\w+)(?:\s+AS)?(?:\s+(\w+))?/i', $joinTbl, $matches))
930 {
931 $table = $matches[1];
932 $alias = isset($matches[2]) ? $matches[2] : $table;
933
934 // Chek if exists a wildcard with current alias table?
935 if (in_array($alias, $wildcardTables, true))
936 {
937 if (!isset($cacheCols[$table]))
938 {
939 $cacheCols[$table] = $this->db->getTableColumns($table);
940 }
941
942 foreach ($cacheCols[$table] as $name => $type)
943 {
944 $knownColumnsByAlias[$alias][] = $alias . '.' . $name;
945 }
946 }
947 }
948 }
949 }
950 }
951
952 $selectExpression = implode(',', $selectColumns);
953
954 // Split into the right columns
955 $groupColumns = $this->splitSqlExpression(implode(',', $this->group->getElements()));
956
957 // Remove column aliases from GROUP statement - SQLSRV does not support it
958 foreach ($groupColumns as $i => $column)
959 {
960 $groupColumns[$i] = implode(' ', $column);
961 $column = str_replace($nquotes, '', $groupColumns[$i]);
962
963 if (isset($knownAliases[$column]))
964 {
965 // Be sure that this is not a valid column name
966 if (!preg_match('/\b' . preg_quote($column, '/') . '\b/', $selectExpression))
967 {
968 // Replace column alias by column expression
969 $groupColumns[$i] = $knownAliases[$column];
970 }
971 }
972 }
973
974 // Find all alias.* and fill with proper table column names
975 foreach ($filteredColumns as $i => $column)
976 {
977 if (substr($column, -2) === '.*')
978 {
979 unset($filteredColumns[$i]);
980
981 // Extract alias.* columns into GROUP BY statement
982 $groupColumns = array_merge($groupColumns, $knownColumnsByAlias[substr($column, 0, -2)]);
983 }
984 }
985
986 $groupColumns = array_merge($groupColumns, $filteredColumns);
987
988 if ($this->order)
989 {
990 // Remove direction suffixes
991 $dir = array(" DESC\v", " ASC\v");
992
993 $orderColumns = $this->splitSqlExpression(implode(',', $this->order->getElements()));
994
995 foreach ($orderColumns as $i => $column)
996 {
997 $column = implode(' ', $column);
998 $orderColumns[$i] = $column = trim(str_ireplace($dir, '', "$column\v"), "\v");
999
1000 if (isset($knownAliases[str_replace($nquotes, '', $column)]))
1001 {
1002 unset($orderColumns[$i]);
1003 }
1004
1005 if (str_ireplace($aFuncs, '', $column) != $column)
1006 {
1007 // Do not add aggregate expression
1008 unset($orderColumns[$i]);
1009 }
1010 }
1011
1012 $groupColumns = array_merge($groupColumns, $orderColumns);
1013 }
1014
1015 // Get a unique string of all column names that need to be included in the group statement
1016 $this->group = new JDatabaseQueryElement('GROUP BY', array_unique($groupColumns));
1017
1018 return $this;
1019 }
1020
1021 /**
1022 * Return correct rand() function for MSSQL.
1023 *
1024 * Ensure that the rand() function is MSSQL compatible.
1025 *
1026 * Usage:
1027 * $query->Rand();
1028 *
1029 * @return string The correct rand function.
1030 *
1031 * @since 3.5
1032 */
1033 public function Rand()
1034 {
1035 return ' NEWID() ';
1036 }
1037 }
1038