链式操作

一、获取一条记录

  1. $db = Db::connect('timo');
  2. $user = $db->table('user')->where('id', 394835)->row();
  4. //指定字段
  5. $user = $db
  6.     ->table('user')
  7.     ->fields('id, name, avatar')
  8.     ->where(['id' => 394835])
  9.     ->row();

二、获取多条记录

获取全部

  1. $users = $db
  2.     ->table('user')
  3.     ->where(['certified' => 5, 'grade' => ['between', 2, 5]])
  4.     ->select();

限制条数

  1. $users = $db->table('user')->where('id', '>', 5)->limit(10)->select();

三、分页获取

  1. $page = ['p' => 1, 'limit' => 20];
  2. $users = $db
  3.     ->table('user')
  4.     ->where(['certified' => 5, 'grade' => ['between', 2, 5]])
  5.     ->order('id DESC')
  6.     ->page($page)
  7.     ->select();
  8. return App::result(0, 'ok', [
  9.     'users' => $users,
  10.     'page' => $page
  11. ]);

page说明

page里面的参数是引用传递，查询之后$page会在原来的基础上增加两个元素：

  1. $page == [
  2.     'p' => 1,
  3.     'limit' => 10,
  4.     'total' => 298,
  5.     'total_page' => 30
  6. ]

四、排序

  1. $users = $db
  2.     ->table('user')
  3.     ->where('certified', 5)
  4.     ->where('grade', 'between', [2, 5])
  5.     ->order('grade DESC, id DESC')
  6.     ->select();

五、分组

  1. $users = $db
  2.     ->table('user')
  3.     ->fields('grade, count(*) num')
  4.     ->where('certified', 5)
  5.     ->where('grade', 'between', [2, 5])
  6.     ->group('grade')
  7.     ->select();

六、获取一列

返回某列值的数组

  1. $users = $db
  2.     ->table('user')
  3.     ->where('certified', 5)
  4.     ->where('grade', 'between', [2, 5])
  5.     ->column('nickname');
  7. //返回值如：
  8. Array
  9. (
  10.     [0] => timo900
  11.     [1] => timor
  12.     [2] => oky
  13. )

返回以某个字段的值作为键，指定字段的值作为值的数组

  1. $users = $db
  2.     ->table('user')
  3.     ->where('certified', 5)
  4.     ->where('grade', 'between', [2, 5])
  5.     ->column('nickname', id);
  7. //返回值如：
  8. Array
  9. (
  10.     [1] => timo
  11.     [3] => tommy
  12.     [4] => oky
  13. )

返回以某个字段的值作为键的二维数组

  1. $users = $db
  2.     ->table('user')
  3.     ->where('certified', 5)
  4.     ->where('grade', 'between', [2, 5])
  5.     ->column('nickname, avatar', id);
  7. //返回值如：
  8. Array
  9. (
  10.     [1] => Array
  11.         (
  12.             [id] => 1
  13.             [nickname] => timo
  14.             [avatar] => 1234567890.jpg
  15.         )
  17.     [3] => Array
  18.         (
  19.             [id] => 3
  20.             [nickname] => tommy
  21.             [avatar] => b344.jpg
  22.         )
  24.     [4] => Array
  25.         (
  26.             [id] => 4
  27.             [nickname] => oky
  28.             [avatar] => 3984N.jpg
  29.         )
  30. )

七、获取值

  1. $nickname = UserModel::where('id', 3)->value('nickname');
  2. //返回值如：tommy

八、关联查询

  1. join($table, $condition, $type = 'LEFT')
  2. INNER JOIN
  3. LEFT JOIN
  4. RIGHT JOIN
  5. FULL JOIN
  6. 第三个参数控制，默认LEFT JOIN，
  1. $user = $db->table('user')->alias('u')
  2.     ->fields('u.id, u.nickname, i.school')
  3.     ->join('user_info i', 'u.id = i.uid')
  4.     ->where('u.id', '<', 3)
  5.     ->select();
  6. var_dump($db->getLastSql());
  7. // SELECT u.id, u.nickname, i.school FROM `user` u LEFT JOIN user_info i ON u.id = i.uid WHERE `u`.id < 3