链式操作

一、获取一条记录

$db = Db::connect('timo');
$user = $db->table('user')->where('id', 394835)->row();

//指定字段
$user = $db
    ->table('user')
    ->fields('id, name, avatar')
    ->where(['id' => 394835])
    ->row();

二、获取多条记录

获取全部

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

限制条数

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

三、分页获取

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

page说明

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

$page == [
    'p' => 1,
    'limit' => 10,
    'total' => 298,
    'total_page' => 30
]

四、排序

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

五、分组

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

六、获取一列

返回某列值的数组

$users = $db
    ->table('user')
    ->where('certified', 5)
    ->where('grade', 'between', [2, 5])
    ->column('nickname');
    
//返回值如：
Array
(
    [0] => timo900
    [1] => timor
    [2] => oky
)

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

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

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

$users = $db
    ->table('user')
    ->where('certified', 5)
    ->where('grade', 'between', [2, 5])
    ->column('nickname, avatar', id);
    
//返回值如：
Array
(
    [1] => Array
        (
            [id] => 1
            [nickname] => timo
            [avatar] => 1234567890.jpg
        )

    [3] => Array
        (
            [id] => 3
            [nickname] => tommy
            [avatar] => b344.jpg
        )

    [4] => Array
        (
            [id] => 4
            [nickname] => oky
            [avatar] => 3984N.jpg
        )
)

七、获取值

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

八、关联查询

join($table, $condition, $type = 'LEFT')
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
第三个参数控制，默认LEFT JOIN，

$user = $db->table('user')->alias('u')
    ->fields('u.id, u.nickname, i.school')
    ->join('user_info i', 'u.id = i.uid')
    ->where('u.id', '<', 3)
    ->select();
var_dump($db->getLastSql());
// SELECT u.id, u.nickname, i.school FROM `user` u LEFT JOIN user_info i ON u.id = i.uid WHERE `u`.id < 3