/**
* Associate many records with it's related records by foreign key in one query
* @param fRecordset|array $records
* @param string $relatedClassName
* @param string $foreignKeyName
* @return array Objects fo type relatedClassName by it's ids
*/
protected function associateManyRelated($records, $relatedClassName, $foreignKeyName)
{
$recordsById = array();
if ($records instanceof fRecordSet) {
foreach ($records as $record) {
$recordsById[$record->getId()] = $record;
}
} else {
$recordsById = $records;
}
if (empty($recordsById)) {
return array();
}
// Get related records
$orderBys = fORMRelated::getOrderBys(get_class(reset($recordsById)), $relatedClassName, $foreignKeyName);
$relatedRecords = fRecordSet::build($relatedClassName, array($foreignKeyName . '=' => array_keys($recordsById)), $orderBys);
$relatedRecordsById = array();
$relatedRecordsByByForeignKey = array();
foreach ($relatedRecords as $relatedRecord) {
$relatedRecordsById[$relatedRecord->getId()] = $relatedRecord;
$relatedRecordsByByForeignKey[$relatedRecord->get($foreignKeyName)][] = $relatedRecord;
}
// Assoc related records to records
$associateMethodName = 'associate' . $relatedClassName;
foreach ($relatedRecordsByByForeignKey as $foreignKeyValue => $relatedRecords) {
$recordsById[$foreignKeyValue]->{$associateMethodName}($relatedRecords);
}
return $relatedRecordsById;
}
/**
* Adds an `ORDER BY` clause to the SQL for the primary keys of this record set
*
* @param fDatabase $db The database the query will be executed on
* @param fSchema $schema The schema for the database
* @param array $params The parameters for the fDatabase::query() call
* @param string $related_class The related class to add the order bys for
* @param string $route The route to this table from another table
* @return array The params with the `ORDER BY` clause added
*/
private function addOrderByParams($db, $schema, $params, $related_class, $route = NULL)
{
$table = fORM::tablize($this->class);
$table_with_route = $route ? $table . '{' . $route . '}' : $table;
$pk_columns = $schema->getKeys($table, 'primary');
$first_pk_column = $pk_columns[0];
$escaped_pk_columns = array();
foreach ($pk_columns as $pk_column) {
$escaped_pk_columns[$pk_column] = $db->escape('%r', $table_with_route . '.' . $pk_column);
}
$column_info = $schema->getColumnInfo($table);
$sql = '';
$number = 0;
foreach ($this->getPrimaryKeys() as $primary_key) {
$sql .= 'WHEN ';
if (is_array($primary_key)) {
$conditions = array();
foreach ($pk_columns as $pk_column) {
$value = $primary_key[$pk_column];
// This makes sure the query performs the way an insert will
if ($value === NULL && $column_info[$pk_column]['not_null'] && $column_info[$pk_column]['default'] !== NULL) {
$value = $column_info[$pk_column]['default'];
}
$conditions[] = str_replace('%r', $escaped_pk_columns[$pk_column], fORMDatabase::makeCondition($schema, $table, $pk_column, '=', $value));
$params[] = $value;
}
$sql .= join(' AND ', $conditions);
} else {
$sql .= str_replace('%r', $escaped_pk_columns[$first_pk_column], fORMDatabase::makeCondition($schema, $table, $first_pk_column, '=', $primary_key));
$params[] = $primary_key;
}
$sql .= ' THEN ' . $number . ' ';
$number++;
}
$params[0] .= 'CASE ' . $sql . 'END ASC';
if ($related_order_bys = fORMRelated::getOrderBys($this->class, $related_class, $route)) {
$params[0] .= ', ';
$params = fORMDatabase::addOrderByClause($db, $schema, $params, fORM::tablize($related_class), $related_order_bys);
}
return $params;
}
/**
* Builds 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 prebuild($related_class, $route = NULL)
{
if (!$this->records) {
return $this;
}
$this->validateSingleClass('prebuild');
// 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);
if ($related_order_bys = fORMRelated::getOrderBys($this->class, $related_class, $route)) {
$order_by_sql .= ', ' . fORMDatabase::createOrderByClause($related_table, $related_order_bys);
}
$new_sql = 'SELECT ' . $related_table . '.*';
// If we are going through a join table we need the related primary key for matching
if (isset($relationship['join_table'])) {
$new_sql .= ", " . $table_with_route . '.' . $relationship['column'];
}
$new_sql .= ' FROM :from_clause ';
$new_sql .= ' WHERE ' . $where_sql;
$new_sql .= ' :group_by_clause ';
$new_sql .= ' ORDER BY ' . $order_by_sql;
$new_sql = fORMDatabase::insertFromAndGroupByClauses($related_table, $new_sql);
// Add the joining column to the group by
if (strpos($new_sql, 'GROUP BY') !== FALSE) {
$new_sql = str_replace(' ORDER BY', ', ' . $table . '.' . $relationship['column'] . ' ORDER BY', $new_sql);
}
// Run the query and inject the results into the records
$result = fORMDatabase::retrieve()->translatedQuery($new_sql);
$total_records = sizeof($this->records);
for ($i = 0; $i < $total_records; $i++) {
// Get the record we are injecting into
$record = $this->records[$i];
$keys = array();
// If we are going through a join table, keep track of the record by the value in the join table
if (isset($relationship['join_table'])) {
try {
$current_row = $result->current();
$keys[$relationship['column']] = $current_row[$relationship['column']];
} catch (fExpectedException $e) {
}
// If it is a straight join, keep track of the value by the related column value
} else {
$method = 'get' . fGrammar::camelize($relationship['related_column'], TRUE);
$keys[$relationship['related_column']] = $record->{$method}();
}
// Loop through and find each row for the current record
$rows = array();
try {
while (!array_diff_assoc($keys, $result->current())) {
$row = $result->fetchRow();
// If we are going through a join table we need to remove the related primary key that was used for matching
if (isset($relationship['join_table'])) {
unset($row[$relationship['column']]);
}
$rows[] = $row;
}
} catch (fExpectedException $e) {
}
// Set up the result object for the new record set
$set = new fRecordSet($related_class, new ArrayIterator($rows));
// Inject the new record set into the record
$method = 'inject' . fGrammar::pluralize($related_class);
$record->{$method}($set, $route);
}
return $this;
}
