Yii2.0 where查询条件组合

Left Join 搜索

TABLE:

Client[c_id, c_name]

Order[o_id, o_client_id, o_amount]

// models/xxxxSearch.php

// 正确代码
$staffs = Client::find();

// left join and search

$staffs->leftJoin('order', 'c_id = o_client_id');

$staffs->andWhere(['c_role_id'=>5]);

 

// 错误代码

$staffs->leftJoin('order', ['c_id' => 'o_client_id']);

条件查询

$staffs = Staff::find()->where($where)->all();

单查询:

sql: (year = 1) and (status = 2)

$where = ['year' => 1, 'status' => 2];

and 条件组合:

sql: id=1 and id=2

$where = ['and', 'id=1', 'id=2'];

sql: year=1 and (id=1 OR id=2)

$where = ['and', 'year=1', ['or', 'id=1', 'id=2']];

sql: year=1 and (id=1 OR id=2) 此写法'='可以换成其他操作符,例:in like != >=等

$where = [
    'and',
    ['=', 'year', 1],
    [
        'or',
        ['>=', 'id', '1'],
        ['=', 'id', '10'],
    ]
];

or 条件:

sql: (year in (7, 8, 9) OR (id in (1, 2, 3)))

$where = ['or', ['year' => [7, 8, 9]], ['id' => [1, 2, 3]];

not条件:

sql: not (attribute IS NULL)

$where = ['not', ['attribute' => null]];

between / not between 用法一样:

sql: id BETWEEN 1 and 10

$where = ['between', 'id', 1, 10]

in / not in 用法一样:

sql: id in (1, 2, 3)

$where = ['in', 'id', [1, 2, 3]] or $where = ['id'=>[1, 2, 3]];

in条件也适用于多字段

$where = ['in', ['id', 'name'], [['id' => 1, 'name' => 'zsf'], ['id' => 2, 'name' => 'lsis']]];

也适用于内嵌sql:句


$where = ['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])];

like:

sql: name LIKE '%zsm%

$where = ['like', 'name', 'zsm'];

sql:name LIKE '%test%' and name LIKE '%sample%'

$where = ['like', 'name', ['test', 'sample']];

sql: name LIKE '%zsm'

$where = ['like', 'name', '%zsm', false];

exists / not exists 用法一样:

sql: EXISTS (SELECT "id" FROM "users" WHERE "active"=1)

$where = ['exists', (new Query())->select('id')->from('users')->where(['active' => 1])];

指定运算符:

sql: id >= 10

$where = ['>=', 'id', 10];

sql: id != 10

$where = ['!=', 'id', 10];

### sql计算字段中相同值重复次数,并排序

$query = static::find()
    ->select(['package_uuid', 'count(*) AS count', 'cost', 'package_shop_id'])
    ->groupBy('package_uuid');
    ->orderBy('count DESC')
    ->limit(10);

where条件中两字段相加或相减

$query->andWhere(['<', '`updated_at` + `duration`', time()])->all();

避免select里面的子查询被识别成字段

$quert = User::find()
    ->select([
          new Expression('count(*) as count , count(distinct mobile) as mnumber')
     ])->asArray()
    ->all();

SQL 随机抽取十名幸运用户

$query = new Query;
$query->select('ID, City,State,StudentName')
      ->from('student')
      ->where(['IsActive' => 1])
      ->andWhere(['not', ['State' => null]])
      ->orderBy(['rand()' => SORT_DESC])
      ->limit(10);

yii2 多表联查 where条件里 A表字段=B表字段怎么表示?

#想在where条件里加上c.type=b.type怎么加?
$res =self::find()
->select(['a.id','a.name'])
->join('LEFT JOIN','b','b.qid=a.id')
->join('LEFT JOIN','c','c.uid=b.uid')
->where(['a.state'=>0, 'b.state'=>0, 'c.state'=>0, 'c.uid'=>123456])
->asArray()->all();

$query->andWhere(new \yii\db\Expression('c.type = b.type'));

输出查询的sql语句

$query = Weibo::find()->joinWith('account')->where([
    'and',
    ['is_forward' => 0],
    ['status' => Weibo::STATUS_NORMAL_WITH_STAT],
    ['account_open_id' => $account_list],
    ['read_limit_time' => null],
])->andWhere("`posted_at` BETWEEN {$now}-`account`.`scrape_time`*60 AND {$now}-`account`.`scrape_time`*60+60");


$commandQuery = clone $query;
// 输出SQL语句
echo $commandQuery->createCommand()->getRawSql();

$weibo = $query->all();

如果要用 find_in_set 需要使用到 Expression 表达式:

User::find()
    ->where(new Expression('FIND_IN_SET(:status, status)'))
    ->addParams([':status' => 1])
    ->all();

字段去重的三种方法

static::find()
->where([
    'user_id' => $user_id,
])
->groupBy('uuid')
->all();

static::find()
->select(['uuid'])
->where([
    'user_id' => $user_id,
])
->distinct()
->count();

static::find()->where([
    'user_id' => $user_id,
])->count('distinct uuid');

查找 auth_times 表 type=1 并且 不存在 auth_item 表里面的数据

// AuthItem.php 关键是 onCondition 方法
public function getAuthTimes()
{
    return $this->hasOne(AuthTimes::className(), ['name' => 'name', ])->onCondition([AuthTimes::tableName() . '.type' => 1]);
}

// AuthTimes.php 文件
// ......
AuthItem::find()->joinWith('authTimes')->where([self::tableName() . '.name' => null])->all();

执行SQL查询并缓存结果

$styleId = Yii::$app->request->get('style');
$collection = Yii::$app->db->cache(function($db) use($styleId){
    return Collection::findOne(['style_id'=>$styleId]);
}, self::SECONDS_IN_MINITUE * 10);

嵌套查询,groupBy 分组之后排序功能

$subQuery = new Query();
$subQuery->from(PostComment::tableName())->where(['status' => PostComment::STATUS_ACTIVE])->orderBy(['created_at' => SORT_DESC]);

$comment = PostComment::find()->from(['tmpA' => $subQuery])
    ->groupBy('post_id')
    ->all();

// SELECT * FROM (SELECT * FROM `post_comment` WHERE `status`=1 ORDER BY `created_at` DESC) `tmpA` GROUP BY `post_id`



导航