Yii2模糊查询where()的用法

高调费力互联网 2022-08-17 15:09:07 148686阅读 北京市 教育网

简单的用法中,where()方法是设置查询条件的,也就是sql语句中的where部分,例如:

  1. $query = Article::find()->where(['status'=>10]);
  2. //sql: SELECT * FROM `article` WHERE `status`=10
  3. $query = Article::find()->where(['status'=>10, 'type'=>1]);
  4. //sql: SELECT * FROM `article` WHERE (`status`=10) AND (`type`=1)
  5. $query = Article::find()->where(['status'=>10, 'id'=>[1,2,3]]);
  6. //sql: SELECT * FROM `article` WHERE (`status`=10) AND (`id` IN (1, 2, 3))

where()还可以指定运输符,例如:

  1. $query = Article::find()->where(['>=', 'id', 10]);
  2. //sql: SELECT * FROM `article` WHERE `id` >= 10

当然,where()还有更复杂的用法,例如:

  1. $query = Article::find()->where(['and', 'type=1', 'status=10']);
  2. //sql: SELECT * FROM `article` WHERE (type=1) AND (status=10)
  3. $query = Article::find()->where(['and', 'type=1', ['or', 'cid=1', 'status=10']]);
  4. //sql: SELECT * FROM `article` WHERE (type=1) AND ((cid=1) OR (status=10))

除了and,当然还有如ornotbetweennot betweeninnot inlikeor likenot likeor not likeexistsnot exists,用法示例如下:

  1. //or
  2. $query = Article::find()->where(['or', ['type'=>[7,8,9]], ['status'=>[1,2,3]]]);
  3. //sql: SELECT * FROM `article` WHERE (`type` IN (7, 8, 9)) OR (`status` IN (1, 2, 3))
  4.  
  5. //not
  6. $query = Article::find()->where(['not', ['type'=>[7,8,9]]]);
  7. //sql: SELECT * FROM `article` WHERE NOT (`type` IN (7, 8, 9))
  8.  
  9. //between
  10. $query = Article::find()->where(['between', 'id', 1, 10]);
  11. //sql: SELECT * FROM `article` WHERE `id` BETWEEN 1 AND 10
  12.  
  13. //not between
  14. $query = Article::find()->where(['not between', 'id', 1, 10]);
  15. //sql: SELECT * FROM `article` WHERE `id` NOT BETWEEN 1 AND 10
  16.  
  17. //in
  18. $query = Article::find()->where(['in', 'id', [1,2,3]]);
  19. //sql: SELECT * FROM `article` WHERE `id` IN (1, 2, 3)
  20. $query = Article::find()->where(['in', ['id', 'type'], [['id'=>1, 'type'=>10], ['id'=>2, 'type'=>8]]]);
  21. //sql: SELECT * FROM `article` WHERE (`id`, `type`) IN ((1, 10), (2, 8))
  22.  
  23. //not in
  24. $query = Article::find()->where(['not in', 'id', [1,2,3]]);
  25. //sql: SELECT * FROM `article` WHERE `id` NOT IN (1, 2, 3)
  26.  
  27. //like
  28. $query = Article::find()->where(['like', 'name', 'tester']);
  29. //sql: SELECT * FROM `article` WHERE `name` LIKE '%tester%'
  30. $query = Article::find()->where(['like', 'name', ['test', 'sample']]);
  31. //sql: SELECT * FROM `article` WHERE `name` LIKE '%test%' AND `name` LIKE '%sample%'
  32. $query = Article::find()->where(['like', 'name', '%tester', false]);
  33. //sql: SELECT * FROM `article` WHERE `name` LIKE '%tester'
  34.  
  35. //or like
  36. $query = Article::find()->where(['or like', 'name', ['test', 'sample']]);
  37. //sql: SELECT * FROM `article` WHERE `name` LIKE '%test%' OR `name` LIKE '%sample%'
  38.  
  39. //not like
  40. $query = Article::find()->where(['not like', 'name', 'tester']);
  41. //sql: SELECT * FROM `article` WHERE `name` NOT LIKE '%tester%'
  42.  
  43. //or not like
  44. $query = Article::find()->where(['or not like', 'name', ['test', 'sample']]);
  45. //sql: SELECT * FROM `article` WHERE `name` NOT LIKE '%test%' OR `name` NOT LIKE '%sample%'
  46.  
  47. //exists
  48. $query = Article::find()->where(['exists', Article::find()->select('id')->from('users')->where(['active'=>1])]);
  49. //sql: SELECT * FROM `article` WHERE EXISTS (SELECT `id` FROM `users` WHERE `active`=1)
  50.  
  51. //not exists
  52. $query = Article::find()->where(['not exists', Article::find()->select('id')->from('users')->where(['active'=>1])]);
  53. //sql: SELECT * FROM `article` WHERE NOT EXISTS (SELECT `id` FROM `users` WHERE `active`=1)

另外,andWhere()orWhere()的用法,与where()基本相同。

提示:如果此问题没有解决您的需求,您可以点击 “我也要问” 在线咨询。 我也要问

若此问题存在违规行为,您可以点击 “举报”

0条回答

快速提问,在线解答

1

描述需求

填写需求概要标题,补充详细需求

2

耐心等

等待网友或网站工作人员在线解答

3

巧咨询

还有疑问?及时追问回复

立即咨询