create a morris chart in Laravel
so I was able to build a Morris chart following some tutorials, and this is what I've come up with so far
$range = CarbonCarbon::now()->subDays(30);
$stats = DB::table('tickets')
->where('created_at', '>=', $range)
->groupBy('date')
->orderBy('date', 'ASC')
->get([
DB::raw('Date(created_at) as date'),
DB::raw('COUNT(*) as value')
])
->toJSON();
OUTPUT
"[{"date":"2018-11-10","value":1},{"date":"2018-11-11","value":1}]"
the thing is I'm trying to get the results by months and ticket status not daily.
js code
Morris.Area({
element: 'morris-area-chart'
, data: data
, xkey: 'date'
, ykeys: ['status']
});
Tickets Table
id|phone_model |cus_name |issue |notes |created_at |updated_at |status
Expected Output
[{"date":"11","tickets":2,"status":1},{"date":"11","tickets":1,"status":2},
{"date":"10","tickets":3,"status":1},{"date":"10","tickets":1,"status":3},]
I tried more than a way to get the results based on months and ticket status but no luck
any help will be appreciated, Thank you.
php mysql laravel
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
add a comment |
so I was able to build a Morris chart following some tutorials, and this is what I've come up with so far
$range = CarbonCarbon::now()->subDays(30);
$stats = DB::table('tickets')
->where('created_at', '>=', $range)
->groupBy('date')
->orderBy('date', 'ASC')
->get([
DB::raw('Date(created_at) as date'),
DB::raw('COUNT(*) as value')
])
->toJSON();
OUTPUT
"[{"date":"2018-11-10","value":1},{"date":"2018-11-11","value":1}]"
the thing is I'm trying to get the results by months and ticket status not daily.
js code
Morris.Area({
element: 'morris-area-chart'
, data: data
, xkey: 'date'
, ykeys: ['status']
});
Tickets Table
id|phone_model |cus_name |issue |notes |created_at |updated_at |status
Expected Output
[{"date":"11","tickets":2,"status":1},{"date":"11","tickets":1,"status":2},
{"date":"10","tickets":3,"status":1},{"date":"10","tickets":1,"status":3},]
I tried more than a way to get the results based on months and ticket status but no luck
any help will be appreciated, Thank you.
php mysql laravel
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
If you want to show it by month then you should probably put the month name into your output instead of the whole date.
– ADyson
Nov 20 '18 at 12:03
add a comment |
so I was able to build a Morris chart following some tutorials, and this is what I've come up with so far
$range = CarbonCarbon::now()->subDays(30);
$stats = DB::table('tickets')
->where('created_at', '>=', $range)
->groupBy('date')
->orderBy('date', 'ASC')
->get([
DB::raw('Date(created_at) as date'),
DB::raw('COUNT(*) as value')
])
->toJSON();
OUTPUT
"[{"date":"2018-11-10","value":1},{"date":"2018-11-11","value":1}]"
the thing is I'm trying to get the results by months and ticket status not daily.
js code
Morris.Area({
element: 'morris-area-chart'
, data: data
, xkey: 'date'
, ykeys: ['status']
});
Tickets Table
id|phone_model |cus_name |issue |notes |created_at |updated_at |status
Expected Output
[{"date":"11","tickets":2,"status":1},{"date":"11","tickets":1,"status":2},
{"date":"10","tickets":3,"status":1},{"date":"10","tickets":1,"status":3},]
I tried more than a way to get the results based on months and ticket status but no luck
any help will be appreciated, Thank you.
php mysql laravel
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
so I was able to build a Morris chart following some tutorials, and this is what I've come up with so far
$range = CarbonCarbon::now()->subDays(30);
$stats = DB::table('tickets')
->where('created_at', '>=', $range)
->groupBy('date')
->orderBy('date', 'ASC')
->get([
DB::raw('Date(created_at) as date'),
DB::raw('COUNT(*) as value')
])
->toJSON();
OUTPUT
"[{"date":"2018-11-10","value":1},{"date":"2018-11-11","value":1}]"
the thing is I'm trying to get the results by months and ticket status not daily.
js code
Morris.Area({
element: 'morris-area-chart'
, data: data
, xkey: 'date'
, ykeys: ['status']
});
Tickets Table
id|phone_model |cus_name |issue |notes |created_at |updated_at |status
Expected Output
[{"date":"11","tickets":2,"status":1},{"date":"11","tickets":1,"status":2},
{"date":"10","tickets":3,"status":1},{"date":"10","tickets":1,"status":3},]
I tried more than a way to get the results based on months and ticket status but no luck
any help will be appreciated, Thank you.
php mysql laravel
php mysql laravel
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
edited Nov 20 '18 at 12:53
Aboud Jouda
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
asked Nov 20 '18 at 12:00
Aboud JoudaAboud Jouda
306
306
If you want to show it by month then you should probably put the month name into your output instead of the whole date.
– ADyson
Nov 20 '18 at 12:03
add a comment |
If you want to show it by month then you should probably put the month name into your output instead of the whole date.
– ADyson
Nov 20 '18 at 12:03
If you want to show it by month then you should probably put the month name into your output instead of the whole date.
– ADyson
Nov 20 '18 at 12:03
If you want to show it by month then you should probably put the month name into your output instead of the whole date.
– ADyson
Nov 20 '18 at 12:03
add a comment |
1 Answer
1
active
oldest
votes
You can use this query:
$result_arr = DB::select("SELECT DATE_FORMAT(created_at, '%m') date, status, COUNT(*) count FROM tickets GROUP BY date, status ORDER BY date DESC");
return collect($result_arr)->toJson();
It outputs such a result for example(Output is in mysql console here):
+------+--------+-------+
| date | status | count |
+------+--------+-------+
| 12 | 1 | 1 |
| 12 | 2 | 3 |
| 12 | 4 | 1 |
| 12 | 10 | 7 |
| 11 | 1 | 1 |
| 11 | 2 | 1 |
| 11 | 4 | 1 |
| 10 | 1 | 3 |
| 10 | 2 | 2 |
| 10 | 3 | 1 |
| 10 | 4 | 2 |
| 09 | 1 | 2 |
| 09 | 2 | 7 |
| 09 | 3 | 5 |
| 08 | 1 | 1 |
| 08 | 2 | 1 |
| 08 | 12 | 1 |
| 07 | 1 | 4 |
| 06 | 1 | 1 |
| 06 | 2 | 12 |
| 06 | 3 | 2 |
| 05 | 1 | 1 |
| 05 | 2 | 8 |
| 05 | 11 | 1 |
| 04 | 1 | 2 |
| 04 | 2 | 8 |
| 04 | 10 | 1 |
| 03 | 1 | 2 |
| 02 | 1 | 2 |
| 02 | 2 | 26 |
| 02 | 3 | 1 |
| 02 | 11 | 2 |
| 02 | 12 | 1 |
| 01 | 2 | 21 |
| 01 | 3 | 8 |
| 01 | 10 | 6 |
+------+--------+-------+
36 rows in set (0.00 sec) Keep in mind that as you want to group the results based on both status & date it does not give you just a row for each date(as you may have multiple statuses), so for each date you may have different values for each status.
See Mysql Docs for more about DATE_FORMAT.
answered Nov 20 '18 at 12:38
akoako
7861321
Thank you very much, work's like a charm
– Aboud Jouda
Nov 20 '18 at 12:54
1
Absolutely it must work like a charm. :-)
– ako
Nov 20 '18 at 12:55
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53392553%2fcreate-a-morris-chart-in-laravel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use this query:
$result_arr = DB::select("SELECT DATE_FORMAT(created_at, '%m') date, status, COUNT(*) count FROM tickets GROUP BY date, status ORDER BY date DESC");
return collect($result_arr)->toJson();
It outputs such a result for example(Output is in mysql console here):
+------+--------+-------+
| date | status | count |
+------+--------+-------+
| 12 | 1 | 1 |
| 12 | 2 | 3 |
| 12 | 4 | 1 |
| 12 | 10 | 7 |
| 11 | 1 | 1 |
| 11 | 2 | 1 |
| 11 | 4 | 1 |
| 10 | 1 | 3 |
| 10 | 2 | 2 |
| 10 | 3 | 1 |
| 10 | 4 | 2 |
| 09 | 1 | 2 |
| 09 | 2 | 7 |
| 09 | 3 | 5 |
| 08 | 1 | 1 |
| 08 | 2 | 1 |
| 08 | 12 | 1 |
| 07 | 1 | 4 |
| 06 | 1 | 1 |
| 06 | 2 | 12 |
| 06 | 3 | 2 |
| 05 | 1 | 1 |
| 05 | 2 | 8 |
| 05 | 11 | 1 |
| 04 | 1 | 2 |
| 04 | 2 | 8 |
| 04 | 10 | 1 |
| 03 | 1 | 2 |
| 02 | 1 | 2 |
| 02 | 2 | 26 |
| 02 | 3 | 1 |
| 02 | 11 | 2 |
| 02 | 12 | 1 |
| 01 | 2 | 21 |
| 01 | 3 | 8 |
| 01 | 10 | 6 |
+------+--------+-------+
36 rows in set (0.00 sec) Keep in mind that as you want to group the results based on both status & date it does not give you just a row for each date(as you may have multiple statuses), so for each date you may have different values for each status.
See Mysql Docs for more about DATE_FORMAT.
answered Nov 20 '18 at 12:38
akoako
7861321
Thank you very much, work's like a charm
– Aboud Jouda
Nov 20 '18 at 12:54
1
Absolutely it must work like a charm. :-)
– ako
Nov 20 '18 at 12:55
add a comment |
You can use this query:
$result_arr = DB::select("SELECT DATE_FORMAT(created_at, '%m') date, status, COUNT(*) count FROM tickets GROUP BY date, status ORDER BY date DESC");
return collect($result_arr)->toJson();
It outputs such a result for example(Output is in mysql console here):
+------+--------+-------+
| date | status | count |
+------+--------+-------+
| 12 | 1 | 1 |
| 12 | 2 | 3 |
| 12 | 4 | 1 |
| 12 | 10 | 7 |
| 11 | 1 | 1 |
| 11 | 2 | 1 |
| 11 | 4 | 1 |
| 10 | 1 | 3 |
| 10 | 2 | 2 |
| 10 | 3 | 1 |
| 10 | 4 | 2 |
| 09 | 1 | 2 |
| 09 | 2 | 7 |
| 09 | 3 | 5 |
| 08 | 1 | 1 |
| 08 | 2 | 1 |
| 08 | 12 | 1 |
| 07 | 1 | 4 |
| 06 | 1 | 1 |
| 06 | 2 | 12 |
| 06 | 3 | 2 |
| 05 | 1 | 1 |
| 05 | 2 | 8 |
| 05 | 11 | 1 |
| 04 | 1 | 2 |
| 04 | 2 | 8 |
| 04 | 10 | 1 |
| 03 | 1 | 2 |
| 02 | 1 | 2 |
| 02 | 2 | 26 |
| 02 | 3 | 1 |
| 02 | 11 | 2 |
| 02 | 12 | 1 |
| 01 | 2 | 21 |
| 01 | 3 | 8 |
| 01 | 10 | 6 |
+------+--------+-------+
36 rows in set (0.00 sec) Keep in mind that as you want to group the results based on both status & date it does not give you just a row for each date(as you may have multiple statuses), so for each date you may have different values for each status.
See Mysql Docs for more about DATE_FORMAT.
answered Nov 20 '18 at 12:38
akoako
7861321
Thank you very much, work's like a charm
– Aboud Jouda
Nov 20 '18 at 12:54
1
Absolutely it must work like a charm. :-)
– ako
Nov 20 '18 at 12:55
add a comment |
You can use this query:
$result_arr = DB::select("SELECT DATE_FORMAT(created_at, '%m') date, status, COUNT(*) count FROM tickets GROUP BY date, status ORDER BY date DESC");
return collect($result_arr)->toJson();
It outputs such a result for example(Output is in mysql console here):
+------+--------+-------+
| date | status | count |
+------+--------+-------+
| 12 | 1 | 1 |
| 12 | 2 | 3 |
| 12 | 4 | 1 |
| 12 | 10 | 7 |
| 11 | 1 | 1 |
| 11 | 2 | 1 |
| 11 | 4 | 1 |
| 10 | 1 | 3 |
| 10 | 2 | 2 |
| 10 | 3 | 1 |
| 10 | 4 | 2 |
| 09 | 1 | 2 |
| 09 | 2 | 7 |
| 09 | 3 | 5 |
| 08 | 1 | 1 |
| 08 | 2 | 1 |
| 08 | 12 | 1 |
| 07 | 1 | 4 |
| 06 | 1 | 1 |
| 06 | 2 | 12 |
| 06 | 3 | 2 |
| 05 | 1 | 1 |
| 05 | 2 | 8 |
| 05 | 11 | 1 |
| 04 | 1 | 2 |
| 04 | 2 | 8 |
| 04 | 10 | 1 |
| 03 | 1 | 2 |
| 02 | 1 | 2 |
| 02 | 2 | 26 |
| 02 | 3 | 1 |
| 02 | 11 | 2 |
| 02 | 12 | 1 |
| 01 | 2 | 21 |
| 01 | 3 | 8 |
| 01 | 10 | 6 |
+------+--------+-------+
36 rows in set (0.00 sec) Keep in mind that as you want to group the results based on both status & date it does not give you just a row for each date(as you may have multiple statuses), so for each date you may have different values for each status.
See Mysql Docs for more about DATE_FORMAT.
answered Nov 20 '18 at 12:38
akoako
7861321
You can use this query:
$result_arr = DB::select("SELECT DATE_FORMAT(created_at, '%m') date, status, COUNT(*) count FROM tickets GROUP BY date, status ORDER BY date DESC");
return collect($result_arr)->toJson();
It outputs such a result for example(Output is in mysql console here):
+------+--------+-------+
| date | status | count |
+------+--------+-------+
| 12 | 1 | 1 |
| 12 | 2 | 3 |
| 12 | 4 | 1 |
| 12 | 10 | 7 |
| 11 | 1 | 1 |
| 11 | 2 | 1 |
| 11 | 4 | 1 |
| 10 | 1 | 3 |
| 10 | 2 | 2 |
| 10 | 3 | 1 |
| 10 | 4 | 2 |
| 09 | 1 | 2 |
| 09 | 2 | 7 |
| 09 | 3 | 5 |
| 08 | 1 | 1 |
| 08 | 2 | 1 |
| 08 | 12 | 1 |
| 07 | 1 | 4 |
| 06 | 1 | 1 |
| 06 | 2 | 12 |
| 06 | 3 | 2 |
| 05 | 1 | 1 |
| 05 | 2 | 8 |
| 05 | 11 | 1 |
| 04 | 1 | 2 |
| 04 | 2 | 8 |
| 04 | 10 | 1 |
| 03 | 1 | 2 |
| 02 | 1 | 2 |
| 02 | 2 | 26 |
| 02 | 3 | 1 |
| 02 | 11 | 2 |
| 02 | 12 | 1 |
| 01 | 2 | 21 |
| 01 | 3 | 8 |
| 01 | 10 | 6 |
+------+--------+-------+
36 rows in set (0.00 sec) Keep in mind that as you want to group the results based on both status & date it does not give you just a row for each date(as you may have multiple statuses), so for each date you may have different values for each status.
See Mysql Docs for more about DATE_FORMAT.
+------+--------+-------+
| date | status | count |
+------+--------+-------+
| 12 | 1 | 1 |
| 12 | 2 | 3 |
| 12 | 4 | 1 |
| 12 | 10 | 7 |
| 11 | 1 | 1 |
| 11 | 2 | 1 |
| 11 | 4 | 1 |
| 10 | 1 | 3 |
| 10 | 2 | 2 |
| 10 | 3 | 1 |
| 10 | 4 | 2 |
| 09 | 1 | 2 |
| 09 | 2 | 7 |
| 09 | 3 | 5 |
| 08 | 1 | 1 |
| 08 | 2 | 1 |
| 08 | 12 | 1 |
| 07 | 1 | 4 |
| 06 | 1 | 1 |
| 06 | 2 | 12 |
| 06 | 3 | 2 |
| 05 | 1 | 1 |
| 05 | 2 | 8 |
| 05 | 11 | 1 |
| 04 | 1 | 2 |
| 04 | 2 | 8 |
| 04 | 10 | 1 |
| 03 | 1 | 2 |
| 02 | 1 | 2 |
| 02 | 2 | 26 |
| 02 | 3 | 1 |
| 02 | 11 | 2 |
| 02 | 12 | 1 |
| 01 | 2 | 21 |
| 01 | 3 | 8 |
| 01 | 10 | 6 |
+------+--------+-------+
36 rows in set (0.00 sec) +------+--------+-------+
| date | status | count |
+------+--------+-------+
| 12 | 1 | 1 |
| 12 | 2 | 3 |
| 12 | 4 | 1 |
| 12 | 10 | 7 |
| 11 | 1 | 1 |
| 11 | 2 | 1 |
| 11 | 4 | 1 |
| 10 | 1 | 3 |
| 10 | 2 | 2 |
| 10 | 3 | 1 |
| 10 | 4 | 2 |
| 09 | 1 | 2 |
| 09 | 2 | 7 |
| 09 | 3 | 5 |
| 08 | 1 | 1 |
| 08 | 2 | 1 |
| 08 | 12 | 1 |
| 07 | 1 | 4 |
| 06 | 1 | 1 |
| 06 | 2 | 12 |
| 06 | 3 | 2 |
| 05 | 1 | 1 |
| 05 | 2 | 8 |
| 05 | 11 | 1 |
| 04 | 1 | 2 |
| 04 | 2 | 8 |
| 04 | 10 | 1 |
| 03 | 1 | 2 |
| 02 | 1 | 2 |
| 02 | 2 | 26 |
| 02 | 3 | 1 |
| 02 | 11 | 2 |
| 02 | 12 | 1 |
| 01 | 2 | 21 |
| 01 | 3 | 8 |
| 01 | 10 | 6 |
+------+--------+-------+
36 rows in set (0.00 sec) answered Nov 20 '18 at 12:38
akoako
7861321
edited Nov 20 '18 at 12:49
answered Nov 20 '18 at 12:38
akoako
7861321
answered Nov 20 '18 at 12:38
akoako
7861321
answered Nov 20 '18 at 12:38
akoako
7861321
7861321
Thank you very much, work's like a charm
– Aboud Jouda
Nov 20 '18 at 12:54
1
Absolutely it must work like a charm. :-)
– ako
Nov 20 '18 at 12:55
add a comment |
Thank you very much, work's like a charm
– Aboud Jouda
Nov 20 '18 at 12:54
1
Absolutely it must work like a charm. :-)
– ako
Nov 20 '18 at 12:55
Thank you very much, work's like a charm
– Aboud Jouda
Nov 20 '18 at 12:54
Thank you very much, work's like a charm
– Aboud Jouda
Nov 20 '18 at 12:54
1
1
Absolutely it must work like a charm. :-)
– ako
Nov 20 '18 at 12:55
Absolutely it must work like a charm. :-)
– ako
Nov 20 '18 at 12:55
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53392553%2fcreate-a-morris-chart-in-laravel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
If you want to show it by month then you should probably put the month name into your output instead of the whole date.
– ADyson
Nov 20 '18 at 12:03