[Chartjs]-Iterate over query for use in Charts.js using Coldfusion

by

3đź‘Ť

âś…

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:

enter image description here

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.

0đź‘Ť

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

Leave a comment