How to write select query with subquery using laravel Eloquent Querybuilder?

Question

I had got the result from the query.

My Simple SQL is:

 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:

$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.

Andrii Lutskevych · Accepted Answer · 2017-06-21 07:56:47Z

10

Also show this 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();

edited Jun 21, 2017 at 7:56

answered Jun 21, 2017 at 6:36

Andrii Lutskevych

1,38913 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hardika Satasiya Over a year ago

I have modified your code there in $q statement ends with get(). so, I have put this. SQLSTATE[HY000]: General error: 2031 (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 = ? and order_status = ? and created_at between ? and ? group by DATE_FORMAT(created_at ,"%Y-%m-%d"),driver_id) as o1 inner join orders as o2 on o1.driver_id = o2.driver_id group by o1.created_at)

Andrii Lutskevych Over a year ago

I updated my answer. Add ->mergeBindings($subQuery) before get(). $subQuery return only sql string without values in the where clause and mergeBindings add it to the final $q

Collectives™ on Stack Overflow

How to write select query with subquery using laravel Eloquent Querybuilder?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related