[Solved]-laravel query builder with conditions-Chart.js

you'll need to convert this to laravel/eloquent, but this is a raw db query that does what you're looking for.

assumed tables / data

table: tbl_dates
id | date
1    2016-11-17 00:00:00
2    2016-11-18 00:00:00
...etc...

table: tickets
id | created_at
1    2016-11-18 12:34:56
2    2016-11-18 01:23:45
3    2016-11-18 02:34:56

table: ticket_status
ticket_id | name
1           open
2           closed
3           closed

query:

select 
  count(tickets.id) as tickets,
  ticket_status.name, 
  date(tbl_dates.date) as ticket_date
from 
  tbl_dates
left join 
  tickets 
on
  (date(tbl_dates.date) = date(tickets.created_at))
left join
  ticket_status
on
  (tickets.id = ticket_status.ticket_id)
group by 
  ticket_status.name
order by 
  ticket_date
asc

result:

 tickets | name | ticket_date
 0         null   2016-11-17
 1         open   2016-11-18
 2         closed 2016-11-18

basically, to do this in pure mysql you need a table with all dates. check out this so post for an easy way to generate the dates table.

use mysql's date function to format the date first and then group by the formatted date:

so your query should look something like this:

$join = $this->tickets();
    $tickets = $join
            ->when($category, function($query) use ($category) {
                $ranges = $this->daterange($category);
                return $query->wherebetween('tickets.created_at', $ranges);
            })
            ->select(db::raw('count(tickets.id) as tickets'), 'ticket_status.name as name', db::raw('date('tickets.created_at') as created_date'))
            ->groupby('ticket_status.name', 'created_date')
            ->get();

follow please laravel:collection:groupby

so it's maybe you need to have a code look like this

$join = $this->tickets();
$tickets = $join
         ->when($category, function($query) use ($category) {
             $ranges = $this->daterange($category);
             return $query->wherebetween('tickets.created_at', $ranges);
         })
         ->select(db::raw('count(tickets.id) as tickets'), 'ticket_status.name as name', 'tickets.created_at')
         ->get()->groupby(/** your logic here  **/);