[Summary view] [Print] [Text view]

   1  <?php
   2  /**
   3   * Part of the Joomla Framework Database Package
   4   *
   5   * @copyright  Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved.
   6   * @license    GNU General Public License version 2 or later; see LICENSE
   7   */
   8  
   9  namespace Joomla\Database\Query;
  10  
  11  /**
  12   * Trait for MySQL Query Building.
  13   *
  14   * @since  2.0.0
  15   */
  16  trait MysqlQueryBuilder
  17  {
  18      /**
  19       * Magic function to convert the query to a string.
  20       *
  21       * @return  string  The completed query.
  22       *
  23       * @since   2.0.0
  24       */
  25  	public function __toString()
  26      {
  27          switch ($this->type)
  28          {
  29              case 'select':
  30                  if ($this->selectRowNumber)
  31                  {
  32                      $orderBy      = $this->selectRowNumber['orderBy'];
  33                      $tmpOffset    = $this->offset;
  34                      $tmpLimit     = $this->limit;
  35                      $this->offset = 0;
  36                      $this->limit  = 0;
  37                      $tmpOrder     = $this->order;
  38                      $this->order  = null;
  39                      $query        = parent::__toString();
  40                      $this->order  = $tmpOrder;
  41                      $this->offset = $tmpOffset;
  42                      $this->limit  = $tmpLimit;
  43  
  44                      // Add support for second order by, offset and limit
  45                      $query = PHP_EOL . 'SELECT * FROM (' . $query . PHP_EOL . "ORDER BY $orderBy" . PHP_EOL . ') w';
  46  
  47                      if ($this->order)
  48                      {
  49                          $query .= (string) $this->order;
  50                      }
  51  
  52                      return $this->processLimit($query, $this->limit, $this->offset);
  53                  }
  54          }
  55  
  56          return parent::__toString();
  57      }
  58  
  59      /**
  60       * Method to modify a query already in string format with the needed additions to make the query limited to a particular number of
  61       * results, or start at a particular offset.
  62       *
  63       * @param   string   $query   The query in string format
  64       * @param   integer  $limit   The limit for the result set
  65       * @param   integer  $offset  The offset for the result set
  66       *
  67       * @return  string
  68       *
  69       * @since   2.0.0
  70       */
  71  	public function processLimit($query, $limit, $offset = 0)
  72      {
  73          if ($limit > 0 && $offset > 0)
  74          {
  75              $query .= ' LIMIT ' . $offset . ', ' . $limit;
  76          }
  77          elseif ($limit > 0)
  78          {
  79              $query .= ' LIMIT ' . $limit;
  80          }
  81  
  82          return $query;
  83      }
  84  
  85      /**
  86       * Concatenates an array of column names or values.
  87       *
  88       * @param   string[]     $values     An array of values to concatenate.
  89       * @param   string|null  $separator  As separator to place between each value.
  90       *
  91       * @return  string  The concatenated values.
  92       *
  93       * @since   2.0.0
  94       */
  95  	public function concatenate($values, $separator = null)
  96      {
  97          if ($separator !== null)
  98          {
  99              $statement = 'CONCAT_WS(' . $this->quote($separator);
 100  
 101              foreach ($values as $value)
 102              {
 103                  $statement .= ', ' . $value;
 104              }
 105  
 106              return $statement . ')';
 107          }
 108  
 109          return 'CONCAT(' . implode(',', $values) . ')';
 110      }
 111  
 112      /**
 113       * Aggregate function to get input values concatenated into a string, separated by delimiter
 114       *
 115       * Usage:
 116       * $query->groupConcat('id', ',');
 117       *
 118       * @param   string  $expression  The expression to apply concatenation to, this may be a column name or complex SQL statement.
 119       * @param   string  $separator   The delimiter of each concatenated value
 120       *
 121       * @return  string  Input values concatenated into a string, separated by delimiter
 122       *
 123       * @since   2.0.0
 124       */
 125  	public function groupConcat($expression, $separator = ',')
 126      {
 127          return 'GROUP_CONCAT(' . $expression . ' SEPARATOR ' . $this->quote($separator) . ')';
 128      }
 129  
 130      /**
 131       * Method to quote and optionally escape a string to database requirements for insertion into the database.
 132       *
 133       * This method is provided for use where the query object is passed to a function for modification.
 134       * If you have direct access to the database object, it is recommended you use the quote method directly.
 135       *
 136       * Note that 'q' is an alias for this method as it is in DatabaseDriver.
 137       *
 138       * Usage:
 139       * $query->quote('fulltext');
 140       * $query->q('fulltext');
 141       * $query->q(array('option', 'fulltext'));
 142       *
 143       * @param   array|string  $text    A string or an array of strings to quote.
 144       * @param   boolean       $escape  True (default) to escape the string, false to leave it unchanged.
 145       *
 146       * @return  string  The quoted input string.
 147       *
 148       * @since   2.0.0
 149       * @throws  \RuntimeException if the internal db property is not a valid object.
 150       */
 151      abstract public function quote($text, $escape = true);
 152  
 153      /**
 154       * Get the regular expression operator
 155       *
 156       * Usage:
 157       * $query->where('field ' . $query->regexp($search));
 158       *
 159       * @param   string  $value  The regex pattern.
 160       *
 161       * @return  string
 162       *
 163       * @since   2.0.0
 164       */
 165  	public function regexp($value)
 166      {
 167          return ' REGEXP ' . $value;
 168      }
 169  
 170      /**
 171       * Get the function to return a random floating-point value
 172       *
 173       * Usage:
 174       * $query->rand();
 175       *
 176       * @return  string
 177       *
 178       * @since   2.0.0
 179       */
 180  	public function rand()
 181      {
 182          return ' RAND() ';
 183      }
 184  
 185      /**
 186       * Find a value in a varchar used like a set.
 187       *
 188       * Ensure that the value is an integer before passing to the method.
 189       *
 190       * Usage:
 191       * $query->findInSet((int) $parent->id, 'a.assigned_cat_ids')
 192       *
 193       * @param   string  $value  The value to search for.
 194       * @param   string  $set    The set of values.
 195       *
 196       * @return  string  A representation of the MySQL find_in_set() function for the driver.
 197       *
 198       * @since   2.0.0
 199       */
 200  	public function findInSet($value, $set)
 201      {
 202          return ' find_in_set(' . $value . ', ' . $set . ')';
 203      }
 204  
 205      /**
 206       * Return the number of the current row.
 207       *
 208       * Usage:
 209       * $query->select('id');
 210       * $query->selectRowNumber('ordering,publish_up DESC', 'new_ordering');
 211       * $query->from('#__content');
 212       *
 213       * @param   string  $orderBy           An expression of ordering for window function.
 214       * @param   string  $orderColumnAlias  An alias for new ordering column.
 215       *
 216       * @return  $this
 217       *
 218       * @since   2.0.0
 219       * @throws  \RuntimeException
 220       */
 221  	public function selectRowNumber($orderBy, $orderColumnAlias)
 222      {
 223          $this->validateRowNumber($orderBy, $orderColumnAlias);
 224  
 225          return $this->select("(SELECT @rownum := @rownum + 1 FROM (SELECT @rownum := 0) AS r) AS $orderColumnAlias");
 226      }
 227  
 228      /**
 229       * Casts a value to a char.
 230       *
 231       * Ensure that the value is properly quoted before passing to the method.
 232       *
 233       * Usage:
 234       * $query->select($query->castAs('CHAR', 'a'));
 235       *
 236       * @param   string  $type    The type of string to cast as.
 237       * @param   string  $value   The value to cast as a char.
 238       * @param   string  $length  The value to cast as a char.
 239       *
 240       * @return  string  SQL statement to cast the value as a char type.
 241       *
 242       * @since   1.0
 243       */
 244  	public function castAs(string $type, string $value, ?string $length = null)
 245      {
 246          switch (strtoupper($type))
 247          {
 248              case 'CHAR':
 249                  if (!$length)
 250                  {
 251                      return $value;
 252                  }
 253                  else
 254                  {
 255                      return 'CAST(' . $value . ' AS CHAR(' . $length . '))';
 256                  }
 257  
 258              case 'INT':
 259                  return '(' . $value . ' + 0)';
 260          }
 261  
 262          return parent::castAs($type, $value, $length);
 263      }
 264  }