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
- chartjs height does not follow parent container
- Is it possible to get the assigned ref name in a component? Vue.js
- JavaScript doughnut chart with centered hover label
- unable to add background color to the canvas using jspdf and chartjs
- Chat js with respose ajax
- Laravel displaying a chart from another database connection
- react-chartjs-2 Line Chart not updating with State
- Chart.js Radar chart legend label font size doesn't work
- Chart js x-axis values getting repeated twice
- How to increase space between chart and legend? (googlecharts)
- Periodically refresh placeholder and canvas tag in Django template
- Vue.js component for Chart.js - strange issue on AJAX reload
- react-chartjs-2 TypeError: undefined is not an object (evaluating 'nextDatasets.map')
- Chart.js - Scale of secondary Y axis
- How to sum array value in chart.js inside loop function?
- Placing JavaScript content in Bootstrap Popover
- Reactjs- Bar Graph-chartjs- Data not coming in individual blocks
- How to make fixed width of column without ticks.min/max in chart.js?
- I am getting TS2339: Property 'chart' does not exist on type 'Window'?
- How to reach to a chartjs chart's value?
- Why can't I update the aspectRatio within the chartjs chart?
- how do i make ungrouped datasets with charts.js
- Add line from point to x-axis and bold label of him
- How to vary the thickness of doughnut chart, using ChartJs.?
- why tooltip appears wrong in my chart JS?
- Using Chart.js - Creating Legend for Doughnut Chart
- Disable tooltip for Line chart for one line
- Chartjs, scatter with For-Loop
- Chart.js awkward label gaps before last x-axis label
- ng2-charts: How to set fixed range for y axis