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 JDatabaseQueryMysqli extends JDatabaseQuery implements JDatabaseQueryLimitable
18 {
19 /**
20 * @var integer The offset for the result set.
21 * @since 12.1
22 */
23 protected $offset;
24
25 /**
26 * @var integer The limit for the result set.
27 * @since 12.1
28 */
29 protected $limit;
30
31 /**
32 * Magic function to convert the query to a string.
33 *
34 * @return string The completed query.
35 *
36 * @since 11.1
37 */
38 public function __toString()
39 {
40 switch ($this->type)
41 {
42 case 'select':
43 if ($this->selectRowNumber)
44 {
45 $orderBy = $this->selectRowNumber['orderBy'];
46 $tmpOffset = $this->offset;
47 $tmpLimit = $this->limit;
48 $this->offset = 0;
49 $this->limit = 0;
50 $tmpOrder = $this->order;
51 $this->order = null;
52 $query = parent::__toString();
53 $this->order = $tmpOrder;
54 $this->offset = $tmpOffset;
55 $this->limit = $tmpLimit;
56
57 // Add support for second order by, offset and limit
58 $query = PHP_EOL . 'SELECT * FROM (' . $query . PHP_EOL . "ORDER BY $orderBy" . PHP_EOL . ') w';
59
60 if ($this->order)
61 {
62 $query .= (string) $this->order;
63 }
64
65 return $this->processLimit($query, $this->limit, $this->offset);
66 }
67 }
68
69 return parent::__toString();
70 }
71
72 /**
73 * Method to modify a query already in string format with the needed
74 * additions to make the query limited to a particular number of
75 * results, or start at a particular offset.
76 *
77 * @param string $query The query in string format
78 * @param integer $limit The limit for the result set
79 * @param integer $offset The offset for the result set
80 *
81 * @return string
82 *
83 * @since 12.1
84 */
85 public function processLimit($query, $limit, $offset = 0)
86 {
87 if ($limit > 0 && $offset > 0)
88 {
89 $query .= ' LIMIT ' . $offset . ', ' . $limit;
90 }
91 elseif ($limit > 0)
92 {
93 $query .= ' LIMIT ' . $limit;
94 }
95
96 return $query;
97 }
98
99 /**
100 * Concatenates an array of column names or values.
101 *
102 * @param array $values An array of values to concatenate.
103 * @param string $separator As separator to place between each value.
104 *
105 * @return string The concatenated values.
106 *
107 * @since 11.1
108 */
109 public function concatenate($values, $separator = null)
110 {
111 if ($separator)
112 {
113 $concat_string = 'CONCAT_WS(' . $this->quote($separator);
114
115 foreach ($values as $value)
116 {
117 $concat_string .= ', ' . $value;
118 }
119
120 return $concat_string . ')';
121 }
122 else
123 {
124 return 'CONCAT(' . implode(',', $values) . ')';
125 }
126 }
127
128 /**
129 * Sets the offset and limit for the result set, if the database driver supports it.
130 *
131 * Usage:
132 * $query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
133 * $query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)
134 *
135 * @param integer $limit The limit for the result set
136 * @param integer $offset The offset for the result set
137 *
138 * @return JDatabaseQuery Returns this object to allow chaining.
139 *
140 * @since 12.1
141 */
142 public function setLimit($limit = 0, $offset = 0)
143 {
144 $this->limit = (int) $limit;
145 $this->offset = (int) $offset;
146
147 return $this;
148 }
149
150 /**
151 * Return correct regexp operator for mysqli.
152 *
153 * Ensure that the regexp operator is mysqli compatible.
154 *
155 * Usage:
156 * $query->where('field ' . $query->regexp($search));
157 *
158 * @param string $value The regex pattern.
159 *
160 * @return string Returns the regex operator.
161 *
162 * @since 11.3
163 */
164 public function regexp($value)
165 {
166 return ' REGEXP ' . $value;
167 }
168
169 /**
170 * Return correct rand() function for Mysql.
171 *
172 * Ensure that the rand() function is Mysql compatible.
173 *
174 * Usage:
175 * $query->Rand();
176 *
177 * @return string The correct rand function.
178 *
179 * @since 3.5
180 */
181 public function Rand()
182 {
183 return ' RAND() ';
184 }
185
186 /**
187 * Return the number of the current row.
188 *
189 * @param string $orderBy An expression of ordering for window function.
190 * @param string $orderColumnAlias An alias for new ordering column.
191 *
192 * @return JDatabaseQuery Returns this object to allow chaining.
193 *
194 * @since 3.7.0
195 * @throws RuntimeException
196 */
197 public function selectRowNumber($orderBy, $orderColumnAlias)
198 {
199 $this->validateRowNumber($orderBy, $orderColumnAlias);
200 $this->select("(SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS $orderColumnAlias");
201
202 return $this;
203 }
204
205 /**
206 * Casts a value to a char.
207 *
208 * Ensure that the value is properly quoted before passing to the method.
209 *
210 * Usage:
211 * $query->select($query->castAsChar('a'));
212 * $query->select($query->castAsChar('a', 40));
213 *
214 * @param string $value The value to cast as a char.
215 *
216 * @param string $len The lenght of the char.
217 *
218 * @return string Returns the cast value.
219 *
220 * @since 3.7.0
221 */
222 public function castAsChar($value, $len = null)
223 {
224 if (!$len)
225 {
226 return $value;
227 }
228 else
229 {
230 return ' CAST(' . $value . ' AS CHAR(' . $len . '))';
231 }
232 }
233 }
234