PHP fORMDatabase::splitHavingConditions方法代码示例

//.........这里部分代码省略.........
  * '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);
 }

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

//.........这里部分代码省略.........
  * '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);
 }