如何使用Laravel查询生成器编写此查询?(我可以用拉拉威尔的雄辩来得到同样的结果)

Question

SELECT *,
SUM(num_hr) AS total_hr,
attendances.empID AS empid
FROM attendances
LEFT JOIN addsalaryemployees
ON addsalaryemployees.empID = attendances.empID
LEFT JOIN positions
ON positions.id = addsalaryemployees.empPosition
GROUP BY attendances.empID
ORDER BY addsalaryemployees.empFirstName ASC

Answer 1

Laravel文档非常好，有一整节关于Query Builder，我建议您通读一遍。

有几种方法可以实现您想要的SQL，以下只是其中之一。

$query = DB::table('attendances')
->leftJoin('addsalaryemployees', 'addsalaryemployees.empID', '=', 'attendances.empID')
->leftJoin('positions', 'positions.id', '=', 'addsalaryemployees.empPosition')
->groupBy('attendances.empID')
->orderBy('addsalaryemployees.empFirstName')
->selectRaw('*, SUM(num_hr) AS total_hr, attendances.empID AS empid');

如果您dd($query->toSql())，您将得到以下内容。

select *, SUM(num_hr) AS total_hr, attendances.empID AS empid 
from `attendances`
left join `addsalaryemployees` on `addsalaryemployees`.`empID` = `attendances`.`empID`
left join `positions` on `positions`.`id` = `addsalaryemployees`.`empPosition`
group by `attendances`.`empID`
order by `addsalaryemployees`.`empFirstName` asc