Coldfusion: QueryOfQueries or adding QueryColumn to display results based on DB query AND calculated value thereof

Question

I have searched extensively but am unable to find something that applies to my task - fully aware that it could be my search terms not leading to a result, so I am grateful for any pointers in the right direction.

Application Server: Coldfusion Application Server 10 Enterprise (U13) Database: mySQL 5 Community Edition

Task: Show list of suitable machines based on user input.

The user inputs certain parameters which are calculated and then retrieved from the mySQL database. This is step 1 and works fine:

<cfquery name="qmodells" datasource="dproducts">
SELECT idproduct,mymodell,kw1,mymodellprice
FROM myproducts 
WHERE kw1 > <cfqueryparam value="#session.something1.something2.calculatedvalueC1#"/>
AND kw2 > <cfqueryparam value="#session.something1.something2.calculatedvalueC2#"/>
ORDER BY mymodellprice ASC

After retrieving these values, I can calculate the "machineruntime" of those machines (much simplified formula below). And if the calculated runtime is between one and three hours, I show the result. But to do so, I require the values from the database query:

<table class="table table-condensed"> <cfoutput query="qmodells"> <cfset machineruntime = "#2*(qmodells.kw1/(qmodells.kw2/konstantK3))^konstantK4/24#"> <cfif #TimeFormat(machineruntime, "HH:mm")# GT "01:00" AND #TimeFormat(machineruntime, "HH:mm")# LT "03:30"> <tr> <td nowrap="">#qmodells.mymodell#</td> <td nowrap="">#qmodells.kw1#</td> <td nowrap="">#qmodells.machineruntime#</td> </tr>
</cfif> </cfoutput> </table>

My goal is to show only the first matching machine that runs longer than one hour, possible also the next two results. I can obviously add CSS hidden to any row outside these results, but how would one properly do this?

Should I re-populate a query and then output that, or is there a clever way to iterate through the results and do this more elegantly? All my attemps of calculating within the first query just give me the same runtime for all machines, clearly I am doing it wrong.

I was thinking/trying something like this in the SELECT part of the query:

#2*(qmodells.kw1/(qmodells.kw2/konstantK3))^konstantK4/24# AS machineruntime

And also using the query as the base for a second query, like so:

<cfquery name="topmatch" dbtype="query">
SELECT *
FROM qmodells
WHERE machineruntime > 0.0416666666667

THANKS FOR ANY HELP IN THIS!

Answer 1

Your calc attempt which uses qmodells.kw2 above will grab the only first row of the query as it is not in a loop. Here's my guess at what I think you are trying to do.

First run your qModells query as above but let's add a row as a placeholder:

<Cfquery ...>
SELECT idproduct,mymodell,kw1,kw2,mymodellprice, 0.0 AS machineruntime
FROM myproducts 
WHERE kw1 > <cfqueryparam value="#session.something1.something2.calculatedvalueC1#"/>
AND kw2 > <cfqueryparam value="#session.something1.something2.calculatedvalueC2#"/>
ORDER BY mymodellprice ASC
</cfquery>

Note I added kw2 to the column list as well because I didn't see where that was coming from - but it seemed logically a part of the query (since k1 was in the column list).

Next loop through your query, calculate and set the machine run time.

<cfloop query="#qmodels#">

    <cfset mymachineruntime = 2*(kw1/(kw2/konstantK3))^konstantK4/24 />
    <cfset querysetcell(qmodels,'machineruntime',mymachineruntime,currentrow)/>
</cfloop>

Finally, using q of a q, select the record matching the values you are looking for:

<cfquery name="topmatch" dbtype="query">
SELECT *
FROM qmodells
WHERE machineruntime > 0.0416666666667
</cfquery>

I think that is close to what you are trying above. You just need a loop.

---

Note - this is possibly a good spot for a stored procedure or some clever database code in your query. The DB is probably capable of generating this value and returning it for you directly. Hope this helps!

---

EDIT NOTE: As Dan pointed out an empty string might cause problems for you when you try to select via Q of a Q (it might result in AlphaNum selection instead of just Num selection). So I have altered the query to use a number (0).

Answer 2

As Leigh said, could you do this directly in the query?

select model,modelprice,kw1,kw2,
  (2 * (kw1 / (kw2/#val(konstantK3)#))^#val(konstantK4)#/24) as MachineRunTime
  from MyProducts
 WHERE kw1 > <cfqueryparam value="#session.something1.something2.calculatedvalueC1#"/>
   AND kw2 > <cfqueryparam value="#session.something1.something2.calculatedvalueC2#"/>

I made a basic demonstration sqlfiddle with very random sample data.

If it's not important that you have the base query data AND the QoQ data, you can append this and nix the QoQ.

AND (2 * (kw1 / (kw2/#val(konstantK3)#))^#val(konstantK4)#/24) > 0.0416666666667

Because we're working with numbers as the variable input, besides the cfqueryparams OP already placed, I just used the #val()# function to convert any non-number to 0. OP, you could do some error-checking outside of the query and say <cfif val(konstantk3) gt 0 and val(konstantk4) eq 0>...execute the code....</cfif> as well.

Answer 3

I generally handle that situation like this:

<cfquery name="q1" dsn="somedatabase">
select field1, field2, 10.12314 MachineRunTime
etc
</cfquery>

Then loop through the query and calculate MachineRunTime for each row. You can now use query of query with an order by clause and maxrows attribute to get the stuff you want to display.