从mysql获取数据并在几周内对它们进行分组，并显示日期

Question

I am printing data on weekly basis. I need to add one thing to my query. I want the starting and ending date of the week as well along with data.

At the moment the data is like this

        [0] => Array
            (
                [WEEK] => 7

                [total_sub_total] => 110.30
                [total_tax] => 9.92
                [total_restaurant_delivery_fee] => 0
            )


        [0] => Array
            (
                [WEEK] => 8

                [total_sub_total] => 6540.00
                [total_tax] => 1046.40
                [total_restaurant_delivery_fee] => 0
            )

I want the dates as well of that week. for example, it should be

[0] => Array
                (
                    [WEEK] => 8,
                    [starting_week_date] => 2018-02-21 13:18:10,
                    [ending_week_date] => 2018-02-26 13:18:10,


                    [total_sub_total] => 6540.00
                    [total_tax] => 1046.40
                    [total_restaurant_delivery_fee] => 0
                )

Here is the code

public function getWeeklyEarnings($restaurant_id)
    {


        return $this->find('all', array(


            //'contain' => array('OrderMenuItem', 'Restaurant', 'OrderMenuItem.OrderMenuExtraItem', 'PaymentMethod', 'Address','UserInfo','RiderOrder.Rider'),

            'conditions' => array(



                'Order.restaurant_id' => $restaurant_id,
                'Order.status' => 2,
                'Order.created >    DATE_SUB(NOW(), INTERVAL 4 WEEK)'



            ),
            'fields' => array(


                'WEEK(Order.created) AS WEEK',

                'sum(Order.sub_total)   AS total_sub_total',
                'sum(Order.tax)   AS total_tax',
                'sum(Order.restaurant_delivery_fee)   AS total_restaurant_delivery_fee',

            ),
            'group' => array('WEEK(Order.created)'),

            'recursive' => 0

        ));
    }

Answer 1

There is no simple function that will yield a week interval (or start/end date of a week) given a week number. You have to find these dates manually.
try this :

'fields' => array(
    'WEEK(Order.created) AS WEEK',

    'DATE_ADD(Order.created, INTERVAL(1-DAYOFWEEK(Order.created)) DAY) AS week_start',
    'DATE_ADD(Order.created, INTERVAL(7-DAYOFWEEK(Order.created)) DAY) AS week_end',

    'sum(Order.sub_total)   AS total_sub_total',
    'sum(Order.tax)   AS total_tax',
    'sum(Order.restaurant_delivery_fee)   AS total_restaurant_delivery_fee'
)

The DAYOFWEEK() function returns an integer ranging from 1 (Sunday) to 7 (Saturday). So if Order.created happens to be Tuesday we get the following statements:

DATE_ADD(Order.created, INTERVAL -2 DAY)

which essentially means “subtract 2 days from Order.created (which is that week’s Sunday) and also:

DATE_ADD(Order.created, INTERVAL 4 DAY)

which yields the date of that week’s Friday.

Or, you can try :

'fields' => array(
    'WEEK(Order.created) AS WEEK',

    'MIN(Order.created) as week_start',
    'MAX(Order.created) as week_end',

    'sum(Order.sub_total)   AS total_sub_total',
    'sum(Order.tax)   AS total_tax',
    'sum(Order.restaurant_delivery_fee)   AS total_restaurant_delivery_fee'
)

But you have to be sure that you have orders on every single day of the week.

I hope this helps.