Where to put queries (especially long ones) in a CFWheels project

Question

(wheels novice here)

I'm trying to figure out the best place to put a number of large and/or complex queries that are used in the controllers function for the view.

I have a view calendar.cfm, and a controller with the 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 almost 700 lines long, and I've only implemented one of the 5 or 6 views in the project -- which will require additional queries.

I have the sense that I'm doing this wrong. I tried putting all the query functions in a cfc file in the models folder

<cfcomponent extends="Model">
    functions...

but I can't figure out how to call them from the calendar function in the controller. I tried, for instance,

<cfset dateList= model("model_file_name").getDateList()>

but that isn't it.

Where should I put the queries and how should I call them?

---

EDIT

When I try to call the function in the model, I get "The data source could not be reached." However, I explicitly specify the datasource in the query. For example I have the following function in both the 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 datasource error. However if I call it from the model with <cfset dateList = model("wheels_girs_model").getDateList()/>, I get the error. Because I am using more than one datasource, I'm not setting the datasources in the config file. I have set them both up in the admin panel and refer to them directly in the cfqeury. What would prevent the model from finding the datasource?

Answer 1

There are different ways to do this:

First of all, 700 lines per controller is not too long.

You can put all queries in the controller it self. You can create different functions for different queries (i guess you are doing this).

If you still think this is too long. You can group functions in different cfc's. Then you can create object of these cfc's using createObject to access methods or you can invoke cfc using cfinvoke to access your function.
Secondly, You can put your queries in the model file. You need to create a function in the your model cfc, then add your query in this function and you need to return the query or particular value, whatever you want using cfreturn.
You can call that function using the format

<cfset yourVariableName = model("yourModelName").yourCustomFunctionName(arguments if required) />

After adding a new function to your cfc, you need to reload application using your_site_url/?reload=true&password=password_you_set
Third, as Dan Bracuk suggested, you can use stored procedures and call stored procedures using cfstoredProc

Edit Answer to the problem i understood from comment
You should have put error in the question itself as this changes everything. This error has nothing to do with your code. This error means you haven't set the correct datasource or you haven't created datasource but you are using it or it means there is something wrong in your datasource configuration.
Do as the following:

1. Check whether you have defined correct datasource in the config/settings.cfm.
2. If not, please define it. You can define the datasource by <cfset set(dataSourceName="your_desired_datasource_name.") />
3. If yes, then go to your cfadministrator control and datasource tab. Check whether that datasource exists or not.
4. If not, create datasource. If yes, check whether it is correctly configured or not, also check for spelling differnece between the datasource name from cfadministrator and the one defined in the config/settings.cfm.

Your problem should go if you do one of the above. Remember, whatever you do, you have to reload application using yourappurl/?reload=1&password=password_you_set

Second Edit:
Your question still doesn't give us clear info like How do you set datasource in admin panel and then how do you access in the query? This should have been different question as this question is totally different from the original question. Anyway, You should always use default datasource (the one defined in the config/settings.cfm). You can override this datasource in the model cfc. You can override default datasource for particular model by <cfset dataSource("your datasource name for that particular table")>. Please check the link for more info. And if you have more queries, you should as separate question (you will probably get more responses).