cfscript

[coylo]

ChartDirector Support

Forum Home Search

Message List     Post Message    Subscribe

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>

[Dan_Bracuk]

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.

[coylo]

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.

[Adam Cameron.]

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

[Dan_Bracuk]

You don't need a loop.  The general syntax for getting the most recent or highest, or whatever set of data is as follows.  I'll use most recent as an example.

select datefield, some_other_fields

from some_tables

join (

select some_other_fields, max(datefield) maxdatefield

from some_tables

where whatever

group by some_other_fields

) sq on some_tables.some_other_fields = sq.some_other_fields

and datefield = maxdatefield

where whatever