Iterating on demand for use in Charts.js using Coldfusion
I'm trying to figure out how to iterate over a request for use with Charts.JS.
Request:
<cfquery name="bymonth" datasource="#application.dsn#">
SELECT DISTINCT r_vehicle, r_month, COUNT(*)
FROM rsvs
GROUP BY r_vehicle, r_month
ORDER BY r_vehicle, r_month;
</cfquery>
This gives me the following data:
r_vehicle r_month COUNT(*)
1 Limo 01 2
2 Limo 02 1
3 Limo 05 1
4 Limo 07 3
5 Limo 08 3
6 Limo 09 3
7 Limo 11 2
8 Charter Bus 01 3
9 Charter Bus 02 2
10 Charter Bus 03 2
Main excerpt from Chart.JS where I need to loop over this request:
labels: ["January", "February", "March", "April", "May",
"June", "July", "August", "September", "October", "November", "December"],
{
label: "r_vehicle",
backgroundColor: window.chartColors.red,
borderColor: window.chartColors.red,
data: [
(Number of times this vehicle was used per month -
Displayed as: 3,6,5,2,3,7,9,2,3,8,3,2)
],
fill: false,
}
For any months that r_vehicle has not been used, I need to insert "null" (since they do not appear in the query results).
I've tried this in several ways, but it's just a few notches above my paid subscription! I really appreciate any help.
UPDATE: This should add "0" if the vehicle has not been used in a month:
<cfquery name="bymonth2" datasource="#application.dsn#">
SELECT DISTINCT r_vehicle, r_month, IFNULL(count(r_vehicle),0) AS vehCount
FROM rsvs
GROUP BY r_vehicle, r_month
ORDER BY r_vehicle, r_month;
</cfquery>
So at this point, I think I just need to know how to iterate over the output like this:
LIMO (r_vehicle) 2,1,0,0,1,0,3,3,3,0,2,0 (vehCount)
Second update: I have the following code -
<cfquery name="bymonthLimos" datasource="#application.dsn#">
SELECT DISTINCT r_vehicle, r_month, IFNULL(count(r_vehicle),0) AS vehCount
FROM rsvs
WHERE r_vehicle = 'Limo'
GROUP BY r_vehicle, r_month
ORDER BY r_vehicle, r_month
</cfquery>
<cfset MonthList = "01,02,03,04,05,06,07,08,09,10,11,12">
<cfset MonthValues = structNew()>
<cfloop list="#MonthList#" index="index" delimiters=",">
<cfset structInsert(MonthValues,"Month#index#", "0")>
</cfloop>
<cfoutput query="bymonthLimos">
<cfset MonthCount = ValueList(bymonth2.vehCount, ",")>
#r_vehicle# - #MonthCount#<br>
</cfoutput>
I must be drawing the wrong conclusion, this is what I get:
Limousine - 3,2,2,2,1,1,3,4,3,5,5,7,1,5,13,17,16,12,17,7,16,7,9,13, 1,3,8,7,7,7,13,9,5,6,7,12,3,3,8,10,3,7,7,5,8,1,3,7,1, 1,3,4,7,7,2,1,2,1,1,3,3,3,2,1,1,3,1,2,3,5,5,1,2, 1, 1,2,1,1,5,3,6,7,8,6,11,8,7,3
3rd Update Changed code:
<cfquery name="bymonthLimos" datasource="#application.dsn#">
SELECT DISTINCT r_vehicle, r_month, COUNT(*) as count
FROM rsvs
WHERE r_vehicle = 'Sedan'
GROUP BY r_vehicle, r_month
ORDER BY r_vehicle, r_month
</cfquery>
<cfset MonthList = "01,02,03,04,05,06,07,08,09,10,11,12">
<cfset MonthValues = structNew()>
<cfloop list="#MonthList#" index="index" delimiters=",">
<cfset structInsert(MonthValues,"Month#index#", "0")>
</cfloop>
<cfoutput query="bymonthLimos">
<cfset StructUpdate(MonthValues, "Month#r_month#", #count#)>
#r_vehicle# - #count#<br>
</cfoutput>
Note: I changed it from Limo to Sedan because the Sedan has had a few months. I am currently getting:
Sedan - 1 Sedan - 1 Sedan - 3 Sedan - 1 Sedan - 2 Sedan - 3 Sedan - 5 Sedan - 5 Sedan - 1 Sedan - 2
Thank you TRose !!!
So the two problems I see are:
1) I only need a "sedan", which needs to be written once. 2) I need to fill the missing months with zero
Update 4:
Thanks again TROSE!
So now I have the following code:
<cfquery name="bymonthLimos" datasource="#application.dsn#">
SELECT DISTINCT r_vehicle, r_month, COUNT(*) as count
FROM rsvs
WHERE r_vehicle = 'Sedan'
GROUP BY r_vehicle, r_month
ORDER BY r_vehicle, r_month
</cfquery>
<cfset MonthList = "01,02,03,04,05,06,07,08,09,10,11,12">
<cfset MonthValues = createObject("java", "java.util.LinkedHashMap").init() />
<cfloop list="#MonthList#" index="index" delimiters=",">
<cfset structInsert(MonthValues,"Month#index#", "0")>
</cfloop>
OUTPUT:
<cfoutput>
<cfloop collection="#MonthValues#" item="key">
#key#: #MonthValues[key]# <br />
</cfloop>
</cfoutput>
<br>
OUTPUT2:
<cfoutput>
<cfset step = 0>
<cfloop collection="#MonthValues#" item="key">
<cfset step++>#MonthValues[key]#<cfif step lt 12>,</cfif>
</cfloop>
</cfoutput>
<br>
OUTPUT3:
<cfoutput query="bymonthLimos">#r_vehicle# -
<cfset StructUpdate(MonthValues, "Month#r_month#", #count#)>
#count#<br>
</cfoutput>
Results:
OUTPUT: Month01: 0 Month02: 0 Month03: 0 Month04: 0 Month05: 0 Month06: 0 Month07: 0 Month08: 0 Month09: 0 Month10: 0 Month11: 0 Month12: 0
OUTPUT2: 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
OUTPUT3: Sedan - 1 Sedan - 1 Sedan - 3 Sedan - 1 Sedan - 2 Sedan - 3 Sedan - 5 Sedan - 5 Sedan - 1 Sedan - 2
Obviously I am missing something. I play with him to see what I can do.
Update 5
It seems to work, not sure what it was!
OUTPUT4:
<cfoutput>
#bymonthLimos.r_vehicle#
<cfset step = 0>
<cfloop collection="#MonthValues#" item="key">
<cfset step++>#MonthValues[key]#<cfif step lt 12>,</cfif>
</cfloop>
</cfoutput>
Results:
OUTPUT4: Sedan 1, 1, 3, 0, 0, 1, 2, 3, 5, 5, 1, 2
Yahoo !!! Thanks again!
Now works great with Chart.js script!
data: [
<cfoutput>
<cfset step = 0>
<cfloop collection="#MonthValues#" item="key">
<cfset step++>#MonthValues[key]#<cfif step lt 12>,</cfif>
</cfloop>
</cfoutput>
],
Just wanted to point out some things:
1) CFOUTPUT stuff (not the ones in the Charts.JS tag) I had to put CFSILENT tags or the data is rendered in HTML.
2) The DB must have a month formatted as MM, not just M.
source to share
This is a longer comment. Just curious to figure out the logic. Arrays and Lists are the bane of my existence and are always difficult to manage effectively, but I can give you a couple of hints.
How ... Structures are your friend!
I just looked at the Chart.js format. You can select Limousines and Charter Buses separately, and then iterate over any query as separate datasets on one graph. I think this is important, so we can only focus on the Month, what do you need to work with, huh?
Source: https://www.sitepoint.com/introduction-chart-js-2-0-six-examples/
So, for example, for Limos:
<cfquery name="bymonthLimos" datasource="#application.dsn#">
SELECT DISTINCT r_vehicle, r_month, COUNT(*)
FROM rsvs
GROUP BY r_vehicle, r_month
ORDER BY r_vehicle, r_month
WHERE r_vehicle LIKE 'Limo';
</cfquery>
This will give you a result set like this:
r_vehicle r_month COUNT(*)
1 Limo 01 2
2 Limo 02 1
3 Limo 05 1
4 Limo 07 3
5 Limo 08 3
6 Limo 09 3
7 Limo 11 2
So what I did, I created a list with all the months of the year, formatted as they appear in your database.
I looped over this list to create a Struct containing all the months of the year and a default "Count".
<cfset MonthList = "01,02,03,04,05,06,07,08,09,10,11,12">
<cfset MonthValues = structNew()>
<cfloop list="#MonthList#" index="index" delimiters=",">
<cfset structInsert(MonthValues,"Month#index#", "0")>
</cfloop>
Then take the data you actually have for months of limousine rental. Skip this request and update your Struct.
<cfoutput query="bymonthLimos">
<cfset StructUpdate(MonthValues, "Month#r_month#", #count#)>
</cfoutput>
You now have the correct account for each month, in order. I dropped it and it should look like this:
From here, you can iterate over it in whatever way you think is appropriate to create this list of numbers and connect it to Chart.js.
You can create a different dataset for any other vehicle type you want in the same way.
EDIT
I see that you changed your request to include 0s. If you still need to create a list for Chart.js, try the List () value. Example:
<cfset MonthCount = ValueList(bymonth2.COUNT, ",")>
EDIT 2
Ok, replace this:
<cfset MonthValues = structNew()>
With this.
<cfset MonthValues = createObject("java", "java.util.LinkedHashMap").init() />
They do the same thing, but the second keeps the structure information in a specific order.
After that, you can skip it to get the values.
<cfoutput>
<cfloop collection="#MonthValues#" item="key">
#key#: #MonthValues[key]# <br />
</cfloop>
</cfoutput>
You only need the #MonthValues[key]#
(bill) that will produce 2, 1, 0, 0, 1, 0, 3, 3, 3, 0, 2, 0
, but I've included everything for clarity.
After you finish the loop, you have your list. Just load it into your Chart plugin and format it. If I am interpreting your information page correctly, you can make as many shortcuts as you want for your data.
I took advantage of the freedom to fill in the first data (Limos) - so the code will look something like this when you're done.
data: {
labels: ["January", "February", "March", "April", "May",
"June", "July", "August", "September", "October", "November", "December"],
datasets: [{
label: 'Limos',
data: [
<cfoutput>
<cfset step = 0>
<cfloop collection="#MonthValues#" item="key">
<cfset step++>#MonthValues[key]#<cfif step lt 12>,</cfif>
</cfloop>
</cfoutput>
],
backgroundColor: "rgba(153,255,51,1)"
},
{
label: 'Charter Buses',
data: [
YOUR DELIMITED DATA FOR CHARTER BUSES HERE
],
backgroundColor: "rgba(255,153,0,1)"
}]
}
});
Etc.
source to share
Using an existing table, another approach is to use a "number table" to generate the missing months. There are various ways to implement it: as a physical table, a view , or a CTE . (MySQL doesn't support CTEs yet, but you can easily do something like this with VIEW ). Then use a table of numbers with an OUTER JOIN to fill in any numbers of the missing months:
NB . Assuming the table contains a Year column, you can also filter the derived query by year number.
SELECT mo.Num AS MonthNum, COALESCE(res.ReservationCount, 0) AS ReservationCount
FROM YourNumbersTable mo LEFT JOIN
(
SELECT r_month, COUNT(*) as ReservationCount
FROM rsvs
WHERE r_vehicle = <cfqueryparam value="Limos" cfsqltype="cf_sql_varchar">
GROUP BY r_month
)
res ON res.r_month = mo.Num
WHERE mo.Num BETWEEN 1 AND 12
ORDER BY mo.Num
Then a simple ValueList () generates a list of values used in your chart:
<cfoutput>
datasets: [{
label: 'Limos',
data: [ #ValueList(yourLimoQuery.ReservationCount)# ]
]
...
</cfoutput>
As an aside, you might also consider normalizing the table to ensure data integrity and improve performance. Create a separate table for unique vehicle types:
CREATE TABLE Vehicle (
VehicleID INT NOT NULL,
VehicleName VARCHAR(100) NULL,
PRIMARY KEY (VehicleID)
);
Then save the vehicle ID (not the "name") in the booking table. For more flexibility in reports / graphical queries, store the full "date", not just the month / year.
CREATE TABLE VehicleReservation (
VehicleReservationID INT AUTO_INCREMENT,
VehicleID INT NULL,
ReservationDate DATETIME NULL,
PRIMARY KEY (VehicleReservationID),
KEY VehicleID (VehicleID),
CONSTRAINT fKVehicleReservation_VehicleID FOREIGN KEY (VehicleID) REFERENCES vehicle (VehicleID)
);
source to share