我是Yii-2框架的新手。如何使用activeQuery和models在Yii-2框架中实现以下查询。
SELECT * FROM users AS u WHERE u.user_id IN(1,5,8) AND (u.status = 1 OR u.verified = 1) OR (u.social_account = 1 AND u.enable_social = 1)
谢谢
我是Yii-2框架的新手。如何使用activeQuery和models在Yii-2框架中实现以下查询。
SELECT * FROM users AS u WHERE u.user_id IN(1,5,8) AND (u.status = 1 OR u.verified = 1) OR (u.social_account = 1 AND u.enable_social = 1)
谢谢
你可以尝试这个:
//SELECT * FROM users AS u WHERE u.user_id IN(1,5,8) AND (u.status = 1 OR u.verified = 1) OR (u.social_account = 1 AND u.enable_social = 1)
$model = arname()->find()
->andWhere(['user_id'=>[1,5,8]])
->andWhere(['or',
['status'=>1],
['verified'=>1]
])
->orWhere(['and',
['social_account'=>1],
['enable_social'=>1]
])
->all();
试一下这个 -
$query = (new \yii\db\Query())
->select('*')
->from('users u')
->where(['and',['u.user_id'=>[1,5,8]],['or','u.status=1','u.verified=1']])
->orWhere(['u.social_account'=>1,'u.enable_social'=>1]);
$command = $query->createCommand();
print_r ($command->sql);die;
我假设你已经了解Yii 2.0中的数据库配置,这基本上与Yii 1.0版本相同。
如果您想使用activeQuery,您需要先定义一个'USERS'类:
<?php
namespace app\models;
use yii\db\ActiveRecord;
class USERS extends ActiveRecord {
public static function tableName()
{
return 'users';
}
}
?>
当您使用它时,可以按照以下方式编写:
<?
$usr_data = USERS::find()->
->where("user_id IN(1,5,8) AND (status = 1 OR verified = 1) OR (social_account = 1 AND enable_social = 1)")
->all();
?>
在我看来,主动查询为您提供了通过子块分离 SQL 的方法。但是当您有如此复杂的“AND OR” WHERE 条件时,应用它没有任何意义。
User::find()
->select('u_id , u_unique_id, u_first_name, u_last_name, u_username, u_email, u_image,u_phone')
->andWhere("u_status != 2 AND u_id != 1 and u_role in (6,7)")
->andWhere(
['or',
['like', 'u_first_name', $searchVal],
['like', 'u_last_name', $searchVal],
['like', 'u_username', $searchVal],
['like', 'u_email', $searchVal]
]
)
->all();
您也可以这样做:
$data = model::find()
->andWhere('plz = :plz',[':plz' => 40])
->andWhere('plz = :plz',[':plz' => 40000])
->all();
$data = model::find()->andWhere(['plz' => 40])->andWhere(['plz' => 40000])->all();
- Anton Rybalko使用 MongoDB
:
$query->andWhere(['and',
['$eq', 'status', 1],
['$in', 'activity', [1, 2]],
]);
$query->orWhere(['$in', 'yourField', $yourArray]);
已测试。类似于数据库管理系统。
首先使用OR条件。例如:
使用OR条件的示例:
(new \yii\db\Query())
->select(['id', 'client', 'ts', 'action'])
->from('log_client as log')
->orWhere(['action' => 'lock'])
->orWhere(['action' => 'rel'])
->andWhere(['in', 'client', $IDs])
->orderBy(['ts' => SORT_ASC])
->all();
ActiveQuery
的 where()
函数也接受字符串作为其第一个参数,该参数将用于查询的 WHERE
条件。因此,最简单的方法是将这些条件放在 where()
函数中。
假设您正在使用 ActiveRecord
模型,您可以在模型中执行以下操作。
$this->find()
->where("user_id IN(1,5,8) AND (status = 1 OR verified = 1) OR (social_account = 1 AND enable_social = 1)")
->all();
findOne()
中使用它? - Moeez->one()
而不是->all()
。 - Boolean_Type