Laravel query builder with conditions

Question

I am executing a query to draw a multi-line chart using charts. I have an array of dates

["2016-10-16","2016-10-17","2016-10-18","2016-10-19","2016-10-20","2016-10-21","2016-10-22","2016-10-23","2016-10-24","2016-10-25","2016-10-26","2016-10-27","2016-10-28","2016-10-29","2016-10-30","2016-10-31","2016-11-01","2016-11-02","2016-11-03","2016-11-04","2016-11-05","2016-11-06","2016-11-07","2016-11-08","2016-11-09","2016-11-10","2016-11-11","2016-11-12","2016-11-13","2016-11-14","2016-11-15","2016-11-16"] 

This array has dates between "2016-11-16" and "2016-10-16".

I created the "Tickets" model, and I wrote a query to count the number of tickets grouped by ticket.status

 $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') ->groupBy('ticket_status.name', 'tickets.created_at') ->get(); 

When I execute this request, I received

 [ { "tickets":"1", "name":"Closed", "created_at":"2016-11-08 14:07:32" }, { "tickets":"1", "name":"Open", "created_at":"2016-11-08 14:07:32" }, { "tickets":"1", "name":"Open", "created_at":"2016-11-11 12:24:39" }, { "tickets":"1", "name":"Open", "created_at":"2016-11-11 12:26:38" }, { "tickets":"1", "name":"Open", "created_at":"2016-11-11 12:27:04" }, { "tickets":"1", "name":"Open", "created_at":"2016-11-11 12:27:49" }, { "tickets":"1", "name":"Open", "created_at":"2016-11-11 12:28:47" }, { "tickets":"1", "name":"Resolved", "created_at":"2016-11-08 14:07:32" } ] 

If $label[0] != $tickets.created , the ticket and name will be empty, but must have a date

Please help me get a conclusion, for example

 [ [ 'tickets'=>0, 'name'=>null,//tickets don't have this date 'created_at'=>'2016-10-16' ], [ 'tickets'=>0, 'name'=>null,//tickets don't have this date 'created_at'=>'2016-10-15' ], [ 'tickets'=>1, 'name'=>'closed',//on this date 1 closed ticket 'created_at'=>'2016-10-14' ], [ 'tickets'=>3, 'name'=>'open',//on this date 3 open ticket 'created_at'=>'2016-10-14' ], [ 'tickets'=>2, 'name'=>'resolved',//on this date 2 resolved ticket 'created_at'=>'2016-10-14'// on 2016-10-14 has three different tickets ], ........... ] 

Please help me find a solution. Thanks in advance.

Answer 1

You will need to convert this to Laravel / Eloquent, but this is a raw db request that does what you are looking for.

Estimated 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, for this in pure MySQL you need a table with all dates. Check out this SO post for an easy way to create a date table.

Answer 2

Use the MySQL date function to format the date first and then group by 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();