其他条件

在DAO或者选择器里都可以调用条件方法，方法可传递式调用，相同方法内的条件会自动合并

其中包括group，addition，order，limit，having

  1. // SELECT `user`.`id`, avg(`user`.`cash`) AS 'a_c' FROM `TABLE` `user` WHERE ...
  2.         GROUP BY `user`.`id`,`user`.`type` HAVING `a_c` >= 1000 ORDER BY `a_c` DESC, `id` ASC LIMIT 20,10;
  3. $this->userDAO //->filter(...)
  4.     ->addition(array('avg'=>array('cash'=>'a_c'))
  5.     ->group(array('id', 'type'))
  6.     ->having(array('>='=>array('a_c'=> 1000)))
  7.     ->order(array('a_c'=>'DESC', 'id'=>'ASC'))
  8.     // limit 第一个参数为取的条数，第二个参数为起始位置（默认为0）
  9.     ->limit(10, 20)
  10.     ->query(array('id'));

Biny v2.9.2之后支持自定义排序（order by field），随机排序（order by rand）逻辑，同时也支持了order方法多次调用

  1. // ... ORDER BY `type` asc,FIELD(`status`,'1','9','3','6'),RAND()
  2. $this->userDAO->order(['type'=>'asc', 'status'=>[1,9,3,6]])
  3.     ->order('rand')->query();

addition是对数据做计算处理的方法，提供了max，count，sum，min，avg等计算方法

多联表时同样需要用到二维数组

  1. // SELECT avg(`user`.`cash`) AS 'a_c', avg(`user`.`time`) AS 'time',
  2.         sum(`user`.`cash`) AS 'total', min(`test`.`testid`) AS 'testid'
  3.         FROM `TABLE1` `user` join `TABLE2` `test` ON `user`.`id` = `test`.`user_id` WHERE ...
  4.         GROUP BY `user`.`id`,`user`.`type` HAVING `a_c` >= 1000 ORDER BY `a_c` DESC, `id` ASC LIMIT 0,10;
  5. $DAO = $this->userDAO->join($this->testDAO, array('id'=>'user_id'))
  6. $DAO //->filter(...)
  7.     ->addition(array(
  8.         array(
  9.             'avg'=>array('cash'=>'a_c', 'time'),
  10.             'sum'=>array('cash'=>'total'),
  11.         ),
  12.         array(
  13.             'min'=>array('testid'),
  14.         ),
  15.     )->query();

每次添加条件后都是独立的，不会影响原DAO 或者 选择器，可以放心的使用

  1. // 这个对象不会因添加条件而变化
  2. $filter = $this->userDAO->filter(array('id'=>array(1,2,3,'test')));
  3. // 2
  4. $count = $filter->limit(2)->count()
  5. // 4
  6. $count = $filter->count()
  7. // 100 (user表总行数)
  8. $count = $this->userDAO->count()

Biny同时也可以使用Database::field()来支持复杂的Group By语句，例如：

  1. // SELECT FROM_UNIXTIME(time,'%Y-%m-%d') AS time, count(*) AS 'count'
  2.                 FROM `user` Group By FROM_UNIXTIME(time,'%Y-%m-%d')
  3. $result = $this->userDAO->group(Database::field("FROM_UNIXTIME(time,'%Y-%m-%d')"))
  4.     ->addition(array('count'=>'*'))
  5.     ->query("FROM_UNIXTIME(time,'%Y-%m-%d') AS time");