mysql - 将常规查询更改为 Laravel 查询构建器

Question

如何使用 Laravel 查询构建器编写这个普通查询？

SELECT `t1`.`id` , `i`.`item_description` AS `name` , SUM( t1.amount ) AS total, `u`.`name` AS `unit`
FROM `fees9_items` AS `t1`
LEFT JOIN `items` AS `i` ON `i`.`id` = `t1`.`item_id`
LEFT JOIN `mesure_units` AS `u` ON `u`.`id` = `i`.`unit`
LEFT JOIN `fees9` AS `f` ON `f`.`id` = `t1`.`fees9_id`
WHERE date >= '2016-01-01' AND (f.field1=1 OR f.field2=1 OR f.other =1)
GROUP BY `i`.`id` 

这部分很重要:

(f.field1=1 OR f.field2=1 OR f.other =1)

Answer 1

试试这个，应该有效

 $query = DB::table("fees9_items AS t1")
              ->select(array("t1.id", "i.item_description AS name", 
                        DB::raw("SUM(t1.amount) as total"), "u.name AS unit"
                      )
              ->leftJoin("items AS i", "i.id", "=", "t1.item_id")
              ->leftJoin("mesure_units AS u", "u.id", "=", "i.unit")
              ->leftJoin("fees9 AS f", "f.id", "=", "t1.fees9_id")
              ->where("date", ">=", "2016-01-01")
              ->where(function($query){
                   $query->where("f.field1", "=", 1)
                         ->orWhere("f.field2", "=", 2)
                         ->orWhere("f.other", "=", 1)
                    )
               ->groupBy("i.id")
               ->get();