将一列除以从 Sub 查询创建的另一列

Question

我有这个查询 -

$employees = Employee::where('user_id', $user->id)->withCount([
'answers as questions_received' => function ($query) use($date) {
$query->where('created_at', '>=', $date);
}, 
'answers as correct' => function ($query) use($date) {
$query->where('correct', 1)->where('created_at', '>=', $date);
}
]);
$employees = $employees
->orderBy('answered', 'desc')
->orderBy('correct', 'desc')
->get();

我想做这样的事情 -((correct/questions_received) * 100) as percentage但不明白该怎么做。:)

Answer 1

假设这是表布局，并且answered和correct列的数据类型为值为0和1(例如Laravel用于MySQL布尔值的tinyint(1)数据类型(。

Employees
+----+---------+
| id | user_id |
+----+---------+
Answers
+----+-------------+----------+---------+------------+
| id | employee_id | answered | correct | created_at |
+----+-------------+----------+---------+------------+

这似乎可以解决问题：

$employees = Employee::select('E.*')
->selectRaw('COUNT(*) AS questions_received')
->selectRaw('SUM(correct) AS correct')
->selectRaw('SUM(correct) / COUNT(*) * 100 AS percentage')
->from('employees AS E')
->join('answers AS A', 'E.id', '=', 'A.employee_id')
->where('A.created_at', '>=', $date)
->where('E.user_id', $user->id)
->groupBy('E.id')
->get();