/ code-snippet  

Yii2 Code Snippet

gii CLI

php yii help gii/mode

php yii gii/model --generateLabelsFromComments=1 --overwrite=1 --standardizeCapitals=1 --ns='app\models\gii' --tableName="*"

# 多数据库
php yii gii/model --generateLabelsFromComments=1 --overwrite=1 --standardizeCapitals=1 --db="hub_db" --ns='app\models\hub\gii' --tableName="*"

连接数据库时设置时区

'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=mysql;port=3306;dbname=hub',
'username' => 'root',
'password' => 'root',
'charset' => 'utf8',

// 关闭日志记录，防止被 logs 平台拿走
'enableLogging' => YII_DEBUG ? true : false,
'enableProfiling' => YII_DEBUG ? true : false,

// 设置时区
'on afterOpen' => static function ($event) {
  // set 'Asia/Bangkok' timezone
  $event->sender->createCommand("SET time_zone='+08:00';")->execute();
},

ActiveRecord one

yii\db\ActiveRecord::findOne() 和 yii\db\ActiveQuery::one() 都不会添加 LIMIT 1 到 生成的 SQL 语句中。如果你的查询会返回很多行的数据， 你明确的应该加上 limit(1) 来提高性能，比如 Customer::find()->limit(1)->one()。

DB where

  • 字符串格式，例如：'status=1'
  • 哈希格式，例如： ['status' => 1, 'type' => 2]
  • 操作符格式，例如：['like', 'name', 'test']
  • 对象格式，例如：new LikeCondition('name', 'LIKE', 'test')

简单条件

// SQL: (type = 1) AND (status = 2).
$cond = ['type' => 1, 'status' => 2]

// SQL: (id IN (1, 2, 3)) AND (status = 2)
$cond = ['id' => [1, 2, 3], 'status' => 2]

// SQL: status IS NULL
$cond = ['status' => null]

AND OR

// SQL: `id=1 AND id=2`
$cond = ['and', 'id=1', 'id=2']

// SQL: `type=1 AND (id=1 OR id=2)`
$cond = ['and', 'type=1', ['or', 'id=1', 'id=2']]

// SQL: `type=1 AND (id=1 OR id=2)`
// 此写法 '=' 可以换成其他操作符，例：in like != >= 等
$cond = [
    'and',
    ['=', 'type', 1],
    [
        'or',
        ['=', 'id', '1'],
        ['=', 'id', '2'],
    ]
]

NOT

// SQL: `NOT (attribute IS NULL)`
$cond = ['not', ['attribute' => null]]

BETWEEN

// not between 用法相同
// SQL: `id BETWEEN 1 AND 10`
$cond = ['between', 'id', 1, 10]

IN

// not in 用法相同
// SQL: `id IN (1, 2, 3)`
$cond = ['between', 'id', 1, 10]
$cond = ['id' => [1, 2, 3]]

// IN 条件也适用于多字段
// SQL: (`id`, `name`) IN ((1, 'foo'), (2, 'bar'))
$cond = ['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']]]

// 也适用于内嵌 SQL 语句
$cond = ['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])]

LIKE

// SQL: `name LIKE '%tester%'`
$cond = ['like', 'name', 'tester']

// SQL: `name LIKE '%test%' AND name LIKE '%sample%'`
$cond = ['like', 'name', ['test', 'sample']]

// SQL: `name LIKE '%tester'`
$cond = ['like', 'name', '%tester', false]

EXIST

// not exists用法类似
// SQL: EXISTS (SELECT "id" FROM "users" WHERE "active"=1)
$cond = ['exists', (new Query())->select('id')->from('users')->where(['active' => 1])]

References

– EOF –