Where to place requests (especially long ones) in the CFWheels project
(newbie here)
I am trying to find a better place to put some large and / or complex queries that are used in a controllers function for a view.
I have a view calendar.cfm
and controller with a function:
<cffunction name="calendar">
<cfset formData = getValidatedFormData()>
<cfset fromDate = formData.fromDate>
<cfset toDate = formData.toDate>
<cfset dbLocation = formData.dbLocation>
<cfset dateList = getDateList()>
<cfset selectLists = getSelectLists()>
<cfset companySelectList = selectLists.companySelectList>
<cfset activitySelectList = selectLists.activitySelectList>
<cfset instructorSelectList = selectLists.instructorSelectList>
<cfset statusSelectList = selectLists.statusSelectList>
<cfset courseNumberLists = getCourseNumberLists()>
<cfset girsCourseNumberList = courseNumberLists.girsCourseNumberList>
<cfset activityCourseNumberList = courseNumberLists.activityCourseNumberList>
<cfset activityCourseNumberList = courseNumberLists>
<cfset ELMActivityList = getELMActivityList()>
<cfset activityList = getActivityList()>
<cfif len(elmActivityList.LM_ACT_CD) gt 0>
<cfset elmActIDList = Replace(QuotedValueList(ELMActivityList.LM_ACT_CD, ","), "'',", "", "all")>
<cfelse>
<cfset elmActIDList = "'String1','String2'">
</cfif>
<cfif len(activityList.girs_act_cd) gt 0>
<cfset girsActIDList = Replace(QuotedValueList(activityList.GIRS_ACT_CD, ","), "'',", "", "all")>
<cfelse>
<cfset girsActIDList = "'String1','String2'">
</cfif>
<cfset combinedList = getCombinedList( elmActIDLIst , girsActIDList )>
<cfset needUnion = false>
<cfset programList = getProgramList()>
</cffunction>
Each of the functions, such as getDateList()
, contains long and / or complex queries. The controller is already about 700 lines long and I only performed one of 5 or 6 views in the project, which would require additional queries.
I have a sense that I am doing it wrong. I tried to put all the request functions in a cfc file in the samples folder
<cfcomponent extends="Model">
functions...
but I can't figure out how to call them from a function calendar
in the controller. I have tried for example
<cfset dateList= model("model_file_name").getDateList()>
but it is not.
Where should I put requests and what should I call them?
EDIT
When I try to call a function in the model, I get: "Data source cannot be reached". However, I am explicitly specifying the data source in the request. For example, I have the following function in both model and controller:
<cffunction name="getDateList" returntype="any">
<cfquery name="dateList" dataSource="ELM_Prod">
SELECT DATES FROM
(SELECT dateadd(dd,DAYS, <cfqueryparam cfsqltype="cf_sql_date" value="#fromDate#">) DATES
FROM
(SELECT TOP 365 colorder - 1 AS DAYS from master..syscolumns
WHERE id = -519536829 order by colorder) a
WHERE datediff(
dd,
dateadd(
dd, DAYS, <cfqueryparam cfsqltype="cf_sql_date" value="#fromDate#">
),
<cfqueryparam cfsqltype="cf_sql_date" value="#toDate#">
) >= 0
AND dateadd(
dd,
DAYS,
<cfqueryparam cfsqltype="cf_sql_date" value="#fromDate#">
) <= <cfqueryparam cfsqltype="cf_sql_date" value="#toDate#">
) a order by DATES
</cfquery>
<cfreturn dateList>
</cffunction>
When I call the function in the controller with <cfset dateList = getDateList()>
, everything is fine and I don't get the data source error. However, if I call this from the model with <cfset dateList = model("wheels_girs_model").getDateList()/>
, I get an error. Since I am using more than one data source, I do not set the data sources in the config file. I have installed both of them in the admin panel and link to them directly in cfqeury. What would prevent the model from finding the data source?
source to share
There are different ways to do this:
First of all, 700 lines per controller are not too long.
You can put all requests in the controller yourself. You can create different functions for different requests (I think you do).
If you still think this is too long. You can group functions in different cfc. Then you can create a cfc object with createObject
to access the methods or you can call cfc with cfinvoke
to access your function.
Second, you can put your queries in a model file. You need to create a function in your cfc model , and then add a request to that function, and you need to return a request or value, no matter what you want to use cfreturn .
You can call this function using the format
<cfset yourVariableName = model("yourModelName").yourCustomFunctionName(arguments if required) />
After adding a new function to your cfc, you need to reload your application using your_site_url/?reload=true&password=password_you_set
Third, as Dan Bracus suggested, you can use stored procedures and call stored procedures usingcfstoredProc
Edit Answer to the problem I understood from the comment
You should have put a mistake in the question itself, as that changes everything. This error has nothing to do with your code. This error means that you have not installed the correct data source or created a data source but are using one, or it means that there is something implicit in your data source configuration.
Follow these steps:
- Check if you have defined the data source correctly in config /settings.cfm.
- If not, define it. You can define the data source
<cfset set(dataSourceName="your_desired_datasource_name.") />
- If yes, go to cfadministrator control and datasource tab. Check if this data source exists.
- If not, create a data source. If yes, check if it is configured correctly or not, also check the differnece spelling between the data source name from cfadministrator and the value defined in config /settings.cfm.
Your problem should go if you do one of the above. Remember, whatever you do, you need to reload your application usingyourappurl/?reload=1&password=password_you_set
Second edit :
Your question still doesn't give us clear information about how you set up the datasource in the admin panel and then how do you access the query? This should have been a different question as this question is completely different from the original question. In any case, you should always use the default data source (the one defined in config / settings.cfm). You can override this datasource in cfc model. You can override the original data source for a specific model with <cfset dataSource("your datasource name for that particular table")>
. For more information see the link. And if you have more queries, you should as a separate question (you will probably get more answers).
source to share