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 **/);
Source: stackoverflow.com
Related Query
- laravel query builder with conditions
- Query with empty results laravel eloquent
- Laravel query builder for Charts.js
- Laravel - Bootstrap Javascript's conflict with Chart.js
- Laravel PDF generation with Graph and send it with Email
- How to pass data in Laravel with Chart.js
- Using chart.js with laravel passing data from controller to view
- Chartjs not working with d3 from csv source
- How to display chart using Chartjs with JSON data in Laravel
- How to display variables from Laravel controller to view javascript with specific index
- Dynamic dataset from MySQL query with Chart.js
- How to write better code in es6 for formatting an object with array values
- Problem with script src for Chart.js. The CDN for Chart.js funtions fine, so my code is ok. Somehow I'm not linking the file correctly
- Updating Chartjs to 2.5 with custom code
- what is wrong with my code ? java script chart position
- Generate PDF from HTML page made with Bootstrap and ChartJS in Laravel
- Removing labels from chart.js with a media query (or change option value)
- Laravel Charts Refresh With Ajax
- Laravel Chart JS change data with dropdown
- Problems with chart.js using Laravel 7, Cannot find element: #app?
- Query result into array in Laravel 6
- Laravel Vue error when trying to use chart.js with Laravel-charts package
- how to load mysql query to chartjs with php?
- How to run Chart.js samples using source code
- Laravel DB select statement. Query returns column name as result rather than values
- How do I implement Laravel Analytics with Chart.js
- Charts.js Formatting Y Axis with both Currency and Thousands Separator
- Custom Legend with ChartJS v2.0
- How can I create a horizontal scrolling Chart.js line chart with a locked y axis?
- Chartjs random colors for each part of pie chart with data dynamically from database
More Query from same tag
- Chart.js data values from textbox input only load once
- How do I make my Type:Time to work with my Line Chart?
- How to hide y axis line in ChartJs?
- Chart.js annotation horizontal line on double y-axis graph
- How to edit my custom tooltips in my line chart using chart.js?
- Hiding the ticks on the x-axes clips the chart (Chart.JS)
- Dynamically updating time data in chart.js freezes the chart
- How to get chartjs column chart in ng-repeat
- Chart.js graph with just two cordinates
- hover mode on Chart.js
- How to append more data in tooltip of graph in chart.js
- Different color for each bar in a bar chart; ChartJS
- chartjs maintain points position, put image alongside with label
- ChartJS - Show percentage base on data on hover (AngularJS)
- chat,js stacked bar chart (make one dataset into stacked)
- how to plot multiple time series in chartjs where each time series has different times
- In rails Label X and Y axis in chart. Implemented through chartkick and chart.js
- How can I move a label left, paint it black, or remove it (Chart.JS)?
- Chart.js - cannot fetch result from MySQL via PHP
- how to make a chart.js bar chart scrollable
- How do I access chart.js options dynamically?
- lazy loaded modules share same dependencies?
- Can't bind to 'chartType' since it isn't a known property of 'canvas' with angular12
- Chart js: how can I align the legend and the title
- Chart.Js how to hide datapoints with "0" value in Stacked Bar Chart
- How using charts.js with JSON?
- Extending Existing Chart Types angular chart js using chart js 2.0
- How to make `fillColor` as gradient in chart.js?
- Execute onClick function on Custom Legend
- How to hide grid lines and x-axis labels in chart.js?