PageRenderTime 63ms CodeModel.GetById 15ms RepoModel.GetById 1ms app.codeStats 0ms

/wheels/model/calculations.cfm

http://cfwheels.googlecode.com/
ColdFusion | 222 lines | 209 code | 11 blank | 2 comment | 43 complexity | e4e33430eacdb96c57b8f817efbeac1a MD5 | raw file
Possible License(s): Apache-2.0, CPL-1.0
  1. <!--- PUBLIC MODEL CLASS METHODS --->
  2. <cffunction name="average" returntype="any" access="public" output="false" hint="Calculates the average value for a given property. Uses the SQL function `AVG`. If no records can be found to perform the calculation on you can use the `ifNull` argument to decide what should be returned."
  3. examples=
  4. '
  5. <!--- Get the average salary for all employees --->
  6. <cfset avgSalary = model("employee").average("salary")>
  7. <!--- Get the average salary for employees in a given department --->
  8. <cfset avgSalary = model("employee").average(property="salary", where="departmentId=##params.key##")>
  9. <!--- Make sure a numeric value is always returned if no records are calculated --->
  10. <cfset avgSalary = model("employee").average(property="salary", where="salary BETWEEN ##params.min## AND ##params.max##", ifNull=0>
  11. '
  12. categories="model-class,statistics" chapters="column-statistics" functions="count,maximum,minimum,sum">
  13. <cfargument name="property" type="string" required="true" hint="Name of the property to calculate the average for.">
  14. <cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
  15. <cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
  16. <cfargument name="distinct" type="boolean" required="false" hint="When `true`, `AVG` will be performed only on each unique instance of a value, regardless of how many times the value occurs.">
  17. <cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
  18. <cfargument name="ifNull" type="any" required="false" hint="The value returned if no records are found. Common usage is to set this to `0` to make sure a numeric value is always returned instead of a blank string.">
  19. <cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
  20. <cfscript>
  21. var loc = {};
  22. $args(name="average", args=arguments);
  23. if (ListFindNoCase("cf_sql_integer,cf_sql_bigint,cf_sql_smallint,cf_sql_tinyint", variables.wheels.class.properties[arguments.property].type))
  24. {
  25. // this is an integer column so we get all the values from the database and do the calculation in ColdFusion since we can't run a query to get the average value without type casting it
  26. loc.values = findAll(select=arguments.property, where=arguments.where, include=arguments.include, parameterize=arguments.parameterize, includeSoftDeletes=arguments.includeSoftDeletes);
  27. loc.values = ListToArray(Evaluate("ValueList(loc.values.#arguments.property#)"));
  28. loc.returnValue = arguments.ifNull;
  29. if (!ArrayIsEmpty(loc.values))
  30. {
  31. if (arguments.distinct)
  32. {
  33. loc.tempValues = {};
  34. loc.iEnd = ArrayLen(loc.values);
  35. for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
  36. StructInsert(loc.tempValues, loc.values[loc.i], loc.values[loc.i], true);
  37. loc.values = ListToArray(StructKeyList(loc.tempValues));
  38. }
  39. loc.returnValue = ArrayAvg(loc.values);
  40. }
  41. }
  42. else
  43. {
  44. // if the column's type is a float or similar we can run an AVG type query since it will always return a value of the same type as the column
  45. arguments.type = "AVG";
  46. loc.returnValue = $calculate(argumentCollection=arguments);
  47. // we convert the result to a string so that it is the same as what would happen if you calculate an average in ColdFusion code (like we do for integers in this function for example)
  48. loc.returnValue = JavaCast("string", loc.returnValue);
  49. }
  50. </cfscript>
  51. <cfreturn loc.returnValue>
  52. </cffunction>
  53. <cffunction name="count" returntype="numeric" access="public" output="false" hint="Returns the number of rows that match the arguments (or all rows if no arguments are passed in). Uses the SQL function `COUNT`. If no records can be found to perform the calculation on, `0` is returned."
  54. examples=
  55. '
  56. <!--- Count how many authors there are in the table --->
  57. <cfset authorCount = model("author").count()>
  58. <!--- Count how many authors that have a last name starting with an "A" --->
  59. <cfset authorOnACount = model("author").count(where="lastName LIKE ''A%''")>
  60. <!--- Count how many authors that have written books starting with an "A" --->
  61. <cfset authorWithBooksOnACount = model("author").count(include="books", where="booktitle LIKE ''A%''")>
  62. <!--- Count the number of comments on a specific post (a `hasMany` association from `post` to `comment` is required) --->
  63. <!--- The `commentCount` method will call `model("comment").count(where="postId=##post.id##")` internally --->
  64. <cfset aPost = model("post").findByKey(params.postId)>
  65. <cfset amount = aPost.commentCount()>
  66. '
  67. categories="model-class,statistics" chapters="column-statistics,associations" functions="average,hasMany,maximum,minimum,sum">
  68. <cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
  69. <cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
  70. <cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
  71. <cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
  72. <cfscript>
  73. var returnValue = "";
  74. $args(name="count", args=arguments);
  75. arguments.type = "COUNT";
  76. arguments.property = ListFirst(primaryKey());
  77. if (Len(arguments.include))
  78. arguments.distinct = true;
  79. else
  80. arguments.distinct = false;
  81. returnValue = $calculate(argumentCollection=arguments);
  82. if (IsNumeric(returnValue))
  83. return returnValue;
  84. else
  85. return 0;
  86. </cfscript>
  87. </cffunction>
  88. <cffunction name="maximum" returntype="any" access="public" output="false" hint="Calculates the maximum value for a given property. Uses the SQL function `MAX`. If no records can be found to perform the calculation on you can use the `ifNull` argument to decide what should be returned."
  89. examples=
  90. '
  91. <!--- Get the amount of the highest salary for all employees --->
  92. <cfset highestSalary = model("employee").maximum("salary")>
  93. <!--- Get the amount of the highest salary for employees in a given department --->
  94. <cfset highestSalary = model("employee").maximum(property="salary", where="departmentId=##params.key##")>
  95. <!--- Make sure a numeric value is always returned, even if no records are found to calculate the maximum for --->
  96. <cfset highestSalary = model("employee").maximum(property="salary", where="salary > ##params.minSalary##", ifNull=0)>
  97. '
  98. categories="model-class,statistics" chapters="column-statistics" functions="average,count,minimum,sum">
  99. <cfargument name="property" type="string" required="true" hint="Name of the property to get the highest value for (must be a property of a numeric data type).">
  100. <cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
  101. <cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
  102. <cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
  103. <cfargument name="ifNull" type="any" required="false" hint="See documentation for @average.">
  104. <cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
  105. <cfscript>
  106. $args(name="maximum", args=arguments);
  107. arguments.type = "MAX";
  108. </cfscript>
  109. <cfreturn $calculate(argumentCollection=arguments)>
  110. </cffunction>
  111. <cffunction name="minimum" returntype="any" access="public" output="false" hint="Calculates the minimum value for a given property. Uses the SQL function `MIN`. If no records can be found to perform the calculation on you can use the `ifNull` argument to decide what should be returned."
  112. examples=
  113. '
  114. <!--- Get the amount of the lowest salary for all employees --->
  115. <cfset lowestSalary = model("employee").minimum("salary")>
  116. <!--- Get the amount of the lowest salary for employees in a given department --->
  117. <cfset lowestSalary = model("employee").minimum(property="salary", where="departmentId=##params.id##")>
  118. <!--- Make sure a numeric amount is always returned, even when there were no records analyzed by the query --->
  119. <cfset lowestSalary = model("employee").minimum(property="salary", where="salary BETWEEN ##params.min## AND ##params.max##", ifNull=0)>
  120. '
  121. categories="model-class,statistics" chapters="column-statistics" functions="average,count,maximum,sum">
  122. <cfargument name="property" type="string" required="true" hint="Name of the property to get the lowest value for (must be a property of a numeric data type).">
  123. <cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
  124. <cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
  125. <cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
  126. <cfargument name="ifNull" type="any" required="false" hint="See documentation for @average.">
  127. <cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
  128. <cfscript>
  129. $args(name="minimum", args=arguments);
  130. arguments.type = "MIN";
  131. </cfscript>
  132. <cfreturn $calculate(argumentCollection=arguments)>
  133. </cffunction>
  134. <cffunction name="sum" returntype="any" access="public" output="false" hint="Calculates the sum of values for a given property. Uses the SQL function `SUM`. If no records can be found to perform the calculation on you can use the `ifNull` argument to decide what should be returned."
  135. examples=
  136. '
  137. <!--- Get the sum of all salaries --->
  138. <cfset allSalaries = model("employee").sum("salary")>
  139. <!--- Get the sum of all salaries for employees in a given country --->
  140. <cfset allAustralianSalaries = model("employee").sum(property="salary", include="country", where="countryname=''Australia''")>
  141. <!--- Make sure a numeric value is always returned, even if there are no records analyzed by the query --->
  142. <cfset salarySum = model("employee").sum(property="salary", where="salary BETWEEN ##params.min## AND ##params.max##", ifNull=0)>
  143. '
  144. categories="model-class,statistics" chapters="column-statistics" functions="average,count,maximum,minimum">
  145. <cfargument name="property" type="string" required="true" hint="Name of the property to get the sum for (must be a property of a numeric data type).">
  146. <cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
  147. <cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
  148. <cfargument name="distinct" type="boolean" required="false" hint="When `true`, `SUM` returns the sum of unique values only.">
  149. <cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
  150. <cfargument name="ifNull" type="any" required="false" hint="See documentation for @average.">
  151. <cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
  152. <cfscript>
  153. $args(name="sum", args=arguments);
  154. arguments.type = "SUM";
  155. </cfscript>
  156. <cfreturn $calculate(argumentCollection=arguments)>
  157. </cffunction>
  158. <!--- PRIVATE MODEL CLASS METHODS --->
  159. <cffunction name="$calculate" returntype="any" access="public" output="false" hint="Creates the query that needs to be run for all of the above methods.">
  160. <cfargument name="type" type="string" required="true">
  161. <cfargument name="property" type="string" required="true">
  162. <cfargument name="where" type="string" required="true">
  163. <cfargument name="include" type="string" required="true">
  164. <cfargument name="parameterize" type="any" required="true">
  165. <cfargument name="distinct" type="boolean" required="false" default="false">
  166. <cfargument name="ifNull" type="any" required="false" default="">
  167. <cfargument name="includeSoftDeletes" type="boolean" required="true">
  168. <cfscript>
  169. var loc = {};
  170. // start the select string with the type (`SUM`, `COUNT` etc)
  171. arguments.select = "#arguments.type#(";
  172. // add the DISTINCT keyword if necessary (generally used for `COUNT` operations when associated tables are joined in the query, means we'll only count the unique primary keys on the current model)
  173. if (arguments.distinct)
  174. arguments.select = arguments.select & "DISTINCT ";
  175. // create a list of columns for the `SELECT` clause either from regular properties on the model or calculated ones
  176. loc.properties = "";
  177. loc.iEnd = ListLen(arguments.property);
  178. for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
  179. {
  180. loc.iItem = Trim(ListGetAt(arguments.property, loc.i));
  181. if (ListFindNoCase(variables.wheels.class.propertyList, loc.iItem))
  182. loc.properties = ListAppend(loc.properties, tableName() & "." & variables.wheels.class.properties[loc.iItem].column);
  183. else if (ListFindNoCase(variables.wheels.class.calculatedPropertyList, loc.iItem))
  184. loc.properties = ListAppend(loc.properties, variables.wheels.class.calculatedProperties[loc.iItem].sql);
  185. }
  186. arguments.select = arguments.select & loc.properties;
  187. // alias the result with `AS`, this means that Wheels will not try and change the string (which is why we have to add the table name above since it won't be done automatically)
  188. arguments.select = arguments.select & ") AS wheelsqueryresult";
  189. // call `findAll` with `select`, `where`, `parameterize` and `include` but delete all other arguments
  190. StructDelete(arguments, "type");
  191. StructDelete(arguments, "property");
  192. StructDelete(arguments, "distinct");
  193. loc.returnValue = findAll(argumentCollection=arguments).wheelsqueryresult;
  194. if (!Len(loc.returnValue) && Len(arguments.ifNull))
  195. loc.returnValue = arguments.ifNull;
  196. </cfscript>
  197. <cfreturn loc.returnValue>
  198. </cffunction>