ThinkPHP多个OR条件的写法
做项目偶然用到ThinkPHP这个框架,在写查询SQL的时候遇到上述问题,在调查多次后发现用以下写法可以完美搞定,特来记述一番。
- 有表结构和数据如下
表名:tmp_table
id | name | title | content | price |
---|---|---|---|---|
1 | Why | title why | content why | 199 |
2 | What | title What | content what | 209 |
3 | Where | title Where | content where | 329 |
4 | My Page | title Where | content where | 651 |
- 需要实现下面这样的查询
-
条件1
- name需要模糊匹配多个关键字,如 ‘Why’,‘My’ 条件2
- title和content需要模糊匹配同一个关键字,如:‘Wh’ 条件3
- price需要介于两个区间,如99~199,599~699
首先写出SQL:
SELECT id, name, price
FROM tmp_table
WHERE (name LIKE '%Why%' OR name LIKE '%My%')
AND (title LIKE '%Wh%' OR content LIKE '%Wh%')
AND ((price >= 99 AND price <= 199) OR (price >= 599 AND price <= 699))
PHP代码(仅WHERE部分):
// 假定参数
$nameParams = array('Why', 'My');
$keyword = 'Wh';
$priceArr = array(array(99, 199), array(599, 699));
$whereIdx = 0;
// name LIKE
$condNameArr = array();
foreach ($nameParams as $idx => $nParam) {
$kParam = '%' . $nParam . '%';
array_push($condNameArr, array('name ' => array('like', '%' . $nParam . '%')));
}
$condNameArr['_logic'] = 'or';
$where["_complex"][$whereIdx++] = $condNameArr;
// title, content LIKE
$keyCondArr["title"] = array('like', '%' . $keyword . '%');
$keyCondArr["content"] = array('like', '%' . $keyword . '%');
$keyCondArr["_logic"] = 'or';
$where["_complex"][$whereIdx++] = $keyCondArr;
// price区间
$pCondArr = array();
foreach ($priceArr as $idx => $price) {
array_push($pCondArr, array('price' => array(array('egt', $price[0]), array('elt', $price[1]))));
}
$condArr['_logic'] = 'or';
$where["_complex"][$whereIdx] = $pCondArr;
关键点就是将
$where["_complex"] = XXX;
修改成了有下标的数组形式:
$where["_complex"][$whereIdx++] = $condNameArr;
$where["_complex"][$whereIdx++] = $keyCondArr;
$where["_complex"][$whereIdx] = $pCondArr;
至此,上述问题就得到解决了。
示例SQL本身没有太大意义,主要是分享做法,初次写博客,请诸位大神轻拍。
- 参考链接
ThinkPHP3.2完全开发手册— [ 复合查询 ]