Iterate over query for use in Charts.js using Coldfusion

Question

I am trying to figure out how to loop over a query for use with Charts.JS.

The query:

<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>

That gives me data 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
8   Charter Bus 01      3
9   Charter Bus 02      2
10  Charter Bus 03      2

The main excerpt from Chart.JS where I need to loop over this query:

            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 where r_vehicle was not used I need to insert a 'zero' (since they do not appear in the query results).

I have tried this a number of ways but its just a few notches over my paygrade! Much appreciate any help.

UPDATE: This should add the '0' when no vehicle was used in a given 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 loop over the output data like this:

LIMO (r_vehicle) 2,1,0,0,1,0,3,3,3,0,2,0 (vehCount)

2nd 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 doing the output query wrong, this is what I'm getting:

Limo - 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 to this:

<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>

Please note, i changed it from Limo to Sedan because Sedan is missing some months. I am now 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 issues I see remaining:

1) I only need 'sedan' to be written once. 2) I need to fill in the missing months with a 'zero'

Update 4:
Thank you again so much TRose!
So, I now 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'm missing something. I'm playing around with it to see what i can do.

Update 5

This seems to be working, 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!!! Thank you again!!

Works great with the Chart.js script now!

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) The CFOUTPUT stuff (not the ones in the Charts.JS tag) I had to put in CFSILENT tags or the data shows up in HTML.

2) The DB must have the month formatted as MM and not just M.

I think you are misunderstanding how IFNULL works. It cannot add missing records. It simply converts an *existing* NULL value into something else, like 0. — Leigh, Mar 26 '17 at 20:22
@Leigh he was on the right track. It was my first instinct. Decided it would be easier to just put everything in a struct with default values of zero, then update that using his query. Simpler than SQL witchcraft I think. — TRose, Mar 26 '17 at 20:49
@SFlynn if you're going to try the Struct method there's no use in replacing Nulls with zeroes in your query. That `vehcount` variable is likely the culprit. Just eyeballing everything, that's the only difference in our code. — TRose, Mar 26 '17 at 21:47
@TRose - i just got rid of vehcount. Edited my original post with a 3rd Update. Cheers! — S Flynn, Mar 26 '17 at 22:21
@TRose - Since there are only 12 months, it would not be hard generate the missing counts in SQL. Most likely with a single query. Though with either approach, the table should be normalized and vehicle name stored in a separate table. — Leigh, Mar 26 '17 at 23:12
You're most likely an SQL wizard compared to me so I can't dispute that. Although even I know database normalization is hugely important for large applications. Seems most businesses running ColdFusion ignore that... — TRose, Mar 26 '17 at 23:18
@TRose - Not just for large apps. It is good for performance, but also helps ensures data integrity, regardless of the table size. So there is not risk of silently dropping data if some records were accidentally inserted as "Limos" instead "Limo". Also, you would not have to query the entire reservations table to do things like get a unique list of vehicles. BTW, I am not saying structures is a bad way to go. For only two or three vehicle types, any differences probably are not that significant. — Leigh, Mar 27 '17 at 00:20
Big thank you to TRose (and Leigh!) for the help on this. Incredibly generous of you. This really opened me up because now I can do it for all these other charts I have to build! — S Flynn, Mar 27 '17 at 14:07

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

This is more of a super-long comment. Just kinda sorting out the logic. Arrays and Lists are the bane of my existence and are always tricky to handle in an efficient manner, but I can give you a couple hints at least.

Like... Structs are your friend!

I just looked up the format for Chart.js. You can select Limos and Charter Buses separately and then loop over either query as separate datasets on the same graph. I think that's important so we can focus on the Month only, which is what you need to work on, yes?

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>

It would 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 is I made a List with all the months of the year, formatted like how they appear in your database.

I looped over that list to create a Struct containing all the months of the year and a default "Count" of zero.

<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>

Next, grab the data you actually have for the months the Limo was rented. Loop through that query and update your Struct.

<cfoutput query="bymonthLimos">
<cfset StructUpdate(MonthValues, "Month#r_month#", #count#)>
</cfoutput>

Now you have the correct count for every month, in order. I dumped it, and should look like this given your data:

From here you can loop over it any way you see fit to generate that list of numbers and plug it into Chart.js.

You can create another dataset for any other type of vehicle you want in this same way.

EDIT

I see you've altered your query to include 0s. If you still need to generate a list for Chart.js, try valueList(). Ex:

<cfset MonthCount = ValueList(bymonth2.COUNT, ",")>

EDIT 2

Okay, replace this:

<cfset MonthValues = structNew()>

With this.

<cfset MonthValues = createObject("java", "java.util.LinkedHashMap").init() />

They do the exact same thing, but the 2nd one keeps the Struct Information in specific order.

After that, you can loop through it to get the values.

<cfoutput>
<cfloop collection="#MonthValues#" item="key">
     #key#: #MonthValues[key]# <br />
</cfloop>
</cfoutput>

You only want the #MonthValues[key]# (the count) which would produce 2, 1, 0, 0, 1, 0, 3, 3, 3, 0, 2, 0 but I included everything for clarity.

Once you loop over that you've got your list. Just feed it to your Chart plugin and format it. If I'm interpreting their info page correctly, you can make however many labels you want for your data.

I've taken the liberty of filling out 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)"
        }]
      }
    });

And so on.

First off, THANK YOU for taking the time to go through this, TRose. I have spun wheels for most of my Sunday on this. I'm still getting some issues. I'll post them. — S Flynn, Mar 26 '17 at 21:33
@SFlynn I made one last mega-edit to my answer. I think you're in the clear. — TRose, Mar 26 '17 at 22:59

score 0 · Answer 2 · edited May 23 '17 at 11:54

Using your existing table, another approach is to use a "numbers table" to generate the missing months. There are different ways to implement it: as a physical table, derived table, or CTE. (MySQL does not yet support CTE's, but you could easily do something similar using a VIEW). Then use the numbers table with an OUTER JOIN to fill in any missing month numbers:

_{NB: Assuming the table contains a "Year" column, you may want to filter the derived query on year number as well.}

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 the list of values to use in your chart:

  <cfoutput>
   datasets: [{
          label: 'Limos',
          data: [ #ValueList(yourLimoQuery.ReservationCount)# ]
   ]
   ... 
  </cfoutput>

As an aside, you might also consider normalizing the table to help 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 store the vehicle "ID" (not "name") in the reservations table. For greater flexibility in reporting/charting queries, store a full "date", rather than just month/year number.

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)
);

Iterate over query for use in Charts.js using Coldfusion

2 Answers2

EDIT

EDIT 2