db :: raw在查询生成器中的laravel的左连接

Question

如何在laravel中使用查询生成器进行此连接。我的问题是，我需要MySQL的功能HOUR()为此，我拿DB::raw()且功能DATE()和CURDATE()

SELECT HOUR(horas.hora) as x, tikets.asunto as name, COUNT(tikets.turno) as numero 
    FROM horas 
    LEFT JOIN tikets 
    ON HOUR(tikets.llegada) = HOUR(horas.hora) 
    and DATE(created_at) = CURDATE() and estado = 1 and id_sucursal = 1 and asunto = 'Convenio' 
    GROUP BY hora 
    ORDER BY hora ASC 

感谢您的关注

Answer 1

您可以使用里面的加入是因为MySQL聚合函数

SELECT HOUR(horas.hora) as x, tikets.asunto as name, COUNT(tikets.turno) as numero 
    FROM horas 
    LEFT JOIN tikets 
    ON (SELECT HOUR(llegada) FROM tikets) = (SELECT HOUR(hora) FROM horas) 
    and DATE(created_at) = CURDATE() and estado = 1 and id_sucursal = 1 and asunto = 'Convenio' 
    GROUP BY hora 
    ORDER BY hora ASC