/**
* Creates the objects for related records that are in a one-to-one or many-to-one relationship with the current class in a single DB query
*
* @param string $related_class This should be the name of a related class
* @param string $route This should be the column name of the foreign key 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 precreate($related_class, $route = NULL)
{
if (!$this->records) {
return $this;
}
$this->validateSingleClass('precreate');
// 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);
$relationship = fORMSchema::getRoute(fORMSchema::retrieve($this->class), fORM::tablize($this->class), fORM::tablize($related_class), $route, '*-to-one');
$values = $this->call('get' . fGrammar::camelize($relationship['column'], TRUE));
$values = array_unique($values);
self::build($related_class, array($relationship['related_column'] . '=' => $values));
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;
}
/**
* Creates an fRecordSet by specifying the class to create plus the where conditions and order by rules
*
* The where conditions array can contain `key => value` entries in any of
* the following formats:
*
* {{{
* 'column=' => VALUE, // column = VALUE
* 'column!' => VALUE // column <> VALUE
* 'column!=' => VALUE // column <> VALUE
* 'column<>' => VALUE // column <> VALUE
* 'column~' => VALUE // column LIKE '%VALUE%'
* 'column!~' => VALUE // column NOT LIKE '%VALUE%'
* 'column<' => VALUE // column < VALUE
* 'column<=' => VALUE // column <= VALUE
* 'column>' => VALUE // column > VALUE
* 'column>=' => VALUE // column >= VALUE
* 'column=' => array(VALUE, VALUE2, ... ) // column IN (VALUE, VALUE2, ... )
* 'column!' => array(VALUE, VALUE2, ... ) // column NOT IN (VALUE, VALUE2, ... )
* 'column!=' => array(VALUE, VALUE2, ... ) // column NOT IN (VALUE, VALUE2, ... )
* 'column<>' => array(VALUE, VALUE2, ... ) // column NOT IN (VALUE, VALUE2, ... )
* 'column~' => array(VALUE, VALUE2, ... ) // (column LIKE '%VALUE%' OR column LIKE '%VALUE2%' OR column ... )
* 'column&~' => array(VALUE, VALUE2, ... ) // (column LIKE '%VALUE%' AND column LIKE '%VALUE2%' AND column ... )
* 'column!~' => array(VALUE, VALUE2, ... ) // (column NOT LIKE '%VALUE%' AND column NOT LIKE '%VALUE2%' AND column ... )
* 'column!|column2<|column3=' => array(VALUE, VALUE2, VALUE3) // (column <> '%VALUE%' OR column2 < '%VALUE2%' OR column3 = '%VALUE3%')
* 'column|column2><' => array(VALUE, VALUE2) // WHEN VALUE === NULL: ((column2 IS NULL AND column = VALUE) OR (column2 IS NOT NULL AND column <= VALUE AND column2 >= VALUE))
* // WHEN VALUE !== NULL: ((column <= VALUE AND column2 >= VALUE) OR (column >= VALUE AND column <= VALUE2))
* 'column|column2|column3~' => VALUE // (column LIKE '%VALUE%' OR column2 LIKE '%VALUE%' OR column3 LIKE '%VALUE%')
* 'column|column2|column3~' => array(VALUE, VALUE2, ... ) // ((column LIKE '%VALUE%' OR column2 LIKE '%VALUE%' OR column3 LIKE '%VALUE%') AND (column LIKE '%VALUE2%' OR column2 LIKE '%VALUE2%' OR column3 LIKE '%VALUE2%') AND ... )
* }}}
*
* When creating a condition in the form `column|column2|column3~`, if the
* value for the condition is a single string that contains spaces, the
* string will be parsed for search terms. The search term parsing will
* handle quoted phrases and normal words and will strip punctuation and
* stop words (such as "the" and "a").
*
* The order bys array can contain `key => value` entries in any of the
* following formats:
*
* {{{
* 'column' => 'asc' // 'first_name' => 'asc'
* 'column' => 'desc' // 'last_name' => 'desc'
* 'expression' => 'asc' // "CASE first_name WHEN 'smith' THEN 1 ELSE 2 END" => 'asc'
* 'expression' => 'desc' // "CASE first_name WHEN 'smith' THEN 1 ELSE 2 END" => 'desc'
* }}}
*
* The column in both the where conditions and order bys can be in any of
* the formats:
*
* {{{
* 'column' // e.g. 'first_name'
* 'current_table.column' // e.g. 'users.first_name'
* 'related_table.column' // e.g. 'user_groups.name'
* 'related_table{route}.column' // e.g. 'user_groups{user_group_id}.name'
* 'related_table=>once_removed_related_table.column' // e.g. 'user_groups=>permissions.level'
* 'related_table{route}=>once_removed_related_table.column' // e.g. 'user_groups{user_group_id}=>permissions.level'
* 'related_table=>once_removed_related_table{route}.column' // e.g. 'user_groups=>permissions{read}.level'
* 'related_table{route}=>once_removed_related_table{route}.column' // e.g. 'user_groups{user_group_id}=>permissions{read}.level'
* 'column||other_column' // e.g. 'first_name||last_name' - this concatenates the column values
* }}}
*
* In addition to using plain column names for where conditions, it is also
* possible to pass an aggregate function wrapped around a column in place
* of a column name, but only for certain comparison types:
*
* {{{
* 'function(column)=' => VALUE, // function(column) = VALUE
* 'function(column)!' => VALUE // function(column) <> VALUE
* 'function(column)!= => VALUE // function(column) <> VALUE
* 'function(column)<>' => VALUE // function(column) <> VALUE
* 'function(column)~' => VALUE // function(column) LIKE '%VALUE%'
* 'function(column)!~' => VALUE // function(column) NOT LIKE '%VALUE%'
* 'function(column)<' => VALUE // function(column) < VALUE
* 'function(column)<=' => VALUE // function(column) <= VALUE
* 'function(column)>' => VALUE // function(column) > VALUE
* 'function(column)>=' => VALUE // function(column) >= VALUE
* 'function(column)=' => array(VALUE, VALUE2, ... ) // function(column) IN (VALUE, VALUE2, ... )
* 'function(column)!' => array(VALUE, VALUE2, ... ) // function(column) NOT IN (VALUE, VALUE2, ... )
* 'function(column)!=' => array(VALUE, VALUE2, ... ) // function(column) NOT IN (VALUE, VALUE2, ... )
* 'function(column)<>' => array(VALUE, VALUE2, ... ) // function(column) NOT IN (VALUE, VALUE2, ... )
* }}}
*
* The aggregate functions `AVG()`, `COUNT()`, `MAX()`, `MIN()` and
* `SUM()` are supported across all database types.
*
* Below is an example of using where conditions and order bys. Please note
* that values should **not** be escaped for the database, but should just
* be normal PHP values.
*
* {{{
* #!php
* return fRecordSet::build(
* 'User',
* array(
* 'first_name=' => 'John',
* 'status!' => 'Inactive',
* 'groups.group_id=' => 2
* ),
* array(
* 'last_name' => 'asc',
* 'date_joined' => 'desc'
* )
* );
* }}}
*
* @param string $class The class to create the fRecordSet of
* @param array $where_conditions The `column => value` comparisons for the `WHERE` clause
* @param array $order_bys The `column => direction` values to use for the `ORDER BY` clause
* @param integer $limit The number of records to fetch
* @param integer $page The page offset to use when limiting records
* @return fRecordSet A set of fActiveRecord objects
*/
public static function build($class, $where_conditions = array(), $order_bys = array(), $limit = NULL, $page = NULL)
{
self::validateClass($class);
// Ensure that the class has been configured
fActiveRecord::forceConfigure($class);
$table = fORM::tablize($class);
$sql = "SELECT " . $table . ".* FROM :from_clause";
if ($where_conditions) {
$having_conditions = fORMDatabase::splitHavingConditions($where_conditions);
$sql .= ' WHERE ' . fORMDatabase::createWhereClause($table, $where_conditions);
}
$sql .= ' :group_by_clause ';
if ($where_conditions && $having_conditions) {
$sql .= ' HAVING ' . fORMDatabase::createHavingClause($having_conditions);
}
if ($order_bys) {
$sql .= ' ORDER BY ' . fORMDatabase::createOrderByClause($table, $order_bys);
// If no ordering is specified, order by the primary key
} elseif ($primary_keys = fORMSchema::retrieve()->getKeys($table, 'primary')) {
$expressions = array();
foreach ($primary_keys as $primary_key) {
$expressions[] = $table . '.' . $primary_key . ' ASC';
}
$sql .= ' ORDER BY ' . join(', ', $expressions);
}
$sql = fORMDatabase::insertFromAndGroupByClauses($table, $sql);
// Add the limit clause and create a query to get the non-limited total
$non_limited_count_sql = NULL;
if ($limit !== NULL) {
$primary_key_fields = fORMSchema::retrieve()->getKeys($table, 'primary');
$primary_key_fields = fORMDatabase::addTableToValues($table, $primary_key_fields);
$non_limited_count_sql = str_replace('SELECT ' . $table . '.*', 'SELECT ' . join(', ', $primary_key_fields), $sql);
$non_limited_count_sql = 'SELECT count(*) FROM (' . $non_limited_count_sql . ') AS sq';
$sql .= ' LIMIT ' . $limit;
if ($page !== NULL) {
if (!is_numeric($page) || $page < 1) {
throw new fProgrammerException('The page specified, %s, is not a number or less than one', $page);
}
$sql .= ' OFFSET ' . ($page - 1) * $limit;
}
}
return new fRecordSet($class, fORMDatabase::retrieve()->translatedQuery($sql), $non_limited_count_sql);
}
