return $this; } /** * Extend the WHERE clause with an OR and a single condition or an array of conditions. * * Usage: * $query->where(array('a = 1', 'b = 2'))->orWhere(array('c = 3', 'd = 4')); * will produce: WHERE ((a = 1 AND b = 2) OR (c = 3 AND d = 4) * * @param mixed $conditions A string or array of WHERE conditions. * @param string $glue The glue by which to join the conditions. Defaults to AND. * * @return JDatabaseQuery Returns this object to allow chaining. * * @since 3.6 */ public function orWhere($conditions, $glue = 'AND') { return $this->extendWhere('OR', $conditions, $glue); } /** * Extend the WHERE clause with an AND and a single condition or an array of conditions. * * Usage: * $query->where(array('a = 1', 'b = 2'))->andWhere(array('c = 3', 'd = 4')); * will produce: WHERE ((a = 1 AND b = 2) AND (c = 3 OR d = 4) * * @param mixed $conditions A string or array of WHERE conditions. * @param string $glue The glue by which to join the conditions. Defaults to OR. * * @return JDatabaseQuery Returns this object to allow chaining. * * @since 3.6 */ public function andWhere($conditions, $glue = 'OR') { return $this->extendWhere('AND', $conditions, $glue); } /** * Method to provide deep copy support to nested objects and * arrays when cloning. * * @return void * * @since 11.3 */ public function __clone() { foreach ($this as $k => $v) { if ($k === 'db') { continue; } if (is_object($v) || is_array($v)) { $this->{$k} = unserialize(serialize($v)); } } } /** * Add a query to UNION with the current query. * Multiple unions each require separate statements and create an array of unions. * * Usage (the $query base query MUST be a select query): * $query->union('SELECT name FROM #__foo') * $query->union('SELECT name FROM #__foo', true) * $query->union($query2)->union($query3) * * The $query attribute as an array is deprecated and will not be supported in 4.0. * * $query->union(array('SELECT name FROM #__foo','SELECT name FROM #__bar')) * $query->union(array($query2, $query3)) * * @param mixed $query The JDatabaseQuery object or string to union. * @param boolean $distinct True to only return distinct rows from the union. * @param string $glue The glue by which to join the conditions. * * @return JDatabaseQuery Returns this object to allow chaining. * * @link http://dev.mysql.com/doc/refman/5.0/en/union.html * * @since 12.1 */ public function union($query, $distinct = false, $glue = '') { // Set up the DISTINCT flag, the name with parentheses, and the glue. if ($distinct) { $name = 'UNION DISTINCT ()'; $glue = ')' . PHP_EOL . 'UNION DISTINCT ('; } else { $glue = ')' . PHP_EOL . 'UNION ('; $name = 'UNION ()'; } if (is_array($query)) { JLog::add('Query attribute as an array is deprecated.', JLog::WARNING, 'deprecated'); } // Get the JDatabaseQueryElement if it does not exist if (is_null($this->union)) { $this->union = new JDatabaseQueryElement($name, $query, "$glue"); } // Otherwise append the second UNION. else { $this->union->append($query); } return $this; } /** * Add a query to UNION DISTINCT with the current query. Simply a proxy to union with the DISTINCT keyword. * * Usage: * $query->unionDistinct('SELECT name FROM #__foo') * * @param mixed $query The JDatabaseQuery object or string to union. * @param string $glue The glue by which to join the conditions. * * @return JDatabaseQuery Returns this object to allow chaining. * * @see union * * @since 12.1 * @deprecated 4.0 Use union() instead. */ public function unionDistinct($query, $glue = '') { $distinct = true; // Apply the distinct flag to the union. return $this->union($query, $distinct, $glue); } /** * Find and replace sprintf-like tokens in a format string. * Each token takes one of the following forms: * %% - A literal percent character. * %[t] - Where [t] is a type specifier. * %[n]$[x] - Where [n] is an argument specifier and [t] is a type specifier. * * Types: * a - Numeric: Replacement text is coerced to a numeric type but not quoted or escaped. * e - Escape: Replacement text is passed to $this->escape(). * E - Escape (extra): Replacement text is passed to $this->escape() with true as the second argument. * n - Name Quote: Replacement text is passed to $this->quoteName(). * q - Quote: Replacement text is passed to $this->quote(). * Q - Quote (no escape): Replacement text is passed to $this->quote() with false as the second argument. * r - Raw: Replacement text is used as-is. (Be careful) * * Date Types: * - Replacement text automatically quoted (use uppercase for Name Quote). * - Replacement text should be a string in date format or name of a date column. * y/Y - Year * m/M - Month * d/D - Day * h/H - Hour * i/I - Minute * s/S - Second * * Invariable Types: * - Takes no argument. * - Argument index not incremented. * t - Replacement text is the result of $this->currentTimestamp(). * z - Replacement text is the result of $this->nullDate(false). * Z - Replacement text is the result of $this->nullDate(true). * * Usage: * $query->format('SELECT %1$n FROM %2$n WHERE %3$n = %4$a', 'foo', '#__foo', 'bar', 1); * Returns: SELECT `foo` FROM `#__foo` WHERE `bar` = 1 * * Notes: * The argument specifier is optional but recommended for clarity. * The argument index used for unspecified tokens is incremented only when used. * * @param string $format The formatting string. * * @return string Returns a string produced according to the formatting string. * * @since 12.3 */ public function format($format) { $query = $this; $args = array_slice(func_get_args(), 1); array_unshift($args, null); $i = 1; $func = function ($match) use ($query, $args, &$i) { if (isset($match[6]) && $match[6] == '%') { return '%'; } // No argument required, do not increment the argument index. switch ($match[5]) { case 't': return $query->currentTimestamp(); break; case 'z': return $query->nullDate(false); break; case 'Z': return $query->nullDate(true); break; } // Increment the argument index only if argument specifier not provided. $index = is_numeric($match[4]) ? (int) $match[4] : $i++; if (!$index || !isset($args[$index])) { // TODO - What to do? sprintf() throws a Warning in these cases. $replacement = ''; } else { $replacement = $args[$index]; } switch ($match[5]) { case 'a': return 0 + $replacement; break; case 'e': return $query->escape($replacement); break; case 'E': return $query->escape($replacement, true); break; case 'n': return $query->quoteName($replacement); break; case 'q': return $query->quote($replacement); break; case 'Q': return $query->quote($replacement, false); break; case 'r': return $replacement; break; // Dates case 'y': return $query->year($query->quote($replacement)); break; case 'Y': return $query->year($query->quoteName($replacement)); break; case 'm': return $query->month($query->quote($replacement)); break; case 'M': return $query->month($query->quoteName($replacement)); break; case 'd': return $query->day($query->quote($replacement)); break; case 'D': return $query->day($query->quoteName($replacement)); break; case 'h': return $query->hour($query->quote($replacement)); break; case 'H': return $query->hour($query->quoteName($replacement)); break; case 'i': return $query->minute($query->quote($replacement)); break; case 'I': return $query->minute($query->quoteName($replacement)); break; case 's': return $query->second($query->quote($replacement)); break; case 'S': return $query->second($query->quoteName($replacement)); break; } return ''; }; /** * Regexp to find and replace all tokens. * Matched fields: * 0: Full token * 1: Everything following '%' * 2: Everything following '%' unless '%' * 3: Argument specifier and '$' * 4: Argument specifier * 5: Type specifier * 6: '%' if full token is '%%' */ return preg_replace_callback('#%(((([\d]+)\$)?([aeEnqQryYmMdDhHiIsStzZ]))|(%))#', $func, $format); } /** * Add to the current date and time. * Usage: * $query->select($query->dateAdd()); * Prefixing the interval with a - (negative sign) will cause subtraction to be used. * Note: Not all drivers support all units. * * @param mixed $date The date to add to. May be date or datetime * @param string $interval The string representation of the appropriate number of units * @param string $datePart The part of the date to perform the addition on * * @return string The string with the appropriate sql for addition of dates * * @link http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add * @since 13.1 */ public function dateAdd($date, $interval, $datePart) { return trim("DATE_ADD('" . $date . "', INTERVAL " . $interval . ' ' . $datePart . ')'); } /** * Add a query to UNION ALL with the current query. * Multiple unions each require separate statements and create an array of unions. * * Usage: * $query->union('SELECT name FROM #__foo') * * The $query attribute as an array is deprecated and will not be supported in 4.0. * * $query->union(array('SELECT name FROM #__foo','SELECT name FROM #__bar')) * * @param mixed $query The JDatabaseQuery object or string to union. * @param boolean $distinct Not used - ignored. * @param string $glue Not used - ignored. * * @return JDatabaseQuery Returns this object to allow chaining. * * @see union * * @since 13.1 */ public function unionAll($query, $distinct = false, $glue = '') { $glue = ')' . PHP_EOL . 'UNION ALL ('; $name = 'UNION ALL ()'; if (is_array($query)) { JLog::add('Query attribute as an array is deprecated.', JLog::WARNING, 'deprecated'); } // Get the JDatabaseQueryElement if it does not exist if (is_null($this->unionAll)) { $this->unionAll = new JDatabaseQueryElement($name, $query, "$glue"); } // Otherwise append the second UNION. else { $this->unionAll->append($query); } return $this; } /** * Validate arguments which are passed to selectRowNumber method and set up common variables. * * @param string $orderBy An expression of ordering for window function. * @param string $orderColumnAlias An alias for new ordering column. * * @return void * * @since 3.7.0 * @throws RuntimeException */ protected function validateRowNumber($orderBy, $orderColumnAlias) { if ($this->selectRowNumber) { throw new RuntimeException("Method 'selectRowNumber' can be called only once per instance."); } $this->type = 'select'; $this->selectRowNumber = array( 'orderBy' => $orderBy, 'orderColumnAlias' => $orderColumnAlias, ); } /** * Return the number of the current row. * * Usage: * $query->select('id'); * $query->selectRowNumber('ordering,publish_up DESC', 'new_ordering'); * $query->from('#__content'); * * @param string $orderBy An expression of ordering for window function. * @param string $orderColumnAlias An alias for new ordering column. * * @return JDatabaseQuery Returns this object to allow chaining. * * @since 3.7.0 * @throws RuntimeException */ public function selectRowNumber($orderBy, $orderColumnAlias) { $this->validateRowNumber($orderBy, $orderColumnAlias); $this->select("ROW_NUMBER() OVER (ORDER BY $orderBy) AS $orderColumnAlias"); return $this; } }