我需要将此查询转换为Laravel查询生成器或ORM
SET @start_date = '2020-11-01';
SET @end_date = '2020-11-08';
SET @duration = CONVERT(@end_date, DATE) - CONVERT(@start_date, DATE);
SELECT item_id, days
FROM (
SELECT item_id, sum(end_date - start_date) AS days
FROM schedule WHERE start_date >= @start_date AND end_date <= @end_date
GROUP BY item_id) AS virtual
WHERE days = @duration;
(我使用Laravel 8(
我找不到类似的例子,我可以自己分析和尝试:(
我试试这个:
$res = DB::table('schedule')
->select('schedule.item_id' , DB::raw("SUM(schedule.end_date - schedule.start_date) as days"))
->where('start_date', '>=', $start_date)
->where('end_date', '<=', $end_date)
->groupBy('item_id')
->where('days', '=', $duration)
->get();
但我得到错误:
Column not found: 1054 Unknown column 'days' in 'where clause'
好的,我知道出了什么问题
在用AS创建"days"列之前,我曾尝试访问该列该列将在第->行之后可用;执行get((。所以我改变了这两行的顺序:
->where('days', '=', $duration)
->get();
至
->get()
->where('days', '=', $duration);
现在工作:(