下面有一个带有子查询的查询。如何在laravel 5查询生成器中生成它？困扰我的是共享第一个查询的列的子查询。
SELECT
DATE(review_headers.`created_at`) AS `date`,
COUNT(review_headers.id) AS reviews,
(
SELECT
(ROUND((SUM(IF(rd2.`param_value` >=9, 1,0))/COUNT(rh2.id))*100,2)) - (ROUND((SUM(IF(rd2.`param_value` <7, 1,0))/COUNT(rh2.id))*100,2))
FROM review_headers rh2
INNER JOIN review_details rd2 ON rd2.review_header_id = rh2.id
WHERE DATE(rh2.created_at) <= DATE(review_headers.`created_at`)
) AS cumulativeNPS
FROM review_headers
INNER JOIN review_details ON review_details.review_header_id = review_headers.id
GROUP BY DATE(review_headers.`created_at`)
ORDER BY DATE(review_headers.`created_at`)
表格：
review_headers
id subject created_at
------ ----------------------- ---------------------
20 review 8 2016-03-31 15:50:57
21 review 9 2016-03-30 15:50:57
22 review 10 2016-01-14 15:50:57
23 review 16 2016-04-25 08:19:03
24 review 17 2016-04-25 08:19:03
25 review 18 2016-04-19 08:19:03
26 review 19 2016-04-18 08:19:03
27 review 20 2016-04-17 08:19:03
28 review 21 2016-04-07 08:19:03
29 review 22 2016-03-27 08:19:03
30 review 23 2016-03-25 08:19:03
31 review 24 2016-04-25 08:19:03
32 review 25 2016-04-25 08:19:03
33 review 26 2016-04-19 08:19:03
34 review 27 2016-04-18 08:19:03
35 review 28 2016-04-17 08:19:03
36 review 29 2016-03-27 08:19:03
37 review 30 2016-03-25 08:19:03
review_details
id review_header_id param_value
------ ---------------- -------------
97 21 7
103 22 4
109 23 8
115 24 5
121 25 6
127 26 8
133 27 9
139 28 9
145 29 5
151 30 9
157 31 3
163 32 8
169 33 10
175 34 4
181 35 7
187 36 4
193 37 7
这里有基于查询的完整代码：
$subQuery = 'DB::table('review_headers as rh2')
->select('DB::raw('ROUND((SUM(IF(rd2.`param_value` >=9, 1,0))/COUNT(rh2.id))*100,2)) - (ROUND((SUM(IF(rd2.`param_value` <7, 1,0))/COUNT(rh2.id))*100,2)'))
->join('review_details as rd2'. 'rd2.review_header_id', '=', 'rh2.id')
->whereRaw('DATE(rh2.created_at) <= DATE(review_headers.`created_at`)');
$query = 'DB::table('review_headers')
->select(
'DB::raw('DATE(review_headers.created_at) AS date'),
'DB::raw('COUNT(review_headers.id) AS reviews'),
'DB::raw('(' . $subQuery->toSql() . ') as cumulativeNPS')
)
->join('review_details', 'review_details.review_header_id', '=', 'review_headers.id')
->groupBy('review_headers.created_at')
->orderByRaw('DATE(review_headers.created_at)')
->mergeBindings($subQuery);
我试过了，它似乎奏效了。
$data = ReviewHeader::select(
DB::raw('DATE(review_headers.`created_at`) AS dateTime'),
DB::raw('(SELECT
(ROUND((SUM(IF(rd2.`param_value` >=9, 1,0))/COUNT(rh2.id))*100,2)) - (ROUND((SUM(IF(rd2.`param_value` <7, 1,0))/COUNT(rh2.id))*100,2))
FROM review_headers rh2
INNER JOIN review_details rd2 ON rd2.review_header_id = rh2.id
WHERE DATE(rh2.created_at) <= DATE(review_headers.`created_at`)
) AS cumulativeNPS'),
DB::raw('COUNT(review_headers.id) AS review')
)
->join('review_details', 'review_details.review_header_id', '=', 'review_headers.id')
->groupBy(DB::raw('DATE(review_headers.`created_at`)'))
->orderBy('review_headers.created_at')