thinkphp,thinkOrm同时使用where跟whereOr错误

最新推荐文章于 2024-08-13 15:08:50 发布

flysnownet

最新推荐文章于 2024-08-13 15:08:50 发布

阅读量687

点赞数

本文链接：https://blog.csdn.net/flysnownet/article/details/120313902

版权

php 同时被 2 个专栏收录

101 篇文章 2 订阅

订阅专栏

thinkPHP

29 篇文章 2 订阅

订阅专栏

keyword：thinkphp where跟whereOr同时使用

前言

判断时间冲突时用whereOr跟where没法实现想要的效果，后面用原生SQL写了，今天突然想到试试闭包查询，遂测试一番果然可行
thinkphp同时使用where和whereOr,本意是两个条件and并列查询，但实际上两个条件之间会用OR连接

错误写法如下

$map1 = [['start_time', '>', $startTime], ['start_time', '<', $endTime]];
        $map2 = [['start_time', '<=', $startTime], ['end_time', '>=',$endTime]];
        $map3 = [['end_time', '>', $startTime], ['end_time', '<', $endTime]];
        $exist = $this
            ->where('id','<>',1)
            ->whereOr([$map1, $map2, $map3])
            ->find();

生成的SQL

SELECT * FROM `task` WHERE (  `id` <> 1 OR ( `start_time` > 1640566917 AND `start_time` < 1640567917 )  OR ( `start_time` <= 1640566917 AND `end_time` >= 1640567917 )  OR ( `end_time` > 1640566917 AND `end_time` < 1640567917 ) ) AND `task`.`delete_time` = '0' LIMIT 1

解决方法

方法1.
拼原生SQL，用whereRaw查询，如下

$where = "id <> $id";
        $where .= " and ((`start_time` > $startTime AND `start_time` < $endTime) OR (`start_time` <= $startTime AND `end_time` >= $endTime) OR (`end_time` > $startTime AND `end_time` < $endTime))";
        $exist = $this
            ->whereRaw($where)
            ->find();

方法2
使用闭包查询

$exist = $this
            ->where('id','<>',1)
            ->where(function ($query) use($map1, $map2, $map3) {
                $query->whereOr([$map1, $map2, $map3]);
            })
            ->find();

生成的sql如下，条件之间用了and

SELECT * FROM `task` WHERE (  `id` <> 1  AND (  ( `start_time` > 1640566917 AND `start_time` < 1640567917 )  OR ( `start_time` <= 1640566917 AND `end_time` >= 1640567917 )  OR ( `end_time` > 1640566917 AND `end_time` < 1640567917 ) ) ) AND `task`.`delete_time` = '0' LIMIT 1