sql - transaction - updateorcreate laravel

¿Cómo escribir una consulta select con subconsulta utilizando laravel Eloquent Querybuilder?

Question

Obtuve el resultado de la consulta.

Mi SQL simple es:

 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

En la fuente de Laravel, escribí la consulta:

$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''));

Obtuve el resultado que quería pero quiero escribirlo de una mejor manera.

Por favor dígame el método correcto y correcto para escribir esta consulta.

Answer 1

También muestre esta respuesta y para su variante:

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