Skip to main content
coylo
Known Participant
March 29, 2010
Question

cfscript

ChartDirector Support
Forum HomeForum Home SearchSearch

Message ListMessage List     Post MessagePost Message    Subscribe to forumSubscribe

  cfscript
Posted by kevin on Mar-29-2010 11:00
| Reply | Monitor
I am trying to generate a multi series line graph using 3rd party software. when i loop through the bidders i want data0y and data0x to increment each time i.e. when it goes to bidder 1 it should generate:
<CFSCRIPT>
data0Y = Array(AllBids);
data0X = Array(AllBidDates);
</CFSCRIPT>

bidder 2:

<CFSCRIPT>
data1Y = Array(AllBids);
data1X = Array(AllBidDates);
</CFSCRIPT>

bidder 3:

<CFSCRIPT>
data2Y = Array(AllBids);
data2X = Array(AllBidDates);
</CFSCRIPT>

i have tried things like:
<!--- Find Bidder Position --->
<CFSET Bidderpostemp=ListFind(#AllBidders#, "#GetChartData.BidderID#")>
<CFSET Bidderpos=Bidderpostemp-1>
<CFSCRIPT>
data#Bidderpos#Y = Array(AllBids);
</CFSCRIPT>

but to no avail.

the relevant part of the code is....

<!--- Query to find all the Bidders participating in this Lot --->
<CFQUERY NAME="GetAllBidders" DATASOURCE="#Application.Datasource#">
SELECT BidderID
FROM Bidders
WHERE BidderID IN (<CFQUERYPARAM VALUE="#GetBidders.Bidders#" LIST="YES" CFSQLTYPE="CF_SQL_VARCHAR">) AND (Activated=<CFQUERYPARAM VALUE="Yes" CFSQLTYPE="CF_SQL_CHAR" MAXLENGTH="3">)
</CFQUERY>

<CFSCRIPT>

// ChartDirector for ColdFusion API Access Point
cd = CreateObject("java", "ChartDirector.CFChart");

// A utility to allow us to create arrays with data in one line of code
function Array() {
     var result = ArrayNew(1);
     var i = 0;
     for (i = 1; i LTE ArrayLen(arguments); i = i + 1)
         result = arguments;
     return result;
}

</CFSCRIPT>

<!--- Create a list of all the Bidders --->
<CFSET AllBidders=ValueList(GetAllBidders.BidderID)>

<!--- Loop through the Bidders --->
<CFLOOP INDEX="ListBidders" LIST="#AllBidders#">

<!--- Query to find Data for the Graph --->
<CFQUERY NAME="GetChartData" DATASOURCE="#Application.Datasource#">
SELECT Bids.BidID, Bids.BidderID, Bids.LotID, Bids.Bid, Bids.Bid_Date, Bidders.Company
FROM Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID
WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#URL.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
ORDER BY Bids.Bid_Date ASC;
</CFQUERY>

<!--- Create a list of all the Bids --->
<CFSET AllBids=ValueList(GetChartData.Bid)>
<!--- Create a list of all the Bid Date/Times --->
<CFSET AllBidDates=ValueList(GetChartData.Bid_Date)>

<CFSCRIPT>
data0Y = Array(AllBids);
data0X = Array(AllBidDates);
</CFSCRIPT>

</CFLOOP>
This topic is closed to new replies. Start a new post to keep the conversation going.

4 replies

Dan_Bracuk
Inspiring
March 29, 2010

You are overwriting your variables each time you go through your loop.

Not related to your question, but, going back to the database inside a loop is very inefficient and could cause problems.  A better strategy would be to make one trip to the db and get all the data you need.  Then you can use Q of Q and other things to build your graph.

Translate
coyloAuthor
Known Participant
April 15, 2010

thanks for the Q of Q tip.

however i have a subquery that is also inside a cfloop:

<!--- Query to find the latest/current bid of each of the other Bidders --->
<CFQUERY NAME="GetLastBid" DATASOURCE="#Application.Datasource#">
SELECT DISTINCT Scores.Non_Price_Score, Scores.Price_Score, Bids.BidderID, Bids.Bid
FROM (Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID) INNER JOIN Scores ON (Bids.LotID = Scores.LotID) AND (Bids.BidderID = Scores.BidderID) JOIN
(Select BidderID, MAX(Bid_Date) as Max_Bid_Date
FROM Bids
WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
GROUP BY BidderID) x ON Bids.BidderID = x.BidderID and Bids.Bid_Date = Max_Bid_Date
WHERE (Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">) AND (Bids.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">)
</CFQUERY>


For more efficiency is it possible to do above in Q of Q as in the following:

<CFQUERY NAME="GetBidsScores" DATASOURCE="#Application.Datasource#">
SELECT Bids.BidID, Bids.BidderID, Bids.LotID, Bids.Bid, Bids.Bid_Date, Scores.Non_Price_Score, Scores.Price_Score
FROM (Bids INNER JOIN Bidders ON Bids.BidderID = Bidders.BidderID) INNER JOIN Scores ON (Bids.LotID = Scores.LotID) AND (Bids.BidderID = Scores.BidderID)

WHERE Bids.LotID=<CFQUERYPARAM VALUE="#Form.LotID#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">

</CFQUERY>

<!--- Loop through the other Bidders --->
<CFLOOP INDEX="ListBidders" LIST="#OtherBidders#">

<!--- Query to find the latest/current bid of each of the Bidders --->
<CFQUERY NAME="GetLastBid" DBTYPE="QUERY">
SELECT Non_Price_Score, Price_Score, LotID, BidderID, Bid
FROM GetBidsScores JOIN
(Select GetBidsScores.BidderID, MAX(Bid_Date) as Max_Bid_Date
FROM GetBidsScores
WHERE GetBidsScores.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
GROUP BY BidderID)  x ON GetBidsScores.BidderID = x.BidderID and GetBidsScores.Bid_Date = Max_Bid_Date
WHERE GetBidsScores.BidderID=<CFQUERYPARAM VALUE="#ListBidders#" CFSQLTYPE="CF_SQL_VARCHAR" MAXLENGTH="50">
</CFQUERY>

............

</CFLOOP>

I tried above and got following error.

Query Of Queries syntax error.
Encountered "JOIN.

Translate
Adam Cameron.
Inspiring
April 15, 2010

One cannot use JOIN syntax or have subqueries in a QoQ.  QoQ supports only a very limited subset of SQL constructs. It might be an idea to give the docs a bit of a read:

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html

--

Adam

Translate
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices