CRUD maps to four entry points:

select()
update()
insert()
delete()

select()

Returns a two-dimensional array (rows).

// Expected SQL: "SELECT * FROM `user` WHERE (`name`='xiaofang')";
$row_list = m('user')->where(['name' => 'xiaofang'])->select();

find()

Returns a single row (associative array).

$row = m('user')->where(['name' => 'xiaofang'])->find(); // first match

insert($data)

$data is a one-dimensional array of column => value.

// Expected SQL: "INSERT INTO `user` (id,name,age) VALUES ('1','xiaofang','18')";

$data = ['id'=> 1,'name'=>'xiaofang','age'=>'18'];

$id = m('user')->insert($data);

$sql = m('user')->sql(); // last built SQL

Return value: primary key id (if applicable).

update()

// Expected SQL: "UPDATE `user` SET `age`='20' WHERE (`id`='1')";

$effectRows = m('user')->update(['id'=>1,'age'=>20]); // id used as WHERE by default

$effectRows = m('user')->where(['id'=>1])->update(['age'=>20]);

Return value: affected rows.

delete()

// Expected SQL: "DELETE FROM `user` WHERE (`id`='1')";

$effectRows = m('user')->where(['id'=>1])->delete();

Return value: affected rows.

Advanced where clauses

where in

// Expected SQL: "SELECT * FROM `user` WHERE (`id` IN (1,2) AND `age`='20')";
$where = [
    'id' => ['in',[1,2]],
    'age'  => 20,
];
$row = m('user')->where($where)->select();

where between

// Expected SQL: "SELECT * FROM `user` WHERE (`id` BETWEEN '1' AND '2')";
$where = [
    'id' => ['between',[1,2]],
];
m('user')->where($where)->select();

Expression operators

// Expected SQL: "SELECT * FROM `user` WHERE (`id` != '1')";
$where = [
    'id' => ['!=',1],  // matches '/^(=|!=|<|<>|<=|>|>=)$/'
];
m('user')->where($where)->select();

like / not like

// Expected SQL: "SELECT * FROM `user` WHERE (`name` LIKE 'test%')";

$where = [
    'name' => ['like','test%'],
];
m('user')->where($where)->select();

Mixing AND / OR

// Expected SQL: "SELECT * FROM `user` WHERE (`id`='1' OR `name` LIKE 'test%')";
$where = [
    'id' => 1,
    'name' => ['like','test%'],
    '_logic' => 'OR', // default connector is AND
];
m('user')->where($where)->select();

Nested conditions

// Expected SQL: "SELECT * FROM `user` WHERE ((`name` LIKE 'zhang%'OR`name` LIKE 'li%') AND ((`id`='1' OR `age` IN (18,20))))";

$where_complex = [
    'id' => 1,
    'age' => ['in',[18,20]],
    '_logic' => 'OR',
];

$where = [
    'name' => ['like', ['zhang%','li%'],'OR'],
    '_complex' => [
        $where_complex
    ],
];
m('user')->where($where)->select();

Raw SQL

query() (reads)

$expectSql = "SELECT * FROM `user` WHERE (`name`='test')";
m('user')->query($expectSql);

exec() (writes)

$expectSql = "UPDATE `user` set `age`='20' WHERE (`id`='1')";
m('user')->exec($expectSql);

Bulk insert

insert_multi($datas, $chunk_size = 1000) splits rows into chunks.

// Expected SQL: "INSERT INTO `user` (name,age,company) VALUES ('lisi',21,'baidu'),('zhsa',28,'baidu')";
$datas = [
    ['name' => 'lisi', 'age' => 21, 'company' => 'baidu'],
    ['name' => 'zhsa', 'age' => 28, 'company' => 'tencent'],
];
$id = m('user')->insert_multi($datas); // last insert id

count()

// Expected SQL: "SELECT count(*) as num FROM `user` where `age`=21";
$map = ['age' => 21];
$sum = m('user')->where($map)->count();

Increment / decrement

/**
 * @param string $field column name
 * @param int $num delta
 * @return int affected rows
 */
set_increase($field, $num)

// Expected SQL: "UPDATE `user` SET `age`=`age`+1 WHERE (`id`='1')";
$data = m('user')->where(['id'=>1])->set_increase('age',1);

/**
 * @param string $field column name
 * @param int $num delta
 * @return int affected rows
 */
set_decrease($field, $num)

// Expected SQL: "UPDATE `user` SET `age`=`age`-1 WHERE (`id`='1')";
$data = m('user')->where(['id'=>1])->set_decrease('age',1);

Advanced chains

Combine distinct, field, join, having, where, limit, order, group, etc.

// Expected SQL: "
//     SELECT DISTINCT id,age,name
//     FROM `user`
//     INNER JOIN company on company.user_id = user.id
//     WHERE (`id`='1') GROUP BY name
//     HAVING id < 100
//     ORDER BY id desc
//     LIMIT 0,1";

$rows = m('user')
    ->distinct("id")
    ->field("id,age,name")
    ->join("company on company.user_id = user.id")
    ->where(['id'=>1])
    ->group('name')
    ->having("id < 100")
    ->order('id desc')
    ->limit(1)
    ->select();

$sql = model('user')->sql(); // SQL string for the built query