score:1

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.

score:1

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();

score:2

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  **/);

Related Query

More Query from same tag