/** * 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; }