예제 #1
0
 /**
  * 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();
 }
예제 #2
0
 /**
  * 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);
 }