PHP fORMDatabase::insertFromAndGroupByClauses方法代码示例

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

 /**
  * Counts the number of related one-to-many or many-to-many records
  * 
  * @internal
  * 
  * @param  string $class             The class to get the related values for
  * @param  array  &$values           The values for the fActiveRecord class
  * @param  array  &$related_records  The related records existing for the fActiveRecord class
  * @param  string $related_class     The class that is related to the current record
  * @param  string $route             The route to follow for the class specified
  * @return integer  The number of related records
  */
 public static function countRecords($class, &$values, &$related_records, $related_class, $route = NULL)
 {
     $table = fORM::tablize($class);
     $related_table = fORM::tablize($related_class);
     $route = fORMSchema::getRouteName($table, $related_table, $route, '*-to-many');
     // If we already have the sequence, we can stop here
     if (isset($related_records[$related_table][$route]['count'])) {
         return $related_records[$related_table][$route]['count'];
     }
     $relationship = fORMSchema::getRoute($table, $related_table, $route, '*-to-many');
     // Determine how we are going to build the sequence
     if ($values[$relationship['column']] === NULL) {
         $count = 0;
     } else {
         $column = $table . '.' . $relationship['column'];
         $value = fORMDatabase::escapeBySchema($table, $relationship['column'], $values[$relationship['column']], '=');
         $primary_keys = fORMSchema::retrieve()->getKeys($related_table, 'primary');
         $primary_keys = fORMDatabase::addTableToValues($related_table, $primary_keys);
         $primary_keys = join(', ', $primary_keys);
         $sql = "SELECT count(" . $primary_keys . ") AS __flourish_count ";
         $sql .= "FROM :from_clause ";
         $sql .= "WHERE " . $column . $value;
         $sql .= ' :group_by_clause ';
         $sql .= 'ORDER BY ' . $column . ' ASC';
         $sql = fORMDatabase::insertFromAndGroupByClauses($table, $sql);
         $result = fORMDatabase::retrieve()->translatedQuery($sql);
         $count = $result->valid() ? (int) $result->fetchScalar() : 0;
     }
     self::setCount($class, $related_records, $related_class, $count, $route);
     return $count;
 }