我在Laravel 5.3控制器中使用Raw SQL运行脚本,但发现它运行缓慢且不安全(如Raw)。是否有任何方法可以使其更有效并将其转换为雄辩的Laravel或Query Builder?
代码如下,谢谢!
SELECT machine_code, machine_name
FROM factory_equipment
WHERE machine_code NOT IN
(
SELECT distinct(machine_code)
FROM echecklist_data
WHERE DATE_FORMAT(date, '%Y-%m-%d') = CURDATE()
)
AND type='production' ORDER BY machine_code ASC
最佳答案
您可以使用SQL提高效率。我建议NOT EXISTS
:
SELECT fe.machine_code, fe.machine_name
FROM factory_equipment fe
WHERE NOT EXISTS (SELECT 1
FROM echecklist_data ed
WHERE ed.machine_code = fe.machine_code AND
ed.date >= CURDATE() AND
ed.date < CURDATE() + INTERVAL 1 DAY
) AND
fe.type = 'production'
ORDER BY fe.machine_code ASC;
我将进一步推荐以下索引:
factory_equipment(type, machine_code, machine_name)
和echecklist_data(machine_code, date)
。