我在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)

10-08 13:02