如何使用 Laravel Eloquent Querybuilder 编写带有子查询的选择查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44667668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write select query with subquery using laravel Eloquent Querybuilder?
提问by Hardika Satasiya
I had got the result from the query.
我从查询中得到了结果。
My Simple SQL is:
我的简单 SQL 是：
SELECT
o2.driver_id,
total_delieveries,
DATE_FORMAT(o1.created_at ,'%Y-%m-%d') AS created_at
FROM
(
SELECT
driver_id,
created_at,
COUNT(driver_id) AS total_delieveries
FROM
orders
WHERE
is_paid = 0
AND order_status = 5
AND created_at BETWEEN "'.$first_Day.'"
AND "'.$last_Day.'"
GROUP BY DATE_FORMAT(created_at ,'%Y-%m-%d'),driver_id
)
o1 INNER JOIN orders o2 ON o1.driver_id = o2.driver_id GROUP BY o1.created_at
In Laravel source, I wrote the query:
在 Laravel 源代码中，我编写了查询：
$responseData = DB::select(DB::raw('select t.driver_id,total_delieveries,DATE_FORMAT(q1.created_at,\'%Y-%m-%d\') as created_at from ( SELECT driver_id, created_at, COUNT( driver_id ) AS total_delieveries FROM orders WHERE is_paid=0 AND order_status = 5 AND created_at BETWEEN "'.$first_Day.'" AND "'.$last_Day.'" GROUP BY DATE_FORMAT(created_at,\'%Y-%m-%d\'),driver_id) q1 INNER JOIN orders t ON q1.driver_id = t.driver_id GROUP BY q1.created_at'));
I got the result what I wanted but I want to write it in a better way.
我得到了我想要的结果，但我想以更好的方式写出来。
Please tell me the correct and proper method to write this query.
请告诉我编写此查询的正确方法。
回答by Andrey Lutskevich
Also showthis answer and for your variant:
还显示此答案和您的变体：
$subQuery = \DB::table('orders')->selectRaw('driver_id, created_at, COUNT(driver_id) AS total_delieveries')
->where('is_paid', 0)
->where('order_status', '5')
->whereBetween('created_at', [$first_Day, $last_Day])
->groupBy(\DB::raw('DATE_FORMAT(created_at ,"%Y-%m-%d"),driver_id'));
$q = \DB::table(\DB::raw('('.$subQuery->toSql().') as o1'))
->selectRaw('o2.driver_id,total_delieveries,DATE_FORMAT(o1.created_at ,"%Y-%m-%d") AS created_at')
->join('orders as o2', 'o1.driver_id', '=', 'o2.driver_id')
->groupBy('o1.created_at')
->mergeBindings($subQuery)
->get();
