链式查询

通过主键获取数据

  1. User::find(100);
  2. //SQL: SELECT * FROM user WHERE id = 100;
  4. User::find(100, 'id, nickname, avatar');
  5. //SQL: SELECT id, nickname, avatar FROM user WHERE id = 100;

查询一条数据

  1. User::where('name', 'Tommy')->row();
  2. //SQL: SELECT * FROM user WHERE name = 'Tommy' LIMIT 1;
  4. User::where('name', 'Tommy')->fields('id, nickname, avatar')->row();
  5. //SQL: SELECT id, nickname, avatar FROM user WHERE name = 'Tommy' LIMIT 1;

获取一列

一维数组

  1. User::where('id', 'in', [22, 68, 33])
  2.     ->column('nickname');
  3. //返回
  4. Array
  5. (
  6.     [0] => Tommy
  7.     [1] => Tomener
  8.     [2] => Yob
  9. )

关联数组

  1. // id作为键，nickname作为值
  2. User::where('id', 'in', [22, 68, 33])
  3.     ->column('nickname', 'id');
  4. //返回    
  5. Array
  6. (
  7.     [22] => Tommy
  8.     [68] => Tomener
  9.     [68] => Yob
  10. )

关联数组（值为数组）

  1. // id作为键，user信息数组作为值
  2. User::where('id', 'in', [22, 68, 33])
  3.     ->column('nickname, avatar', 'id');
  4. //返回
  5. Array
  6. (
  7.     [22] => Array('id' => 22, nickname' => 'Tommy', 'avatar' => '22.jpg'),
  8.     [68] => Array('id' => 68, nickname' => 'Tomener', 'avatar' => '68.jpg'),
  9.     [33] => Array('id' => 33, nickname' => 'Yob', 'avatar' => '33.jpg'),
  10. )

值查询

  1. $nickname = User::where(22)->value('nickname');
  2. var_dump($nickname);
  3. //打印出
  4. string(7) "Tommy"

查询多条数据

  1. User::where(['sex' => 1, 'level' => 5])->select();
  2. //SQL: SELECT * FROM user WHERE sex = 1 AND level = 5;

查询条数限制

  1. User::where(['sex' => 1, 'level' => 5])
  2.     ->limit(10)
  3.     ->select();
  4. //SQL: SELECT * FROM user WHERE sex = 1 AND level = 5 LIMIT 10;

分页

  1. $page = ['limit' => 10, 'p' => 1];
  2. User::where(['sex' => 1, 'level' => 5])
  3.     ->page($page)
  4.     ->select();
  5. //SQL: SELECT * FROM user WHERE sex = 1 AND level = 5 LIMIT 0, 10;

排序

  1. User::where('type', 2)
  2.     ->field('uid, nickname, avatar')
  3.     ->order('created DESC, id DESC')
  4.     ->limit(10)
  5.     ->select();
  6. SQL: SELECT uid, nickname, avatar FROM user WHERE type = 2 ORDER BY created DESC, id DESC LIMIT 10;

分组

  1. User::where(['created' => ['between', 1470000000, 1490000000]])
  2.     ->field('COUNT(*) num, SUM(balance) balance')
  3.     ->group('level')
  4.     ->select();
  5. //SQL: SELECT COUNT(*) num, SUM(balance) balance FROM user where created between 1470000000 AND 1490000000 GROUP BY level;