PageRenderTime 29ms CodeModel.GetById 24ms app.highlight 2ms 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
  1<!--- PUBLIC MODEL CLASS METHODS --->
  2
  3<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."
  4	examples=
  5	'
  6		<!--- Get the average salary for all employees --->
  7		<cfset avgSalary = model("employee").average("salary")>
  8		
  9		<!--- Get the average salary for employees in a given department --->
 10		<cfset avgSalary = model("employee").average(property="salary", where="departmentId=##params.key##")>
 11		
 12		<!--- Make sure a numeric value is always returned if no records are calculated --->
 13		<cfset avgSalary = model("employee").average(property="salary", where="salary BETWEEN ##params.min## AND ##params.max##", ifNull=0>
 14	'
 15	categories="model-class,statistics" chapters="column-statistics" functions="count,maximum,minimum,sum">
 16	<cfargument name="property" type="string" required="true" hint="Name of the property to calculate the average for.">
 17	<cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
 18	<cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
 19	<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.">
 20	<cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
 21	<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.">
 22	<cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
 23	<cfscript>
 24		var loc = {};
 25		$args(name="average", args=arguments);
 26		if (ListFindNoCase("cf_sql_integer,cf_sql_bigint,cf_sql_smallint,cf_sql_tinyint", variables.wheels.class.properties[arguments.property].type))
 27		{
 28			// 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
 29			loc.values = findAll(select=arguments.property, where=arguments.where, include=arguments.include, parameterize=arguments.parameterize, includeSoftDeletes=arguments.includeSoftDeletes);
 30			loc.values = ListToArray(Evaluate("ValueList(loc.values.#arguments.property#)"));
 31			loc.returnValue = arguments.ifNull;
 32			if (!ArrayIsEmpty(loc.values))
 33			{
 34				if (arguments.distinct)
 35				{
 36					loc.tempValues = {};
 37					loc.iEnd = ArrayLen(loc.values);
 38					for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
 39						StructInsert(loc.tempValues, loc.values[loc.i], loc.values[loc.i], true);
 40					loc.values = ListToArray(StructKeyList(loc.tempValues));
 41				}
 42				loc.returnValue = ArrayAvg(loc.values);
 43			} 
 44		}
 45		else
 46		{
 47			// 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
 48			arguments.type = "AVG";
 49			loc.returnValue = $calculate(argumentCollection=arguments);
 50			// 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)
 51			loc.returnValue = JavaCast("string", loc.returnValue);
 52		}
 53	</cfscript>
 54	<cfreturn loc.returnValue>
 55</cffunction>
 56
 57<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."
 58	examples=
 59	'
 60		<!--- Count how many authors there are in the table --->
 61		<cfset authorCount = model("author").count()>
 62
 63		<!--- Count how many authors that have a last name starting with an "A" --->
 64		<cfset authorOnACount = model("author").count(where="lastName LIKE ''A%''")>
 65
 66		<!--- Count how many authors that have written books starting with an "A" --->
 67		<cfset authorWithBooksOnACount = model("author").count(include="books", where="booktitle LIKE ''A%''")>
 68		
 69		<!--- Count the number of comments on a specific post (a `hasMany` association from `post` to `comment` is required) --->
 70		<!--- The `commentCount` method will call `model("comment").count(where="postId=##post.id##")` internally --->
 71		<cfset aPost = model("post").findByKey(params.postId)>
 72		<cfset amount = aPost.commentCount()>
 73	'
 74	categories="model-class,statistics" chapters="column-statistics,associations" functions="average,hasMany,maximum,minimum,sum">
 75	<cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
 76	<cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
 77	<cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
 78	<cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
 79	<cfscript>
 80		var returnValue = "";
 81		$args(name="count", args=arguments);
 82		arguments.type = "COUNT";
 83		arguments.property = ListFirst(primaryKey());
 84		if (Len(arguments.include))
 85			arguments.distinct = true;
 86		else
 87			arguments.distinct = false;
 88		returnValue = $calculate(argumentCollection=arguments);
 89		if (IsNumeric(returnValue))
 90			return returnValue;
 91		else
 92			return 0;
 93	</cfscript>
 94</cffunction>
 95
 96<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."
 97	examples=
 98	'
 99		<!--- Get the amount of the highest salary for all employees --->
100		<cfset highestSalary = model("employee").maximum("salary")>
101		
102		<!--- Get the amount of the highest salary for employees in a given department --->
103		<cfset highestSalary = model("employee").maximum(property="salary", where="departmentId=##params.key##")>
104		
105		<!--- Make sure a numeric value is always returned, even if no records are found to calculate the maximum for --->
106		<cfset highestSalary = model("employee").maximum(property="salary", where="salary > ##params.minSalary##", ifNull=0)>
107	'
108	categories="model-class,statistics" chapters="column-statistics" functions="average,count,minimum,sum">
109	<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).">
110	<cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
111	<cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
112	<cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
113	<cfargument name="ifNull" type="any" required="false" hint="See documentation for @average.">
114	<cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
115	<cfscript>
116		$args(name="maximum", args=arguments);
117		arguments.type = "MAX";
118	</cfscript>
119	<cfreturn $calculate(argumentCollection=arguments)>
120</cffunction>
121
122<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."
123	examples=
124	'
125		<!--- Get the amount of the lowest salary for all employees --->
126		<cfset lowestSalary = model("employee").minimum("salary")>
127		
128		<!--- Get the amount of the lowest salary for employees in a given department --->
129		<cfset lowestSalary = model("employee").minimum(property="salary", where="departmentId=##params.id##")>
130		
131		<!--- Make sure a numeric amount is always returned, even when there were no records analyzed by the query --->
132		<cfset lowestSalary = model("employee").minimum(property="salary", where="salary BETWEEN ##params.min## AND ##params.max##", ifNull=0)>
133	'
134	categories="model-class,statistics" chapters="column-statistics" functions="average,count,maximum,sum">
135	<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).">
136	<cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
137	<cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
138	<cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
139	<cfargument name="ifNull" type="any" required="false" hint="See documentation for @average.">
140	<cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
141	<cfscript>
142		$args(name="minimum", args=arguments);
143		arguments.type = "MIN";
144	</cfscript>
145	<cfreturn $calculate(argumentCollection=arguments)>
146</cffunction>
147
148<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."
149	examples=
150	'
151		<!--- Get the sum of all salaries --->
152		<cfset allSalaries = model("employee").sum("salary")>
153
154		<!--- Get the sum of all salaries for employees in a given country --->
155		<cfset allAustralianSalaries = model("employee").sum(property="salary", include="country", where="countryname=''Australia''")>
156		
157		<!--- Make sure a numeric value is always returned, even if there are no records analyzed by the query --->
158		<cfset salarySum = model("employee").sum(property="salary", where="salary BETWEEN ##params.min## AND ##params.max##", ifNull=0)>
159	'
160	categories="model-class,statistics" chapters="column-statistics" functions="average,count,maximum,minimum">
161	<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).">
162	<cfargument name="where" type="string" required="false" default="" hint="See documentation for @findAll.">
163	<cfargument name="include" type="string" required="false" default="" hint="See documentation for @findAll.">
164	<cfargument name="distinct" type="boolean" required="false" hint="When `true`, `SUM` returns the sum of unique values only.">
165	<cfargument name="parameterize" type="any" required="false" hint="See documentation for @findAll.">
166	<cfargument name="ifNull" type="any" required="false" hint="See documentation for @average.">
167	<cfargument name="includeSoftDeletes" type="boolean" required="false" default="false" hint="See documentation for @findAll.">
168	<cfscript>
169		$args(name="sum", args=arguments);
170		arguments.type = "SUM";
171	</cfscript>
172	<cfreturn $calculate(argumentCollection=arguments)>
173</cffunction>
174
175<!--- PRIVATE MODEL CLASS METHODS --->
176
177<cffunction name="$calculate" returntype="any" access="public" output="false" hint="Creates the query that needs to be run for all of the above methods.">
178	<cfargument name="type" type="string" required="true">
179	<cfargument name="property" type="string" required="true">
180	<cfargument name="where" type="string" required="true">
181	<cfargument name="include" type="string" required="true">
182	<cfargument name="parameterize" type="any" required="true">
183	<cfargument name="distinct" type="boolean" required="false" default="false">
184	<cfargument name="ifNull" type="any" required="false" default="">
185	<cfargument name="includeSoftDeletes" type="boolean" required="true">
186	<cfscript>
187		var loc = {};
188
189		// start the select string with the type (`SUM`, `COUNT` etc)
190		arguments.select = "#arguments.type#(";
191
192		// 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)
193		if (arguments.distinct)
194			arguments.select = arguments.select & "DISTINCT ";
195
196		// create a list of columns for the `SELECT` clause either from regular properties on the model or calculated ones
197		loc.properties = "";
198		loc.iEnd = ListLen(arguments.property);
199		for (loc.i=1; loc.i <= loc.iEnd; loc.i++)
200		{
201			loc.iItem = Trim(ListGetAt(arguments.property, loc.i));
202			if (ListFindNoCase(variables.wheels.class.propertyList, loc.iItem))
203				loc.properties = ListAppend(loc.properties, tableName() & "." & variables.wheels.class.properties[loc.iItem].column);
204			else if (ListFindNoCase(variables.wheels.class.calculatedPropertyList, loc.iItem))
205				loc.properties = ListAppend(loc.properties, variables.wheels.class.calculatedProperties[loc.iItem].sql);
206		}
207		arguments.select = arguments.select & loc.properties;
208
209		// 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)
210		arguments.select = arguments.select & ") AS wheelsqueryresult";
211
212		// call `findAll` with `select`, `where`, `parameterize` and `include` but delete all other arguments
213		StructDelete(arguments, "type");
214		StructDelete(arguments, "property");
215		StructDelete(arguments, "distinct");
216		
217		loc.returnValue = findAll(argumentCollection=arguments).wheelsqueryresult;
218		if (!Len(loc.returnValue) && Len(arguments.ifNull))
219			loc.returnValue = arguments.ifNull;
220	</cfscript>
221	<cfreturn loc.returnValue>
222</cffunction>