/** * Counts the number of records that match the conditions specified * * @param string $class The class of records to count * @param mixed $where_conditions An array of where clause parameters in the same format as ::build() * @return integer The number of records */ public static function tally($class, $where_conditions = array()) { fActiveRecord::validateClass($class); fActiveRecord::forceConfigure($class); $db = fORMDatabase::retrieve($class, 'read'); $schema = fORMSchema::retrieve($class); $table = fORM::tablize($class); $pk_columns = array(); foreach ($schema->getKeys($table, 'primary') as $pk_column) { $pk_columns[] = $table . '.' . $pk_column; } $params = array($db->escape("SELECT COUNT(*) FROM (SELECT %r FROM :from_clause", $pk_columns)); if ($where_conditions) { $having_conditions = fORMDatabase::splitHavingConditions($where_conditions); } else { $having_conditions = NULL; } if ($where_conditions) { $params[0] .= ' WHERE '; $params = fORMDatabase::addWhereClause($db, $schema, $params, $table, $where_conditions); } $params[0] .= ' :group_by_clause '; if ($having_conditions) { $params[0] .= ' HAVING '; $params = fORMDatabase::addHavingClause($db, $schema, $params, $table, $having_conditions); } $params[0] .= ') subquery'; $params = fORMDatabase::injectFromAndGroupByClauses($db, $schema, $params, $table); return call_user_func_array($db->translatedQuery, $params)->fetchScalar(); }
/** * 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=:' => 'other_column' // column = other_column * 'column!:' => 'other_column' // column <> other_column * 'column!=:' => 'other_column' // column <> other_column * 'column<>:' => 'other_column' // column <> other_column * 'column<:' => 'other_column' // column < other_column * 'column<=:' => 'other_column' // column <= other_column * 'column>:' => 'other_column' // column > other_column * 'column>=:' => 'other_column' // column >= other_column * '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. //Note that for * column comparisons, the function may be placed on either column or both.// * * {{{ * '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)=:' => 'other_column' // function(column) = other_column * 'function(column)!:' => 'other_column' // function(column) <> other_column * 'function(column)!=:' => 'other_column' // function(column) <> other_column * 'function(column)<>:' => 'other_column' // function(column) <> other_column * 'function(column)<:' => 'other_column' // function(column) < other_column * 'function(column)<=:' => 'other_column' // function(column) <= other_column * 'function(column)>:' => 'other_column' // function(column) > other_column * 'function(column)>=:' => 'other_column' // function(column) >= other_column * '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) { fActiveRecord::validateClass($class); fActiveRecord::forceConfigure($class); $db = fORMDatabase::retrieve($class, 'read'); $schema = fORMSchema::retrieve($class); $table = fORM::tablize($class); $params = array($db->escape("SELECT %r.* FROM :from_clause", $table)); if ($where_conditions) { $having_conditions = fORMDatabase::splitHavingConditions($where_conditions); } else { $having_conditions = NULL; } if ($where_conditions) { $params[0] .= ' WHERE '; $params = fORMDatabase::addWhereClause($db, $schema, $params, $table, $where_conditions); } $params[0] .= ' :group_by_clause '; if ($having_conditions) { $params[0] .= ' HAVING '; $params = fORMDatabase::addHavingClause($db, $schema, $params, $table, $having_conditions); } // If no ordering is specified, order by the primary key if (!$order_bys) { $order_bys = array(); foreach ($schema->getKeys($table, 'primary') as $pk_column) { $order_bys[$table . '.' . $pk_column] = 'ASC'; } } $params[0] .= ' ORDER BY '; $params = fORMDatabase::addOrderByClause($db, $schema, $params, $table, $order_bys); $params = fORMDatabase::injectFromAndGroupByClauses($db, $schema, $params, $table); // Add the limit clause and create a query to get the non-limited total $non_limited_count_sql = NULL; if ($limit !== NULL) { $pk_columns = array(); foreach ($schema->getKeys($table, 'primary') as $pk_column) { $pk_columns[] = $table . '.' . $pk_column; } $non_limited_count_sql = str_replace($db->escape('SELECT %r.*', $table), $db->escape('SELECT %r', $pk_columns), $params[0]); $non_limited_count_sql = preg_replace('#\\s+ORDER BY.*$#', '', $non_limited_count_sql); $non_limited_count_sql = $db->escape('SELECT count(*) FROM (' . $non_limited_count_sql . ') subquery', array_slice($params, 1)); $params[0] .= ' LIMIT ' . $limit; if ($page !== NULL) { if (!is_numeric($page) || $page < 1) { $page = 1; } $params[0] .= ' OFFSET ' . ($page - 1) * $limit; } } return new fRecordSet($class, call_user_func_array($db->translatedQuery, $params), $non_limited_count_sql); }