PHP fORMDatabase::createWhereClause方法代码示例

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