/** * Create a GROUP BY SQL clause. * * @param string|array $fields Group By fields * @param Model $Model The model to get group by fields for. * @return string Group By clause or null. */ public function group($fields, Model $Model = null) { if (empty($fields)) { return null; } if (!is_array($fields)) { $fields = array($fields); } if ($Model !== null) { foreach ($fields as $index => $key) { if ($Model->isVirtualField($key)) { $fields[$index] = '(' . $Model->getVirtualField($key) . ')'; } } } $fields = implode(', ', $fields); return ' GROUP BY ' . $this->_quoteFields($fields); }
/** * Generates the fields list of an SQL query. * * @param Model $model * @param string $alias Alias tablename * @param mixed $fields * @param boolean $quote If false, returns fields array unquoted * @return array * @access public */ function fields(&$model, $alias = null, $fields = array(), $quote = true) { if (empty($alias)) { $alias = $model->alias; } $cacheKey = array($model->useDbConfig, $model->table, array_keys($model->schema()), $model->name, $model->getVirtualField(), $alias, $fields, $quote); $cacheKey = crc32(serialize($cacheKey)); if ($return = $this->cacheMethod(__FUNCTION__, $cacheKey)) { return $return; } $allFields = empty($fields); if ($allFields) { $fields = array_keys($model->schema()); } elseif (!is_array($fields)) { $fields = String::tokenize($fields); } $fields = array_values(array_filter($fields)); $allFields = $allFields || in_array('*', $fields) || in_array($model->alias . '.*', $fields); $virtual = array(); $virtualFields = $model->getVirtualField(); if (!empty($virtualFields)) { $virtualKeys = array_keys($virtualFields); foreach ($virtualKeys as $field) { $virtualKeys[] = $model->alias . '.' . $field; } $virtual = $allFields ? $virtualKeys : array_intersect($virtualKeys, $fields); foreach ($virtual as $i => $field) { if (strpos($field, '.') !== false) { $virtual[$i] = str_replace($model->alias . '.', '', $field); } $fields = array_diff($fields, array($field)); } $fields = array_values($fields); } if (!$quote) { if (!empty($virtual)) { $fields = array_merge($fields, $this->_constructVirtualFields($model, $alias, $virtual)); } return $fields; } $count = count($fields); if ($count >= 1 && !in_array($fields[0], array('*', 'COUNT(*)'))) { for ($i = 0; $i < $count; $i++) { if (is_string($fields[$i]) && in_array($fields[$i], $virtual)) { unset($fields[$i]); continue; } if (is_object($fields[$i]) && isset($fields[$i]->type) && $fields[$i]->type === 'expression') { $fields[$i] = $fields[$i]->value; } elseif (preg_match('/^\\(.*\\)\\s' . $this->alias . '.*/i', $fields[$i])) { continue; } elseif (!preg_match('/^.+\\(.*\\)/', $fields[$i])) { $prepend = ''; if (strpos($fields[$i], 'DISTINCT') !== false) { $prepend = 'DISTINCT '; $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i])); } $dot = strpos($fields[$i], '.'); if ($dot === false) { $prefix = !(strpos($fields[$i], ' ') !== false || strpos($fields[$i], '(') !== false); $fields[$i] = $this->name(($prefix ? $alias . '.' : '') . $fields[$i]); } else { $value = array(); $comma = strpos($fields[$i], ','); if ($comma === false) { $build = explode('.', $fields[$i]); if (!Set::numeric($build)) { $fields[$i] = $this->name(implode('.', $build)); } } } $fields[$i] = $prepend . $fields[$i]; } elseif (preg_match('/\\(([\\.\\w]+)\\)/', $fields[$i], $field)) { if (isset($field[1])) { if (strpos($field[1], '.') === false) { $field[1] = $this->name($alias . '.' . $field[1]); } else { $field[0] = explode('.', $field[1]); if (!Set::numeric($field[0])) { $field[0] = implode('.', array_map(array(&$this, 'name'), $field[0])); $fields[$i] = preg_replace('/\\(' . $field[1] . '\\)/', '(' . $field[0] . ')', $fields[$i], 1); } } } } } } if (!empty($virtual)) { $fields = array_merge($fields, $this->_constructVirtualFields($model, $alias, $virtual)); } return $this->cacheMethod(__FUNCTION__, $cacheKey, array_unique($fields)); }
/** * Normalize field * * @param Model $model Model * @param string $field Name of the field * @return string */ private function normalizeField(Model $model, $field) { // @codingStandardsIgnoreLine if ($model->hasField($field)) { $field = $model->alias . '.' . $field; } elseif ($model->isVirtualField($field)) { $db = $model->getDataSource(); $field = $model->getVirtualField($field); $field = $db->dispatchMethod('_quoteFields', array($field)); $field = '(' . $field . ')'; } return $field; }
/** * Returns an SQL calculation, i.e. COUNT() or MAX() * * @param Model $Model The model to get a calculated field for. * @param string $func Lowercase name of SQL function, i.e. 'count' or 'max' * @param array $params Function parameters (any values must be quoted manually) * * @return string An SQL calculation function */ public function calculate(Model $Model, $func, $params = array()) { $params = (array) $params; switch (strtolower($func)) { case 'count': if (!isset($params[0])) { $params[0] = '*'; } if (!isset($params[1])) { $params[1] = 'count'; } if ($Model->isVirtualField($params[0])) { $arg = $this->_quoteFields($Model->getVirtualField($params[0])); } else { $arg = $this->name($params[0]); } return 'COUNT(' . $arg . ') AS ' . $this->name($params[1]); case 'max': case 'min': if (!isset($params[1])) { $params[1] = $params[0]; } if ($Model->isVirtualField($params[0])) { $arg = $this->_quoteFields($Model->getVirtualField($params[0])); } else { $arg = $this->name($params[0]); } return strtoupper($func) . '(' . $arg . ') AS ' . $this->name($params[1]); } }
/** * Converts model virtual fields into sql expressions to be fetched later * * @param Model $model * @param string $alias Alias table name * @param array $fields virtual fields to be used on query * * @return array */ protected function _constructVirtualFields(Model $model, $alias, $fields) { $virtual = array(); foreach ($fields as $field) { $virtualField = $this->name($alias . $this->virtualFieldSeparator . $field); $expression = $this->_quoteFields($model->getVirtualField($field)); $virtual[] = '(' . $expression . ") {$this->alias} {$virtualField}"; } return $virtual; }
/** * Get All Field Names * * Return a list of all field names, regardless of field map settings, and * including virtual fields. * * @since 1.0 * @param Model $Model * @return array */ public function getAllFieldNames(Model $Model) { $schema = $Model->schema(); $virtualFields = $Model->getVirtualField(); if (empty($virtualFields)) { $virtualFields = array(); } $all_fields = array_merge($schema, $virtualFields); $return = array(); if (!empty($all_fields)) { foreach ($all_fields as $field => $settings) { $return[] = $Model->alias . '.' . $field; } } return $return; }
/** * Generates the fields list of an SQL query. * * @param Model $model * @param string $alias Alias tablename * @param mixed $fields * @param boolean $quote If false, returns fields array unquoted * @return array * @access public */ public function fields(&$model, $alias = null, $fields = array(), $quote = true) { if (empty($alias)) { $alias = $model->alias; } $allFields = empty($fields); if ($allFields) { $fields = array_keys($model->schema()); } elseif (!is_array($fields)) { $fields = String::tokenize($fields); } $fields = array_values(array_filter($fields)); if (!$quote) { return $fields; } $virtual = array(); if ($model->getVirtualField()) { $keys = array_keys($model->getVirtualField()); $virtual = $allFields ? $keys : array_intersect($keys, $fields); } $count = count($fields); if ($count >= 1 && !in_array($fields[0], array('*', 'COUNT(*)'))) { for ($i = 0; $i < $count; $i++) { if (in_array($fields[$i], $virtual)) { unset($fields[$i]); continue; } if (is_object($fields[$i]) && isset($fields[$i]->type) && $fields[$i]->type === 'expression') { $fields[$i] = $fields[$i]->value; } elseif (preg_match('/^\\(.*\\)\\s' . $this->alias . '.*/i', $fields[$i])) { continue; } elseif (!preg_match('/^.+\\(.*\\)/', $fields[$i])) { $prepend = ''; if (strpos($fields[$i], 'DISTINCT') !== false) { $prepend = 'DISTINCT '; $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i])); } $dot = strpos($fields[$i], '.'); if ($dot === false) { $prefix = !(strpos($fields[$i], ' ') !== false || strpos($fields[$i], '(') !== false); $fields[$i] = $this->name(($prefix ? $alias . '.' : '') . $fields[$i]); } else { $value = array(); $comma = strpos($fields[$i], ','); if ($comma === false) { $build = explode('.', $fields[$i]); if (!Set::numeric($build)) { $fields[$i] = $this->name($build[0] . '.' . $build[1]); } $comma = String::tokenize($fields[$i]); foreach ($comma as $string) { if (preg_match('/^[0-9]+\\.[0-9]+$/', $string)) { $value[] = $string; } else { $build = explode('.', $string); $value[] = $this->name(trim($build[0]) . '.' . trim($build[1])); } } $fields[$i] = implode(', ', $value); } } $fields[$i] = $prepend . $fields[$i]; } elseif (preg_match('/\\(([\\.\\w]+)\\)/', $fields[$i], $field)) { if (isset($field[1])) { if (strpos($field[1], '.') === false) { $field[1] = $this->name($alias . '.' . $field[1]); } else { $field[0] = explode('.', $field[1]); if (!Set::numeric($field[0])) { $field[0] = implode('.', array_map(array($this, 'name'), $field[0])); $fields[$i] = preg_replace('/\\(' . $field[1] . '\\)/', '(' . $field[0] . ')', $fields[$i], 1); } } } } } } if (!empty($virtual)) { $fields = array_merge($fields, $this->_constructVirtualFields($model, $alias, $virtual)); } return array_unique($fields); }