链式操作

 


 

一、获取一条记录

 

$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