laravel count records based on each single date

Question

In my database I have records something like

**my_table_name**

id      date_column               status
1       2020-07-21 00:29:05       done
2       2020-07-21 21:29:05       done
3       2020-07-21 21:35:05       failed
4       2020-07-22 10:29:05       done
5       2020-07-22 10:35:05       done
6       2020-07-22 10:37:05       failed
7       2020-07-22 10:39:05       failed
8       2020-07-22 10:40:05       done

And using laravel query I want result like

array(
    [0]=>array(
        'date'=>'2020-07-21',
        'failed'=>1
        'done'=>2
     ),
   [0]=>array(
        'date'=>'2020-07-22',
        'failed'=>2
        'done'=>3
     )
)

How can I do this?

I have tried

$record=DB::table('my_table_name')
                    ->select(
                            DB::raw("DATE_FORMAT(date_column, 'Y-m-d') as date"),
                            DB::raw("SUM(status= 'failed') as failed"),
                            DB::raw("SUM(status= 'done') as done"),
                            )
                    ->where('date_column','>=','2020-07-21 00:00:00')
                    ->where('date_column','<=','2020-07-22 23:59:59')
                    ->groupBy(DB::raw("DATE_FORMAT(date_column, 'Y-m-d')"))
                    ->get();

Thanks in advance

Alireza · Accepted Answer · 2020-08-02 06:16:18Z

1

Your code seems to be fine but using date format of 'Y-m-d' instead of '%Y-%m-%d' which is wrong.

In order to solve your issue, use below query:

DB::table('my_table_name')::select(
            DB::raw("DATE_FORMAT(date_column, '%Y-%m-%d') as date"),
            DB::raw("SUM(name= 'failed') as failed"),
            DB::raw("SUM(name= 'done') as done"),
            )
            ->whereDate('date_column','>=','2020-07-21')
            ->whereDate('date_column','<=','2020-07-22')
            ->groupBy(DB::raw("DATE_FORMAT(date_column, '%Y-%m-%d')"))
            ->get();

edited Aug 2, 2020 at 6:16

Alireza

6,97813 gold badges70 silver badges145 bronze badges

answered Jul 22, 2020 at 11:03

Mortada Jafar

3,7091 gold badge21 silver badges34 bronze badges

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

2 Comments

Gulzar Ali Over a year ago

well it is working now but is there any way that it must return count for dates in between range currently it is not returning any record for that date if there is no record

Mortada Jafar Over a year ago

try to use whereDate instead where

Collectives™ on Stack Overflow

laravel count records based on each single date

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