/** * Adds information about methods provided by this class to fActiveRecord * * @internal * * @param string $class The class to reflect the related record methods for * @param array &$signatures The associative array of `{method_name} => {signature}` * @param boolean $include_doc_comments If the doc block comments for each method should be included * @return void */ public static function reflect($class, &$signatures, $include_doc_comments) { $table = fORM::tablize($class); $schema = fORMSchema::retrieve($class); $one_to_one_relationships = $schema->getRelationships($table, 'one-to-one'); $one_to_many_relationships = $schema->getRelationships($table, 'one-to-many'); $many_to_one_relationships = $schema->getRelationships($table, 'many-to-one'); $many_to_many_relationships = $schema->getRelationships($table, 'many-to-many'); $to_one_relationships = array_merge($one_to_one_relationships, $many_to_one_relationships); $to_many_relationships = array_merge($one_to_many_relationships, $many_to_many_relationships); $to_one_created = array(); foreach ($to_one_relationships as $relationship) { $related_class = fORM::classize($relationship['related_table']); $related_class = fORM::getRelatedClass($class, $related_class); if (isset($to_one_created[$related_class])) { continue; } $routes = fORMSchema::getRoutes($schema, $table, $relationship['related_table'], '*-to-one'); $route_names = array(); foreach ($routes as $route) { $route_names[] = fORMSchema::getRouteNameFromRelationship('*-to-one', $route); } $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Creates the related " . $related_class . "\n"; $signature .= " * \n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return " . $related_class . " The related object\n"; $signature .= " */\n"; } $create_method = 'create' . $related_class; $signature .= 'public function ' . $create_method . '('; if (sizeof($route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$create_method] = $signature; $to_one_created[$related_class] = TRUE; } $one_to_one_created = array(); foreach ($one_to_one_relationships as $relationship) { $related_class = fORM::classize($relationship['related_table']); $related_class = fORM::getRelatedClass($class, $related_class); if (isset($one_to_one_created[$related_class])) { continue; } $routes = fORMSchema::getRoutes($schema, $table, $relationship['related_table'], 'one-to-one'); $route_names = array(); foreach ($routes as $route) { $route_names[] = fORMSchema::getRouteNameFromRelationship('one-to-one', $route); } $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Populates the related " . $related_class . "\n"; $signature .= " * \n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return fActiveRecord The record object, to allow for method chaining\n"; $signature .= " */\n"; } $populate_method = 'populate' . $related_class; $signature .= 'public function ' . $populate_method . '('; if (sizeof($route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$populate_method] = $signature; $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Associates the related " . $related_class . " to this record\n"; $signature .= " * \n"; $signature .= " * @param fActiveRecord|array|string|integer \$record The record, or the primary key of the record, to associate\n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return fActiveRecord The record object, to allow for method chaining\n"; $signature .= " */\n"; } $associate_method = 'associate' . $related_class; $signature .= 'public function ' . $associate_method . '($record'; if (sizeof($route_names) > 1) { $signature .= ', $route'; } $signature .= ')'; $signatures[$associate_method] = $signature; $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Indicates if a related " . $related_class . " exists\n"; $signature .= " * \n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return boolean If a related record exists\n"; $signature .= " */\n"; } $has_method = 'has' . $related_class; $signature .= 'public function ' . $has_method . '($record'; if (sizeof($route_names) > 1) { $signature .= ', $route'; } $signature .= ')'; $signatures[$has_method] = $signature; $one_to_one_created[$related_class] = TRUE; } $to_many_created = array(); foreach ($to_many_relationships as $relationship) { $related_class = fORM::classize($relationship['related_table']); $related_class = fORM::getRelatedClass($class, $related_class); if (isset($to_many_created[$related_class])) { continue; } $routes = fORMSchema::getRoutes($schema, $table, $relationship['related_table'], '*-to-many'); $route_names = array(); $many_to_many_route_names = array(); $one_to_many_route_names = array(); foreach ($routes as $route) { if (isset($route['join_table'])) { $route_name = fORMSchema::getRouteNameFromRelationship('many-to-many', $route); $route_names[] = $route_name; $many_to_many_route_names[] = $route_name; } else { $route_name = fORMSchema::getRouteNameFromRelationship('one-to-many', $route); $route_names[] = $route_name; $one_to_many_route_names[] = $route_name; } } if ($one_to_many_route_names) { $signature = ''; if ($include_doc_comments) { $related_table = fORM::tablize($related_class); $signature .= "/**\n"; $signature .= " * Calls the ::populate() method for multiple child " . $related_class . " records. Uses request value arrays in the form " . $related_table . "::{column_name}[].\n"; $signature .= " * \n"; if (sizeof($one_to_many_route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $one_to_many_route_names) . "'.\n"; } $signature .= " * @return fActiveRecord The record object, to allow for method chaining\n"; $signature .= " */\n"; } $populate_related_method = 'populate' . fGrammar::pluralize($related_class); $signature .= 'public function ' . $populate_related_method . '('; if (sizeof($one_to_many_route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$populate_related_method] = $signature; } if ($many_to_many_route_names) { $signature = ''; if ($include_doc_comments) { $related_table = fORM::tablize($related_class); $signature .= "/**\n"; $signature .= " * Creates entries in the appropriate joining table to create associations with the specified " . $related_class . " records. Uses request value array(s) in the form " . $related_table . "::{primary_key_column_name(s)}[].\n"; $signature .= " * \n"; if (sizeof($many_to_many_route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $many_to_many_route_names) . "'.\n"; } $signature .= " * @return fActiveRecord The record object, to allow for method chaining\n"; $signature .= " */\n"; } $link_related_method = 'link' . fGrammar::pluralize($related_class); $signature .= 'public function ' . $link_related_method . '('; if (sizeof($many_to_many_route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$link_related_method] = $signature; $signature = ''; if ($include_doc_comments) { $related_table = fORM::tablize($related_class); $signature .= "/**\n"; $signature .= " * Creates entries in the appropriate joining table to create associations with the specified " . $related_class . " records\n"; $signature .= " * \n"; $signature .= " * @param fRecordSet|array \$records_to_associate The records to associate - should be an fRecords, an array of records or an array of primary keys\n"; if (sizeof($many_to_many_route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $many_to_many_route_names) . "'.\n"; } $signature .= " * @return fActiveRecord The record object, to allow for method chaining\n"; $signature .= " */\n"; } $associate_related_method = 'associate' . fGrammar::pluralize($related_class); $signature .= 'public function ' . $associate_related_method . '($records_to_associate'; if (sizeof($many_to_many_route_names) > 1) { $signature .= ', $route'; } $signature .= ')'; $signatures[$associate_related_method] = $signature; } $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Builds an fRecordSet of the related " . $related_class . " objects\n"; $signature .= " * \n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return fRecordSet A record set of the related " . $related_class . " objects\n"; $signature .= " */\n"; } $build_method = 'build' . fGrammar::pluralize($related_class); $signature .= 'public function ' . $build_method . '('; if (sizeof($route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$build_method] = $signature; $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Indicates if related " . $related_class . " objects exist\n"; $signature .= " * \n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return boolean If related " . $related_class . " objects exist\n"; $signature .= " */\n"; } $has_method = 'has' . fGrammar::pluralize($related_class); $signature .= 'public function ' . $has_method . '('; if (sizeof($route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$has_method] = $signature; $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Returns an array of the primary keys for the related " . $related_class . " objects\n"; $signature .= " * \n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return array The primary keys of the related " . $related_class . " objects\n"; $signature .= " */\n"; } $list_method = 'list' . fGrammar::pluralize($related_class); $signature .= 'public function ' . $list_method . '('; if (sizeof($route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$list_method] = $signature; $signature = ''; if ($include_doc_comments) { $signature .= "/**\n"; $signature .= " * Counts the number of related " . $related_class . " objects\n"; $signature .= " * \n"; if (sizeof($route_names) > 1) { $signature .= " * @param string \$route The route to the related class. Must be one of: '" . join("', '", $route_names) . "'.\n"; } $signature .= " * @return integer The number related " . $related_class . " objects\n"; $signature .= " */\n"; } $count_method = 'count' . fGrammar::pluralize($related_class); $signature .= 'public function ' . $count_method . '('; if (sizeof($route_names) > 1) { $signature .= '$route'; } $signature .= ')'; $signatures[$count_method] = $signature; $to_many_created[$related_class] = 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; }
/** * Takes information from a method call and determines the subject, route and if subject was plural * * @param string $class The class the method was called on * @param string $subject An underscore_notation subject - either a singular or plural class name * @param string $route The route to the subject * @return array An array with the structure: array(0 => $subject, 1 => $route, 2 => $plural) */ private static function determineSubject($class, $subject, $route) { $schema = fORMSchema::retrieve($class); $table = fORM::tablize($class); $type = '*-to-many'; $plural = FALSE; // one-to-many relationships need to use plural forms $singular_form = fGrammar::singularize($subject, TRUE); if ($singular_form && fORM::isClassMappedToTable($singular_form)) { $subject = $singular_form; $plural = TRUE; } elseif (!fORM::isClassMappedToTable($subject) && in_array(fGrammar::underscorize($subject), $schema->getTables())) { $subject = fGrammar::singularize($subject); $plural = TRUE; } $related_table = fORM::tablize($subject); $routes = fORMSchema::getRoutes($schema, $table, $related_table, '*-to-one'); $star_to_one = $route && isset($routes[$route]) || count($routes); if ($star_to_one) { $type = '*-to-one'; } if ($star_to_one && $plural || !$plural && !$star_to_one) { throw new fProgrammerException('The table %1$s is not in a %2$srelationship with the table %3$s', $table, $type, $related_table); } if ($star_to_one) { $type = !fORMSchema::isOneToOne($schema, $table, $related_table, $route) ? 'many-to-one' : 'one-to-one'; } $route = fORMSchema::getRouteName($schema, $table, $related_table, $route, $type); return array($subject, $route, $plural, $type != 'many-to-one'); }
/** * 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; }