Cakephp - 使用AND和OR条件搜索 [英] Cakephp - Search with AND and OR conditions

问题描述

我想实现一个搜索，其中所有的字段都是可选的，所以我必须根据用户输入过滤我的结果，但坚持如何实现这个...

搜索字段如下

1）通过电子邮件发送：

2）按名称：

3）按注册日期：（即fromDate和toDate）

选项1）

问题是选项2）name应该匹配first_name或last_name，所以我必须有一个OR条件

注意：所有字段都是可选的...

heres my code snippet ....

  $ conditions = array（）; 
 
 if（！empty（$ this-> request-> data ['searchbyemail']））{
 
 $ conditions [] = array '=> $ this-> request-> data ['searchbyemail']）; 
} 
 
 if（！empty（$ this-> request-> data ['regfromdate']）&& 
！empty（$ this-> request-> data ['regtodate']））{
 
 $ conditions [] = array（'User.created BETWEEN？AND？'=> 
 array（$ this-> ; request-> data ['regfromdate']，$ this-> request-> data ['regtodate']））; 
} 
 
 if（！empty（$ this-> request-> data ['searchbyname']））{
 
 $ conditions [] = array （
'OR'=> array（
'User.first_name'=> $ this-> request-> data ['searchbyname']，
'User.last_name' => $ this-> request-> data ['searchbyname'] 
 
））; 
} 
 
 
 $ this-> paginate = array（'conditions'=> $ conditions）; 
 $ this-> set（'users'，$ this-> paginate（））; 
  

尝试实现的查询应如下所示：

  SELECT * FROM`users`AS`User` 
 WHERE`User`.`username` ='a'
 AND`User`.` `
 BETWEEN'2013-02-01'
 AND'2013-02-05'
 AND（（'User`.`first_name` LIKE'％b％'））OR（ `User`.`last_name` LIKE'％b％'））
  

在cakephp，但它的静态，我寻找一个动态的方式来生成这个查询，因为搜索字段是可选的，并且查询应该根据用户输入更改.....

LIKE 运算符，您需要：

 'OR'=>数组（
'User.first_name LIKE'=>％。$ this-> request-> data ['searchbyname']。％，
'User.last_name LIKE' >％。$ this-> request-> data ['searchbyname']。％
），
  

I am trying to implement a search in which all fields are optional, so i have to filter my results based on user input, but am stuck on how to achieve this...

The search field is as follows

1) serach by email :

2) serach by name :

3) serach by registration date : (i.e fromDate and toDate)

options 1) and 3) am able to achieve as only an AND condition is required.

problem is with option 2) name should match either first_name or last_name, so i must have an OR condition , which i cant figure out a right way to do it..

Note: All fields are optional...

heres my code snippet....

$conditions = array();

if(!empty($this->request->data['searchbyemail'])) {

   $conditions[] = array('User.username' => $this->request->data['searchbyemail']);
}

if(!empty($this->request->data['regfromdate']) &&
   !empty($this->request->data['regtodate'])) {

    $conditions[] = array('User.created BETWEEN ? AND ?' => 
    array($this->request->data['regfromdate'], $this->request->data['regtodate']));
}

if(!empty($this->request->data['searchbyname'])) {

    $conditions[] = array(
'OR' => array(
        'User.first_name' => $this->request->data['searchbyname'],
        'User.last_name' => $this->request->data['searchbyname']

                ));
}


$this->paginate = array('conditions' => $conditions);
$this->set('users', $this->paginate());

The query am trying to achieve should look like this

SELECT * FROM  `users` AS `User`
WHERE `User`.`username` = 'a'
AND `User`.`created`
BETWEEN '2013-02-01'
AND '2013-02-05'
AND ((`User`.`first_name` LIKE '%b%')) OR ((`User`.`last_name` LIKE '%b%'))

Am able to generate this query in cakephp but its static , am looking for a dynamic way to generate this query since the search fields are optional and the query should change according to the user input.....

解决方案

To use the LIKE operator with the % wildcards, you would:

        'OR' => array(
            'User.first_name LIKE' => "%" . $this->request->data['searchbyname'] . "%",
            'User.last_name LIKE' => "%" . $this->request->data['searchbyname'] . "%"
        ),

这篇关于Cakephp - 使用AND和OR条件搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！