Laravel雄辩查询在雄辩上失败，但在PhpMyAdmin中成功

Question

Laravel 4.2

这是我的桌子

sk__queue：

+----------+----------+---------------------+--------+
| idQueue  | idCap    | date                | status |
+----------+----------+---------------------+--------+
| 1350     | 7        | 2015-10-20 11:20:00 | 9      |
| 1427     | 7        | 2015-10-29 14:20:00 | 4      |
| 1428     | 7        | 2015-10-29 14:19:00 | 4      |
+----------+----------+---------------------+--------+

sk__cloc

+----------+----------+-----------+-----+---------------------+--------------------------+
| idCloc   | idCap    | cDuration | cNb | begin               | end                      |
+----------+----------+-----------+-----+---------------------+--------------------------+
| 1        | 7        | 10        | 3   | 2015-01-01 08:00:00 | 2015-12-31 22:30:00      |
+----------+----------+-----------+-- --+---------------------+--------------------------+

如果sk__queue.status为< 4或> 7，则可用位置。

我想知道在过去的10分钟到10分钟的时间里有多少个位置可用。

我雄辩的质疑是这样的：

    //$now = date('Y-m-d H:i:s');
    $now = "2015-10-29 14:12:08";
    $dispo = DB::table('sk__cloc')->leftJoin('sk__queue', function($join) use ($now) {
        $join->on('sk__queue.idCap', '=', 'sk__cloc.idCap');
        $join->where('sk__queue.date', '>=', "('$now' - INTERVAL cDuration MINUTE)");
        $join->where('sk__queue.date', '<=', "('$now' + INTERVAL cDuration MINUTE)");
        $join->where('sk__queue.date', '>=', 'sk__cloc.begin');
        $join->where('sk__queue.date', '<=', 'sk__cloc.end');
        $join->where('sk__queue.status', '>=', 4);
        $join->where('sk__queue.status', '<=', 7);
    })
    ->where('sk__cloc.idCap', '=', 7)
    ->groupBy('cNb')
    ->select(DB::raw('cNb*2, count(sk__queue.idCap) AS count'))
    ->first();

结果："cNb*2":6, "count":0

现在我正在做DB::getQueryLog()，这是laravel的请求：

    select cNb*2, count(sk__queue.idCap) AS count, sk__queue.date
    from `sk__cloc` left join `sk__queue`
    on `sk__queue`.`idCap` = `sk__cloc`.`idCap`
    and `sk__queue`.`date` >= ('2015-10-29 14:12:08' - INTERVAL cDuration MINUTE)
    and `sk__queue`.`date` <= ('2015-10-29 14:12:08' + INTERVAL cDuration MINUTE)
    and `sk__queue`.`date` <= sk__cloc.end
    and `sk__queue`.`status` >= 4
    and `sk__queue`.`status` <= 7
    where `sk__cloc`.`idCap` = 7
    group by cNb

在PhpMyAdmin中，结果是："cNb*2":6, "count":2

然后我评论了这些台词：

    //$now = date('Y-m-d H:i:s');
    $now = "2015-10-29 14:12:08";
    $dispo = DB::table('sk__cloc')->leftJoin('sk__queue', function($join) use ($now) {
        $join->on('sk__queue.idCap', '=', 'sk__cloc.idCap');
        $join->where('sk__queue.date', '>=', "('$now' - INTERVAL cDuration MINUTE)");
        //$join->where('sk__queue.date', '<=', "('$now' + INTERVAL cDuration MINUTE)");
        //$join->where('sk__queue.date', '>=', 'sk__cloc.begin');
        $join->where('sk__queue.date', '<=', 'sk__cloc.end');
        $join->where('sk__queue.status', '>=', 4);
        $join->where('sk__queue.status', '<=', 7);
    })
    ->where('sk__cloc.idCap', '=', 7)
    ->groupBy('cNb')
    ->select(DB::raw('cNb*2, count(sk__queue.idCap) AS count'))
    ->first();

结果："cNb*2":6, "count":2

我唯一的问题是:这里发生了什么事?！我真的很困惑。

谢谢你的帮助。

Answer 1

你应该继续加入"on"：

    $join->on('sk__queue.idCap', '=', 'sk__cloc.idCap');
    $join->on('sk__queue.date', '>=', "('$now' - INTERVAL cDuration MINUTE)");
    $join->on('sk__queue.date', '<=', "('$now' + INTERVAL cDuration MINUTE)");
    $join->on('sk__queue.date', '>=', 'sk__cloc.begin');
    $join->on('sk__queue.date', '<=', 'sk__cloc.end');
    $join->on('sk__queue.status', '>=', 4);
    $join->on('sk__queue.status', '<=', 7);