laravel query builder with conditions

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

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_datesid | date1    2016-11-17 00:00:002    2016-11-18 00:00:00...etc...Table: ticketsid | created_at1    2016-11-18 12:34:562    2016-11-18 01:23:453    2016-11-18 02:34:56Table: ticket_statusticket_id | name1           Open2           Closed3           Closed

Query:

SELECT   COUNT(tickets.id) AS tickets,  ticket_status.name,   DATE(tbl_dates.date) AS ticket_dateFROM   tbl_datesLEFT JOIN   tickets ON  (DATE(tbl_dates.date) = DATE(tickets.created_at))LEFT JOIN  ticket_statusON  (tickets.id = ticket_status.ticket_id)GROUP BY   ticket_status.nameORDER BY   ticket_dateASC

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