时间戳

首页 > 解决方案 > 将一列除以从子查询创建的另一列

php - 将一列除以从子查询创建的另一列

问题描述

我有这个查询 -

$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 但不明白该怎么做。:)

标签： phplaravel

解决方案

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

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();

推荐阅读