Understanding and Using CFCs (with OOP and database interaction)

Part 4

Searching our data

Now that we have our component successfully inserting our data properties into the database, we need a way to retrieve that information from the database.

The way we will do this, is by using the same data properties we already have defined for our search criteria.

For example, when we are ready to search our records, we will do the following steps:

1) Create a new instance of our job component

2) Set the properties of the component (such as the .name property) to the criteria. For example, if we want to search for jobs that have a name like "%my%", we will set the .name property to "%my%"

3) Execute a method that searches our data based on the properties.

4) Return an array with job component objects for each matching record based on our criteria

Creating our searching method (or function)

The first thing we will need to do is create a new <cffunction> tag set for our searching method.

I've added some general comments to outline steps that we want our component to do.

<cffunction name="searchJobs">
     <!--- argument to allow dynamic sorting --->
     <!--- query for our job records --->
     <!--- loop over each query record, creating a job component for each--->
     <!--- return our search results in array--->
</cffunction>

Functions can contain arguments

Since we will be performing a searching action, we should probably allow the person calling this function to specify which field to sort our results by. This is a perfect time to introduce the functionality of function arguments.

Functions within your CFC can contain arguments, just like regular native ColdFusion functions. For example, the function len("some string") takes one argument, the string you want to get the length of.

Our custom functions can contain arguments as well. In our CFC search function, we will allow an argument to specify the sort field: searchJobs("sort_field"). We define these arguments with the <cfargument> tag.

<cffunction name="searchJobs">
     <!--- argument to allow dynamic sorting --->
     <cfargument name="sortBy" default="display_name" required="no"
          hint="The column name you wish to sort by (defaults to name) ">
</cffunction>

The <cfargument> tag allows us to specify a default value for our argument. In this case, if no sort by argument is specified, then the argument will automatically be set to "display_name".

Using the this scope to specify our search criteria

Now we are ready to query our database and get job records. We are going search based on criteria that has been set in our data properties. These properties are in the this scope. Remember that each of these values is automatically created when the component is initialized with a blank, or null, value.

We will create a standard query selecting the fields we want to return. After the While clause, we will then test each data property to see if it contains a value. If it does, then we will use it as search criteria.

     <!--- query for our job records --->
     <cfquery datasource="#request.dsn#" name="qSearch">
          select job_id, display_name from jobs
          where 1 = 1
          <!--- if id is not empty, then specify it as search criteria --->
          <cfif len(trim(this.id))>
               and job_id = #this.id#
          </cfif>
          <!--- if name is not empty, then specify it as search criteria --->
          <cfif len(trim(this.name))>
               and display_name like '#this.name#'
          </cfif>
          orderby #sortBy#
     </cfquery>

Notice we have included our sorting argument in the last of the query. There is no scope qualifier such as "this" in front of the variable sortBy. This is because argument variables are created in the local scope when the function is executed. Your argument value will be available just as if it were created with a standard <cfset> tag. Therefore, we do not need to specify any scope.

Creating our array of results to return

Now we have queried for job records that match our criteria (if any was provided) and we are ready to turn each of our records into job component objects

To do this we create an array and store a job component object into an element for each record.

     <!--- initialize our array to hold our search results --->
     <cfset results = arrayNew(1)>
     <!--- loop over each query record --->
     <cfloop query="qSearch">
          <cfscript>
               // create a new job component for this record
               jobRecord = createObject("component", "job");
               // now set the properties of the job component we've
               // just created for this record
               jobRecord.name = qSearch.display_name;
               jobRecord.id = qSearch.job_id;
               // now set our array element to the component we just created
               // qSearch.currentRow = the current record number of the query we're on
               results[qSearch.currentRow] = jobRecord;
          </cfscript>
     </cfloop>

We'll go over each of the above lines in more detail to ensure you can see what is happening.

     <!--- initialize our array to hold our search results --->
     <cfset results = arrayNew(1)>

Here we are creating a new single dimensional. This will store our results.

     <cfloop query="qSearch">
          ...
     </cfloop>

Next we loop over each record in our query with a standard cfloop. This can also be done in a <cfscript> block of course, but I've used tags to make the syntax little easier to read for those not real familiar with <cfscript> syntax.

          <cfscript>
               // create a new job component for this record
               jobRecord = createObject("component", "job");
               ...
          </cfscript>

Here we are creating a new instance of our job component. This might seem a bit strange since we are currently executing a function within the same component, but this the most powerful part of component/object design!

Since we are creating a job component for each record, each job we create will have all of the internal methods and data properties of our job CFC!

          <cfscript>
               ...
               // now set the properties of the job component we've
               // just created for this record
               jobRecord.name = qSearch.display_name;
               jobRecord.id = qSearch.job_id;
               ...
          </cfscript>

Now we set the data properties of the component we just created with the record in the query. We first set the name property, followed by the id property.

          <cfscript>
               ...
               // now set our array element to the component we just created
               // qSearch.currentRow = the current record number of the query we're on
               results[qSearch.currentRow] = jobRecord;
          </cfscript>

Lastly, we set the current index of our array position to our component that we created after we've set its properties.

Creating our array of results to return

The final thing we need to do in our searchJobs() function is to return our results back to the place that called this function.

We do this with the <cfreturn> tag. The <cfreturn> tag is used only in custom functions for returning values to the process that called the function. We do this with the syntax of:

<cfreturn [value to return]>

In this case, we want to return our array of job components, so our line will look like:

     <!--- return our search results --->
     <cfreturn results>

Our finished function

Below is the finished result of the custom function (or method) we have just created.

<cffunction name="searchJobs">
     <!--- argument to allow dynamic sorting --->
     <cfargument name="sortBy" default="display_name" required="no"
          hint="The column name you wish to sort by">
     <!--- query for our job records --->
     <cfquery datasource="#request.dsn#" name="qSearch">
          select job_id, display_name from jobs
          where 1 = 1
          <!--- if id is not empty, then specify it as search criteria --->
          <cfif len(trim(this.id))>
               and job_id = #this.id#
          </cfif>
          <!--- if name is not empty, then specify it as search criteria --->
          <cfif len(trim(this.name))>
               and display_name like '#this.name#'
          </cfif>
          orderby #sortBy#
     </cfquery>
     <!--- initialize our array to hold our search results --->
     <cfset results = arrayNew(1)>
     <!--- loop over each query record --->
     <cfloop query="qSearch">
          <cfscript>
               // create a new job component for this record
               jobRecord = createObject("component", "job");
               // now set the properties of the job component we've
               // just created for this record
               jobRecord.name = qSearch.display_name;
               jobRecord.id = qSearch.job_id;
               // now set our array element to the component we just created
               // qSearch.currentRow = the current record number of the query we're on
               results[qSearch.currentRow] = jobRecord;
          </cfscript>
     </cfloop>
     <!--- return our search results --->
     <cfreturn results>
</cffunction>

In the part 5, we will test our searchJobs() function by putting it to use on a page that lists all of the current jobs in our database.