/** * Generates a clone of the current record, removing any auto incremented primary key value and allowing for replicating related records * * This method will accept three different sets of parameters: * * - No parameters: this object will be cloned * - A single `TRUE` value: this object plus all many-to-many associations and all child records (recursively) will be cloned * - Any number of plural related record class names: the many-to-many associations or child records that correspond to the classes specified will be cloned * * The class names specified can be a simple class name if there is only a * single route between the two corresponding database tables. If there is * more than one route between the two tables, the class name should be * substituted with a string in the format `'RelatedClass{route}'`. * * @param string $related_class The plural related class to replicate - see method description for details * @param string ... * @return fActiveRecord The cloned record */ public function replicate($related_class = NULL) { fORM::callHookCallbacks($this, 'pre::replicate()', $this->values, $this->old_values, $this->related_records, $this->cache, fActiveRecord::$replicate_level); fActiveRecord::$replicate_level++; $class = get_class($this); $hash = self::hash($this->values, $class); $schema = fORMSchema::retrieve($class); $table = fORM::tablize($class); // If the object has not been replicated yet, do it now if (!isset(fActiveRecord::$replicate_map[$class])) { fActiveRecord::$replicate_map[$class] = array(); } if (!isset(fActiveRecord::$replicate_map[$class][$hash])) { fActiveRecord::$replicate_map[$class][$hash] = clone $this; // We need the primary key to get a hash, otherwise certain recursive relationships end up losing members $pk_columns = $schema->getKeys($table, 'primary'); if (sizeof($pk_columns) == 1 && $schema->getColumnInfo($table, $pk_columns[0], 'auto_increment')) { fActiveRecord::$replicate_map[$class][$hash]->values[$pk_columns[0]] = $this->values[$pk_columns[0]]; } } $clone = fActiveRecord::$replicate_map[$class][$hash]; $parameters = func_get_args(); $recursive = FALSE; $many_to_many_relationships = $schema->getRelationships($table, 'many-to-many'); $one_to_many_relationships = $schema->getRelationships($table, 'one-to-many'); // When just TRUE is passed we recursively replicate all related records if (sizeof($parameters) == 1 && $parameters[0] === TRUE) { $parameters = array(); $recursive = TRUE; foreach ($many_to_many_relationships as $relationship) { $parameters[] = fGrammar::pluralize(fORM::classize($relationship['related_table'])) . '{' . $relationship['join_table'] . '}'; } foreach ($one_to_many_relationships as $relationship) { $parameters[] = fGrammar::pluralize(fORM::classize($relationship['related_table'])) . '{' . $relationship['related_column'] . '}'; } } $record_sets = array(); foreach ($parameters as $parameter) { // Parse the Class{route} strings if (strpos($parameter, '{') !== FALSE) { $brace = strpos($parameter, '{'); $related_class = fGrammar::singularize(substr($parameter, 0, $brace)); $related_class = fORM::getRelatedClass($class, $related_class); $related_table = fORM::tablize($related_class); $route = substr($parameter, $brace + 1, -1); } else { $related_class = fGrammar::singularize($parameter); $related_class = fORM::getRelatedClass($class, $related_class); $related_table = fORM::tablize($related_class); $route = fORMSchema::getRouteName($schema, $table, $related_table); } // Determine the kind of relationship $many_to_many = FALSE; $one_to_many = FALSE; foreach ($many_to_many_relationships as $relationship) { if ($relationship['related_table'] == $related_table && $relationship['join_table'] == $route) { $many_to_many = TRUE; break; } } foreach ($one_to_many_relationships as $relationship) { if ($relationship['related_table'] == $related_table && $relationship['related_column'] == $route) { $one_to_many = TRUE; break; } } if (!$many_to_many && !$one_to_many) { throw new fProgrammerException('The related class specified, %1$s, does not appear to be in a many-to-many or one-to-many relationship with %$2s', $parameter, get_class($this)); } // Get the related records $record_set = fORMRelated::buildRecords($class, $this->values, $this->related_records, $related_class, $route); // One-to-many records need to be replicated, possibly recursively if ($one_to_many) { if ($recursive) { $records = $record_set->call('replicate', TRUE); } else { $records = $record_set->call('replicate'); } $record_set = fRecordSet::buildFromArray($related_class, $records); $record_set->call('set' . fGrammar::camelize($route, TRUE), NULL); } // Cause the related records to be associated with the new clone fORMRelated::associateRecords($class, $clone->related_records, $related_class, $record_set, $route); } fActiveRecord::$replicate_level--; if (!fActiveRecord::$replicate_level) { // This removes the primary keys we had added back in for proper duplicate detection foreach (fActiveRecord::$replicate_map as $class => $records) { $table = fORM::tablize($class); $pk_columns = $schema->getKeys($table, 'primary'); if (sizeof($pk_columns) != 1 || !$schema->getColumnInfo($table, $pk_columns[0], 'auto_increment')) { continue; } foreach ($records as $hash => $record) { $record->values[$pk_columns[0]] = NULL; } } fActiveRecord::$replicate_map = array(); } fORM::callHookCallbacks($this, 'post::replicate()', $this->values, $this->old_values, $this->related_records, $this->cache, fActiveRecord::$replicate_level); fORM::callHookCallbacks($clone, 'cloned::replicate()', $clone->values, $clone->old_values, $clone->related_records, $clone->cache, fActiveRecord::$replicate_level); return $clone; }
/** * Counts the related records for all records in this set in one DB query * * @param string $related_class This should be the name of a related class * @param string $route This should be a column name or a join table name and is only required when there are multiple routes to a related table. If there are multiple routes and this is not specified, an fProgrammerException will be thrown. * @return fRecordSet The record set object, to allow for method chaining */ private function precount($related_class, $route = NULL) { if (!$this->records) { return $this; } $this->validateSingleClass('precount'); // If there are no primary keys we can just exit if (!array_merge($this->getPrimaryKeys())) { return $this; } fActiveRecord::validateClass($related_class); fActiveRecord::forceConfigure($related_class); $db = fORMDatabase::retrieve($this->class, 'read'); $schema = fORMSchema::retrieve($this->class); $related_table = fORM::tablize($related_class); $table = fORM::tablize($this->class); $route = fORMSchema::getRouteName($schema, $table, $related_table, $route, '*-to-many'); $relationship = fORMSchema::getRoute($schema, $table, $related_table, $route, '*-to-many'); // Build the query out $table_and_column = $table . '.' . $relationship['column']; if (isset($relationship['join_table'])) { $table_to_join = $relationship['join_table']; $column_to_join = $relationship['join_table'] . '.' . $relationship['join_column']; } else { $table_to_join = $related_table; $column_to_join = $related_table . '.' . $relationship['related_column']; } $params = array($db->escape("SELECT count(*) AS flourish__count, %r AS flourish__column FROM %r INNER JOIN %r ON %r = %r WHERE ", $table_and_column, $table, $table_to_join, $table_and_column, $column_to_join)); $params = $this->addWhereParams($db, $schema, $params); $params[0] .= $db->escape(' GROUP BY %r', $table_and_column); // Run the query and inject the results into the records $result = call_user_func_array($db->translatedQuery, $params); $counts = array(); foreach ($result as $row) { $counts[$row['flourish__column']] = (int) $row['flourish__count']; } unset($result); $total_records = sizeof($this->records); $get_method = 'get' . fGrammar::camelize($relationship['column'], TRUE); $tally_method = 'tally' . fGrammar::pluralize($related_class); for ($i = 0; $i < $total_records; $i++) { $record = $this->records[$i]; $count = isset($counts[$record->{$get_method}()]) ? $counts[$record->{$get_method}()] : 0; $record->{$tally_method}($count, $route); } return $this; }
/** * Sets the related records for *-to-many relationships * * @internal * * @param string $class The class to set the related records for * @param array &$related_records The related records existing for the fActiveRecord class * @param string $related_class The class we are associating with the current record * @param fRecordSet $records The records are associating * @param string $route The route to use between the current class and the related class * @return void */ public static function setRecordSet($class, &$related_records, $related_class, fRecordSet $records, $route = NULL) { fActiveRecord::validateClass($related_class); fActiveRecord::forceConfigure($related_class); $table = fORM::tablize($class); $related_table = fORM::tablize($related_class); $schema = fORMSchema::retrieve($class); $route = fORMSchema::getRouteName($schema, $table, $related_table, $route); if (!isset($related_records[$related_table])) { $related_records[$related_table] = array(); } if (!isset($related_records[$related_table][$route])) { $related_records[$related_table][$route] = array(); } $related_records[$related_table][$route]['record_set'] = $records; $related_records[$related_table][$route]['count'] = $records->count(); $related_records[$related_table][$route]['associate'] = FALSE; $related_records[$related_table][$route]['primary_keys'] = NULL; }
/** * Add a one-to-many rule that requires at least one related record is associated with the current record * * @param mixed $class The class name or instance of the class to add the rule for * @param string $related_class The name of the related class * @param string $route The route to the related class * @return void */ public static function addOneToManyRule($class, $related_class, $route = NULL) { $class = fORM::getClass($class); $related_class = fORM::getRelatedClass($class, $related_class); if (!isset(self::$related_one_or_more_rules[$class])) { self::$related_one_or_more_rules[$class] = array(); } if (!isset(self::$related_one_or_more_rules[$class][$related_class])) { self::$related_one_or_more_rules[$class][$related_class] = array(); } $route = fORMSchema::getRouteName(fORMSchema::retrieve($class), fORM::tablize($class), fORM::tablize($related_class), $route, 'one-to-many'); self::$related_one_or_more_rules[$class][$related_class][$route] = TRUE; }
/** * Finds all of the table names in the SQL and creates the appropriate `FROM` and `GROUP BY` clauses with all necessary joins * * The SQL string should contain two placeholders, `:from_clause` and * `:group_by_clause`. All columns should be qualified with their full table * name. Here is an example SQL string to pass in presumming that the * tables users and groups are in a relationship: * * {{{ * SELECT users.* FROM :from_clause WHERE groups.group_id = 5 :group_by_clause ORDER BY lower(users.first_name) ASC * }}} * * @internal * * @param fDatabase $db The database the query is to be executed on * @param fSchema $schema The schema for the database * @param array $params The parameters for the fDatabase::query() call * @param string $table The main table to be queried * @return array The params with the SQL `FROM` and `GROUP BY` clauses injected */ public static function injectFromAndGroupByClauses($db, $schema, $params, $table) { $table = self::cleanTableName($schema, $table); $joins = array(); if (strpos($params[0], ':from_clause') === FALSE) { throw new fProgrammerException('No %1$s placeholder was found in:%2$s', ':from_clause', "\n" . $params[0]); } if (strpos($params[0], ':group_by_clause') === FALSE && !preg_match('#group\\s+by#i', $params[0])) { throw new fProgrammerException('No %1$s placeholder was found in:%2$s', ':group_by_clause', "\n" . $params[0]); } $has_group_by_placeholder = strpos($params[0], ':group_by_clause') !== FALSE ? TRUE : FALSE; // Separate the SQL from quoted values preg_match_all("#(?:'(?:''|\\\\'|\\\\[^']|[^'\\\\])*')|(?:[^']+)#", $params[0], $matches); $table_alias = $table; $used_aliases = array(); $table_map = array(); // If we are not passing in existing joins, start with the specified table if (!$joins) { $joins[] = array('join_type' => 'none', 'table_name' => $table, 'table_alias' => $table_alias); } $used_aliases[] = $table_alias; foreach ($matches[0] as $match) { if ($match[0] != "'") { // This removes quotes from around . in the {route} specified of a shorthand column name $match = preg_replace('#(\\{\\w+)"\\."(\\w+\\})#', '\\1.\\2', $match); //fCore::expose($match); preg_match_all('#(?<!\\w|"|=>)((?:"?((?:\\w+"?\\."?)?\\w+)(?:\\{([\\w.]+)\\})?"?=>)?("?(?:\\w+"?\\."?)?\\w+)(?:\\{([\\w.]+)\\})?"?)\\."?\\w+"?(?=[^\\w".{])#m', $match, $table_matches, PREG_SET_ORDER); foreach ($table_matches as $table_match) { if (!isset($table_match[5])) { $table_match[5] = NULL; } if (!empty($table_match[2])) { $table_match[2] = self::cleanTableName($schema, $table_match[2]); } $table_match[4] = self::cleanTableName($schema, $table_match[4]); if (in_array($db->getType(), array('oracle', 'db2'))) { foreach (array(2, 3, 4, 5) as $subpattern) { if (isset($table_match[$subpattern])) { $table_match[$subpattern] = strtolower($table_match[$subpattern]); } } } // This is a related table that is going to join to a once-removed table if (!empty($table_match[2])) { $related_table = $table_match[2]; $route = fORMSchema::getRouteName($schema, $table, $related_table, $table_match[3]); $join_name = $table . '_' . $related_table . '{' . $route . '}'; $once_removed_table = $table_match[4]; // Add the once removed table to the aliases in case we also join directly to it // which may cause the replacements later in this method to convert first to the // real table name and then from the real table to the real table's alias if (!in_array($once_removed_table, $used_aliases)) { $used_aliases[] = $once_removed_table; } self::createJoin($schema, $table, $table_alias, $related_table, $route, $joins, $used_aliases); $route = fORMSchema::getRouteName($schema, $related_table, $once_removed_table, $table_match[5]); $join_name = self::createJoin($schema, $related_table, $joins[$join_name]['table_alias'], $once_removed_table, $route, $joins, $used_aliases); $table_map[$table_match[1]] = $db->escape('%r', $joins[$join_name]['table_alias']); // Remove the once removed table from the aliases so we also join directly to it without an alias unset($used_aliases[array_search($once_removed_table, $used_aliases)]); // This is a related table } elseif (($table_match[4] != $table || fORMSchema::getRoutes($schema, $table, $table_match[4])) && self::cleanTableName($schema, $table_match[1]) != $table) { $related_table = $table_match[4]; $route = fORMSchema::getRouteName($schema, $table, $related_table, $table_match[5]); // If the related table is the current table and it is a one-to-many we don't want to join if ($table_match[4] == $table) { $one_to_many_routes = fORMSchema::getRoutes($schema, $table, $related_table, 'one-to-many'); if (isset($one_to_many_routes[$route])) { $table_map[$table_match[1]] = $db->escape('%r', $table_alias); continue; } } $join_name = self::createJoin($schema, $table, $table_alias, $related_table, $route, $joins, $used_aliases); $table_map[$table_match[1]] = $db->escape('%r', $joins[$join_name]['table_alias']); } } } } // Determine if we joined a *-to-many relationship $joined_to_many = FALSE; foreach ($joins as $name => $join) { if (is_numeric($name)) { continue; } if (substr($name, -5) == '_join') { $joined_to_many = TRUE; break; } $main_table = preg_replace('#_' . $join['table_name'] . '{\\w+}$#iD', '', $name); $second_table = $join['table_name']; $route = preg_replace('#^[^{]+{([\\w.]+)}$#D', '\\1', $name); $routes = fORMSchema::getRoutes($schema, $main_table, $second_table, '*-to-many'); if (isset($routes[$route])) { $joined_to_many = TRUE; break; } } $found_order_by = FALSE; $from_clause = self::createFromClauseFromJoins($db, $joins); // If we are joining on a *-to-many relationship we need to group by the // columns in the main table to prevent duplicate entries if ($joined_to_many) { $column_info = $schema->getColumnInfo($table); $columns = array(); foreach ($column_info as $column => $info) { $columns[] = $table . '.' . $column; } $group_by_columns = $db->escape('%r ', $columns); $group_by_clause = ' GROUP BY ' . $group_by_columns; } else { $group_by_clause = ' '; $group_by_columns = ''; } // Put the SQL back together $new_sql = ''; $preg_table_pattern = preg_quote($table, '#') . '\\.|' . preg_quote('"' . $table . '"', '#') . '\\.'; foreach ($matches[0] as $match) { $temp_sql = $match; // Get rid of the => notation and the :from_clause placeholder if ($match[0] !== "'") { // This removes quotes from around . in the {route} specified of a shorthand column name $temp_sql = preg_replace('#(\\{\\w+)"\\."(\\w+\\})#', '\\1.\\2', $match); foreach ($table_map as $arrow_table => $alias) { $temp_sql = preg_replace('#(?<![\\w"])' . preg_quote($arrow_table, '#') . '(?!=[\\w"])#', $alias, $temp_sql); } // In the ORDER BY clause we need to wrap columns in if ($found_order_by && $joined_to_many) { $temp_sql = preg_replace('#(?<!avg\\(|count\\(|max\\(|min\\(|sum\\(|cast\\(|case |when |"|avg\\("|count\\("|max\\("|min\\("|sum\\("|cast\\("|case "|when "|\\{)\\b((?!' . $preg_table_pattern . ')("?\\w+"?\\.)?"?\\w+"?\\."?\\w+"?)(?![^\\w."])#i', 'max(\\1)', $temp_sql); } if ($joined_to_many && preg_match('#order\\s+by#i', $temp_sql)) { $order_by_found = TRUE; $parts = preg_split('#(order\\s+by)#i', $temp_sql, -1, PREG_SPLIT_DELIM_CAPTURE); $parts[2] = $temp_sql = preg_replace('#(?<!avg\\(|count\\(|max\\(|min\\(|sum\\(|cast\\(|case |when |"|avg\\("|count\\("|max\\("|min\\("|sum\\("|cast\\("|case "|when "|\\{)\\b((?!' . $preg_table_pattern . ')("?\\w+"?\\.)?"?\\w+"?\\."?\\w+"?)(?![^\\w."])#i', 'max(\\1)', $parts[2]); $temp_sql = join('', $parts); } $temp_sql = str_replace(':from_clause', $from_clause, $temp_sql); if ($has_group_by_placeholder) { $temp_sql = preg_replace('#\\s:group_by_clause(\\s|$)#', strtr($group_by_clause, array('\\' => '\\\\', '$' => '\\$')), $temp_sql); } elseif ($group_by_columns) { $temp_sql = preg_replace('#(\\sGROUP\\s+BY\\s((?!HAVING|ORDER\\s+BY).)*)\\s#i', '\\1, ' . strtr($group_by_columns, array('\\' => '\\\\', '$' => '\\$')), $temp_sql); } } $new_sql .= $temp_sql; } $params[0] = $new_sql; return $params; }
/** * Counts the related records for all records in this set in one DB query * * @param string $related_class This should be the name of a related class * @param string $route This should be a column name or a join table name and is only required when there are multiple routes to a related table. If there are multiple routes and this is not specified, an fProgrammerException will be thrown. * @return fRecordSet The record set object, to allow for method chaining */ private function precount($related_class, $route = NULL) { if (!$this->records) { return $this; } $this->validateSingleClass('precount'); // If there are no primary keys we can just exit if (!array_merge($this->getPrimaryKeys())) { return $this; } $related_table = fORM::tablize($related_class); $table = fORM::tablize($this->class); $route = fORMSchema::getRouteName($table, $related_table, $route, '*-to-many'); $relationship = fORMSchema::getRoute($table, $related_table, $route, '*-to-many'); $table_with_route = $route ? $table . '{' . $route . '}' : $table; // Build the query out $where_sql = $this->constructWhereClause($route); $order_by_sql = $this->constructOrderByClause($route); $related_table_keys = fORMSchema::retrieve()->getKeys($related_table, 'primary'); $related_table_keys = fORMDatabase::addTableToValues($related_table, $related_table_keys); $related_table_keys = join(', ', $related_table_keys); $column = $table_with_route . '.' . $relationship['column']; $new_sql = 'SELECT count(' . $related_table_keys . ') AS __flourish_count, ' . $column . ' AS __flourish_column '; $new_sql .= ' FROM :from_clause '; $new_sql .= ' WHERE ' . $where_sql; $new_sql .= ' GROUP BY ' . $column; $new_sql .= ' ORDER BY ' . $column . ' ASC'; $new_sql = fORMDatabase::insertFromAndGroupByClauses($related_table, $new_sql); // Run the query and inject the results into the records $result = fORMDatabase::retrieve()->translatedQuery($new_sql); $counts = array(); foreach ($result as $row) { $counts[$row['__flourish_column']] = (int) $row['__flourish_count']; } unset($result); $total_records = sizeof($this->records); $get_method = 'get' . fGrammar::camelize($relationship['column'], TRUE); $tally_method = 'tally' . fGrammar::pluralize($related_class); for ($i = 0; $i < $total_records; $i++) { $record = $this->records[$i]; $count = isset($counts[$record->{$get_method}()]) ? $counts[$record->{$get_method}()] : 0; $record->{$tally_method}($count, $route); } return $this; }
/** * Sets the related records for *-to-many relationships * * @internal * * @param string $class The class to set the related records for * @param array &$related_records The related records existing for the fActiveRecord class * @param string $related_class The class we are associating with the current record * @param fRecordSet $records The records are associating * @param string $route The route to use between the current class and the related class * @return void */ public static function setRecordSet($class, &$related_records, $related_class, fRecordSet $records, $route = NULL) { $table = fORM::tablize($class); $related_table = fORM::tablize($related_class); try { $route = fORMSchema::getRouteName($table, $related_table, $route, '*-to-many'); } catch (fProgrammerException $e) { $route = fORMSchema::getRouteName($table, $related_table, $route, 'one-to-one'); } if (!isset($related_records[$related_table])) { $related_records[$related_table] = array(); } if (!isset($related_records[$related_table][$route])) { $related_records[$related_table][$route] = array(); } $related_records[$related_table][$route]['record_set'] = $records; $related_records[$related_table][$route]['count'] = $records->count(); $related_records[$related_table][$route]['associate'] = FALSE; $related_records[$related_table][$route]['primary_keys'] = NULL; }
/** * Finds all of the table names in the SQL and creates the appropriate `FROM` and `GROUP BY` clauses with all necessary joins * * The SQL string should contain two placeholders, `:from_clause` and * `:group_by_clause`. All columns should be qualified with their full table * name. Here is an example SQL string to pass in presumming that the * tables users and groups are in a relationship: * * {{{ * SELECT users.* FROM :from_clause WHERE groups.group_id = 5 :group_by_clause ORDER BY lower(users.first_name) ASC * }}} * * @internal * * @param string $table The main table to be queried * @param string $sql The SQL to insert the `FROM` clause into * @return string The SQL `FROM` clause */ public static function insertFromAndGroupByClauses($table, $sql) { $joins = array(); if (strpos($sql, ':from_clause') === FALSE) { throw new fProgrammerException("No %1\$s placeholder was found in:%2\$s", ':from_clause', "\n" . $sql); } if (strpos($sql, ':group_by_clause') === FALSE && !preg_match('#group\\s+by#i', $sql)) { throw new fProgrammerException("No %1\$s placeholder was found in:%2\$s", ':group_by_clause', "\n" . $sql); } $has_group_by_placeholder = strpos($sql, ':group_by_clause') !== FALSE ? TRUE : FALSE; // Separate the SQL from quoted values preg_match_all("#(?:'(?:''|\\\\'|\\\\[^']|[^'\\\\])*')|(?:[^']+)#", $sql, $matches); $table_alias = $table; $used_aliases = array(); $table_map = array(); // If we are not passing in existing joins, start with the specified table if (!$joins) { $joins[] = array('join_type' => 'none', 'table_name' => $table, 'table_alias' => $table_alias); } $used_aliases[] = $table_alias; foreach ($matches[0] as $match) { if ($match[0] != "'") { preg_match_all('#\\b((?:(\\w+)(?:\\{(\\w+)\\})?=>)?(\\w+)(?:\\{(\\w+)\\})?)\\.\\w+\\b#m', $match, $table_matches, PREG_SET_ORDER); foreach ($table_matches as $table_match) { if (!isset($table_match[5])) { $table_match[5] = NULL; } // This is a related table that is going to join to a once-removed table if (!empty($table_match[2])) { $related_table = $table_match[2]; $route = fORMSchema::getRouteName($table, $related_table, $table_match[3]); $join_name = $table . '_' . $related_table . '{' . $route . '}'; self::createJoin($table, $table_alias, $related_table, $route, $joins, $used_aliases); $once_removed_table = $table_match[4]; $route = fORMSchema::getRouteName($related_table, $once_removed_table, $table_match[5]); $join_name = self::createJoin($related_table, $joins[$join_name]['table_alias'], $once_removed_table, $route, $joins, $used_aliases); $table_map[$table_match[1]] = $joins[$join_name]['table_alias']; // This is a related table } elseif (($table_match[4] != $table || fORMSchema::getRoutes($table, $table_match[4])) && $table_match[1] != $table) { $related_table = $table_match[4]; $route = fORMSchema::getRouteName($table, $related_table, $table_match[5]); // If the related table is the current table and it is a one-to-many we don't want to join if ($table_match[4] == $table) { $one_to_many_routes = fORMSchema::getRoutes($table, $related_table, 'one-to-many'); if (isset($one_to_many_routes[$route])) { $table_map[$table_match[1]] = $table_alias; continue; } } $join_name = self::createJoin($table, $table_alias, $related_table, $route, $joins, $used_aliases); $table_map[$table_match[1]] = $joins[$join_name]['table_alias']; } } } } // Determine if we joined a *-to-many relationship $joined_to_many = FALSE; foreach ($joins as $name => $join) { if (is_numeric($name)) { continue; } if (substr($name, -5) == '_join') { $joined_to_many = TRUE; break; } $main_table = preg_replace('#_' . $join['table_name'] . '{\\w+}$#iD', '', $name); $second_table = $join['table_name']; $route = preg_replace('#^[^{]+{(\\w+)}$#D', '\\1', $name); $routes = fORMSchema::getRoutes($main_table, $second_table, '*-to-many'); if (isset($routes[$route])) { $joined_to_many = TRUE; break; } } $found_order_by = FALSE; $from_clause = self::createFromClauseFromJoins($joins); // If we are joining on a *-to-many relationship we need to group by the // columns in the main table to prevent duplicate entries if ($joined_to_many) { $column_info = fORMSchema::retrieve()->getColumnInfo($table); $group_by_clause = ' GROUP BY '; $columns = array(); foreach ($column_info as $column => $info) { $columns[] = $table . '.' . $column; } $group_by_columns = join(', ', $columns) . ' '; $group_by_clause .= $group_by_columns; } else { $group_by_clause = ' '; $group_by_columns = ''; } // Put the SQL back together $new_sql = ''; foreach ($matches[0] as $match) { $temp_sql = $match; // Get rid of the => notation and the :from_clause placeholder if ($match[0] !== "'") { foreach ($table_map as $arrow_table => $alias) { $temp_sql = str_replace($arrow_table, $alias, $temp_sql); } // In the ORDER BY clause we need to wrap columns in if ($found_order_by && $joined_to_many) { $temp_sql = preg_replace('#(?<!avg\\(|count\\(|max\\(|min\\(|sum\\(|cast\\(|case |when )\\b((?!' . preg_quote($table, '#') . '\\.)\\w+\\.\\w+)\\b#i', 'max(\\1)', $temp_sql); } if ($joined_to_many && preg_match('#order\\s+by#i', $temp_sql)) { $order_by_found = TRUE; $parts = preg_split('#(order\\s+by)#i', $temp_sql, -1, PREG_SPLIT_DELIM_CAPTURE); $parts[2] = $temp_sql = preg_replace('#(?<!avg\\(|count\\(|max\\(|min\\(|sum\\(|cast\\(|case |when )\\b((?!' . preg_quote($table, '#') . '\\.)\\w+\\.\\w+)\\b#i', 'max(\\1)', $parts[2]); $temp_sql = join('', $parts); } $temp_sql = str_replace(':from_clause', $from_clause, $temp_sql); if ($has_group_by_placeholder) { $temp_sql = preg_replace('#\\s:group_by_clause\\s#', strtr($group_by_clause, array('\\' => '\\\\', '$' => '\\$')), $temp_sql); } elseif ($group_by_columns) { $temp_sql = preg_replace('#(\\sGROUP\\s+BY\\s((?!HAVING|ORDER\\s+BY).)*)\\s#i', '\\1, ' . strtr($group_by_columns, array('\\' => '\\\\', '$' => '\\$')), $temp_sql); } } $new_sql .= $temp_sql; } return $new_sql; }