Laravel groupby Week Eloquent query

Question

As a newbie to Laravel , I had some some doubts regarding the date formatter and the blade templates.

I have written a query which is excuted in the correct way, Following is my Eloquent SQL Query.

public function orderbyweek()
    {

        $orderbyweek = DB::table('sales_flat_order_items as s')
                ->leftJoin('sales_flat_orders as w', 'w.entity_id','=','s.order_id')
                ->select(array(DB::Raw('sum(s.amount_refunded) as amount_refunded'),
                    DB::Raw('sum(s.row_total) as row_total'),
                    DB::Raw('sum(s.discount_amount) as discount_amount'),
                    DB::Raw('sum(s.tax_amount) as tax_amount'), 
                    DB::Raw('sum(s.qty_ordered) as qty_ordered'),
                    DB::Raw('sum(w.subtotal) as subtotal'),
                    DB::Raw('WEEK(w.created_at) week'), 
                    DB::Raw('sum(w.total_invoiced) as total_invoiced'),
                    DB::Raw('sum(w.shipping_amount) as shipping_amount')
                    ))
                ->where('qty_canceled','=','0')
                ->where('status','!=','canceled')
                ->groupBy('week')
                ->orderBy('w.created_at')
                ->get();

            return View::make('sales_flat_orders.orderbyweek', compact('orderbyweek'));


    }

This show me the table where i want the week as in interval of 7 days. So in my template what will i need to do? How to add the date formatter for that? I have done something here:

<table width="100%">
    <thead>

        <th>DATE</th>
        </thead>

  @foreach($orderbyweek as $s)

  <tr>
     <td>{{date("d F, Y",strtotime($s->week))}}</td>

  </tr>

 @endforeach
</table>

I have the sql query executed which is the following:

select sum(s.amount_refunded) as amount_refunded, sum(s.row_total) as row_total, sum(s.discount_amount) as discount_amount, sum(s.tax_amount) as tax_amount, sum(s.qty_ordered) as qty_ordered, sum(w.subtotal) as subtotal, WEEK(w.created_at) week, sum(w.total_invoiced) as total_invoiced, sum(w.shipping_amount) as shipping_amount from `sales_flat_order_items` as `s` left join `sales_flat_orders` as `w` on `w`.`entity_id` = `s`.`order_id` where `qty_canceled` = '0' and `status` != 'canceled' group by `week` order by `s`.`created_at` asc

Where am i going wrong?

I am getting ordebydate, orderbyyear too. But Not Order by Week

Please Help me out.

Answer 1

This is used for month group-by by me, I think this will also work for week.

    $data = \Invoice::select(   
                        DB::raw("MONTHNAME(STR_TO_DATE(MONTH(date), '%m')) as month"),
                        DB::raw('SUM(g_total) as total_invoice_amnt'),
                        DB::raw('MONTH(date) as no_mnth')
                    );
                    foreach ($filters as $filter) {
                        if ($filter[2] != '') {
                            $data->where($filter[0], $filter[1], $filter[2]);
                        }
                    }
    $data = $data->groupBy("month")
                 ->OrderBy('no_mnth','ASC')
                 ->get()
                 ->toArray();

    return $data;