Laravel Nested Select + MySQL

Question

How can i do a nested select using Laravel raw query?

SELECT day_of_week, AVG(order_count) average_order FROM 
(
  SELECT DAYNAME(order_date) day_of_week, 
         DAYOFWEEK(order_date) day_num, 
         TO_DAYS(order_date) date,
         count(*) order_count
  FROM orders 
  GROUP BY date
) temp
GROUP BY day_of_week 
ORDER BY day_num

This is what I tried so far:

DB::table('(
              SELECT DAYNAME(order_date) day_of_week, 
                     DAYOFWEEK(order_date) day_num, 
                     TO_DAYS(order_date) date,
                     count(*) order_count
              FROM orders 
              GROUP BY date
            ) temp')
            ->select(DB::raw('day_of_week, AVG(order_count) average_order'))
            ->groupBy(DB::raw('day_of_week'))
            ->orderBy(DB::raw('day_num'))
            ->get();

This is not working, but so far this query is very close.

By the way, I am using Laravel 5.6.

Thanks!

@JonasStaudenmeir i've updated my question with the query that I tried. I am using Laravel 5.6. — PinoyStackOverflower
– PinoyStackOverflower, Commented Jul 30, 2018 at 11:10
there is method in laravel of using raw query did you try that @PinoyStackOverflower — Gaurav Gupta
– Gaurav Gupta, Commented Jul 30, 2018 at 12:45

Jonas Staudenmeir · Accepted Answer · 2018-07-30 17:14:50Z

2

Use DB::raw() in table() and don't use it in groupBy() and orderBy():

DB::table(DB::raw('(
      SELECT DAYNAME(order_date) day_of_week, 
             DAYOFWEEK(order_date) day_num, 
             TO_DAYS(order_date) date,
             count(*) order_count
      FROM orders 
      GROUP BY date
    ) temp'))
    ->select('day_of_week', DB::raw('AVG(order_count) average_order'))
    ->groupBy('day_of_week')
    ->orderBy('day_num')
    ->get();

You can also use fromSub():

$from = DB::table('orders')
    ->selectRaw(
        'DAYNAME(order_date) day_of_week,
        DAYOFWEEK(order_date) day_num, 
        TO_DAYS(order_date) date,
        count(*) order_count'
    )->groupBy('date');
DB::query()->fromSub($from, 'temp')
    ->select('day_of_week', DB::raw('AVG(order_count) average_order'))
    ->groupBy('day_of_week')
    ->orderBy('day_num')
    ->get();

edited Jul 30, 2018 at 17:14

answered Jul 30, 2018 at 17:09

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

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

1 Comment

PinoyStackOverflower Over a year ago

I wasn't aware I can use DB::raw in the table. Thanks much for this! You are a savior!

Hamed Yarandi · Accepted Answer · 2018-07-30 12:23:40Z

0

Try This , but surely it's need to edit

DB::table('orders')
    ->select(DB::raw('(SELECT order_date AS day_of_week, order_date AS day_num, order_date AS date FROM orders GROUP BY date)'))
    ->groupBy(DB::raw('day_of_week'))
    ->orderBy(DB::raw('day_num'))
    ->get();

edited Jul 30, 2018 at 12:23

answered Jul 30, 2018 at 12:00

Hamed Yarandi

1,2291 gold badge14 silver badges21 bronze badges

Collectives™ on Stack Overflow

Laravel Nested Select + MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related