OPERATORS
$postsGreaterThan = Post::find()->where(['>', 'created_at', '2016-01-25'])->all();
// SELECT * FROM post WHERE created_at > '2016-01-25'
$postsLessThan = Post::find()->where(['<', 'created_at', '2016-01-25'])->all();
// SELECT * FROM post WHERE created_at < '2016-01-25'
$postsNotEqual = Post::find()->where(['<>', 'created_at', '2016-01-25'])->all();
// SELECT * FROM post WHERE created_at <> '2016-01-25'
IN
$postsInArray = Post::find()->where(['id' => [1,2,3]])->all();
// SELECT * FROM post WHERE id IN (1,2,3)
BETWEEN
$postsInBetween = Post::find()
->where(['between', 'date', "2015-06-21", "2015-06-27" ])
->all();
NULL
$postsWithNullTitle = Post::find()->where(['title' => null]);
// SELECT * FROM post WHERE title IS NULL
AND
$postsAND = Post::find()->where(['title' => null, 'body' => null]);
// SELECT * FROM post WHERE title IS NULL AND body IS NULL
OR
$postsAND = Post::find()->where(['OR', 'title IS NULL', 'body IS NULL']);
// SELECT * FROM post WHERE title IS NULL OR body IS NULL
NOT
$postsNotEqual = Post::find()->where(['NOT', ['created_at'=>'2016-01-25']])->all();
// SELECT * FROM post WHERE created_at IS NOT '2016-01-25'
NESTED CLAUSES
$postsNestedWhere = Post::find()->andWhere([
'or',
['title' => null],
['body' => null]
])->orWhere([
'and',
['not', ['title' => null]],
['body' => null]
]);
// SELECT * FROM post WHERE (title IS NULL OR body IS NULL) OR (title IS NOT NULL AND body IS NULL)
LIKE OPERATOR with filterWhere activerecord methods
For example, in search filter you want to filter the post by searing post title or description posted by currently logged in user.
$title = 'test';
$description = 'test';
i) andFilterWhere()
$postLIKE = Post::find()->where(['user_id' => Yii::$app->user->getId()])->andfilterWhere(['or', ['title' => $title, 'description' => $description]])->all();
//SELECT * FROM post WHERE user_id = 2 AND ((`title` LIKE '%test%') OR (`description` LIKE '%test%'))
ii) orFilterWhere()
$postLIKE = Post::find()->where(['user_id' => Yii::$app->user->getId()])->orFilterWhere(['or', ['title' => $title, 'description' => $description]])->all();
//SELECT * FROM post WHERE user_id = 2 OR ((`title` LIKE '%test%') OR (`description` LIKE '%test%'))
iii) filterWhere()
$postLIKE = Post::find()->filterWhere(['AND', ['title' => $title, 'description' => $description]])->andWhere(['user_id' => Yii::$app->user->getId()])->all();
//SELECT * FROM post WHERE ((`title` LIKE '%test%') AND (`description` LIKE '%test%')) AND user_id = 2
Note: While using filterWhere() we have to call all andwhere() or orWhere() after the filterWhere() otherwise all where conditions will get remove except filterWhere()