php - 如何有效地使用 laravel 5.5 eloquent 进行复杂的连接和分组

Question

我有一个在 mysql 工作台上成功运行的查询:

SELECT
    any_value(u.username),
    any_value(b.name),
    any_value(gc.visit_cycle_id),
    any_value(gc.group_number),
    any_value(dc.total_customer),
    count(*) as total_day
FROM
    trackgobackenddb.group_cycles gc
LEFT JOIN (
    SELECT 
        any_value(dc.group_cycle_id) as group_cycle_id,
        count(*) as total_customer
    FROM
        trackgobackenddb.destination_cycles dc
    GROUP BY dc.group_cycle_id
) dc ON dc.group_cycle_id = gc.id
LEFT JOIN visit_cycles vc ON gc.visit_cycle_id = vc.id
LEFT JOIN users u ON vc.user_id = u.id
LEFT JOIN branches b ON vc.branch_id = b.id
GROUP BY gc.visit_cycle_id, gc.group_number;

如何转换该查询以便我可以使用 laravel eloquent 或查询生成器？

Answer 1

您可以使用DB::select运行复杂的原始 SQL

$sql = "";
$data = DB::select($sql);