Populating a Related Select Using jQuery in ColdFusion

In this quick tip we will use jQuery to populate a related select html form element in ColdFusion. The first select element will show a list of states, and the second will show a list of cities based on the selected state.

We will assume that you have a database with tables that holds the state and city information, that the files contacts.cfc and addClients.cfm are saved in the same file folder, and request.dsn is defined in Application.cfc.

Here’s what we need to do:

  • Create queries to retrieve list of states and cities
  • Create a simple form with two related select
  • Use jQuery to populate the related select

1. Create queries to retrieve list of states and cities.

Let’s create a ColdFusion component and name it contacts.cfc. In this component, add the following code fragments:


<cfcomponent>
   <cffunction name="getStates" returntype="query" hint="Get list of states">
      <cfscript>var qGet = "";</cfscript>
      <cfquery name="qGet" datasource="#request.dsn#">
         SELECT stateID, stateName
         FROM states
      </cfquery>
   </cffunction>

   <cffunction name="getCities" output="false" returntype="array" hint="Get cities for a given state">
   <cfargument name="stateID" type="string" required="yes" default="0">
      <cfscript>
         var qGet = "";
         var aCities = [];
      </cfscript>

      <cfquery name="qGet" datasource="#request.dsn#">
         SELECT c.cityID, c.cityName
         FROM cities c
         INNER JOIN states s ON s.stateID = c.FK_stateID
         WHERE c.FK_stateID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.stateID#">
      </cfquery>

      <cfscript>
         //convert query to array
         var cols = qGet.columnList;
         var colsLen = listLen(cols);
         for(var i=1; i<=qGet.recordCount; i++) {
            var row = {};
            for(var k=1; k<=colsLen; k++) {
               row[lcase(listGetAt(cols, k))] = qGet[listGetAt(cols, k)][i];
            } //for k
            arrayAppend(aCities, row);
         } //for i

         return aCities;
      </cfscript>
   </cffunction>
</cfcomponent>

2. Create a simple form with two related select

Let’s a create form to add a client, and name it addClient.cfm. In this file, add the following code fragments:

<!DOCTYPE html>
<html>
<head>
   <meta charset="UTF-8">
   <title>Add Client</title>
   <link href="/path/to/jquery.min.css" rel="stylesheet" type="text/css" />
   <link href="/path/to/bootstrap.min.css" rel="stylesheet" type="text/css"/ >
</head>
<body>
   <script src="/path/to/jquery.min.js"></script>
   <script src="/path/to/bootstrap.min.js"></script>
   <cfscript>
      //retrieve list of states from db
      oContact = createobject("component","contacts");
      states= oContact.getStates();
   </cfscript>

   <cfoutput>
      <form name="addClient" class="form-horizontal" method="post" action="">
         <div class="form-group required">
            <label for="state" class="col-md-4 control-label">State &nbsp;</label>
            <div class="col-md-7">
               <select name="state" id="state" class="form-control">
                  <option value="">&nbsp;</option>
                  <cfloop query="states">
                  <option value="#states.stateID#">#states.stateName#</option>
                  </cfloop>
               </select>
            </div>
         </div>
         <div class="form-group required">
            <label for="city" class="col-md-4 control-label">City &nbsp;</label>
            <div class="col-md-7">
               <!--- the 'city' select will be populated by jQuery when a state is selected --->
               <select name="city" id="city" class="form-control">
                  <option value="">&nbsp;</option>
               </select>
            </div>
         </div>
      </form>
   </cfoutput>

3. Use jQuery to populate the related select

In addClient.cfm, just after the closing form tag, add the following code fragment:


<script>
$(document).ready(function() {
   $('#state').on('change', function(){ 
      var $selStateID = $(this).val(); 

      $.ajax({ 
         method: 'post', 
         dataType: 'json', 
         url: 'contacts.cfc', 
         data: { 
            method: 'getCities', 
            returnFormat: 'json', 
            stateID: $selStateID 
         }, 
         success: 
            function(results,status,xhr){ 
               var $citySelect = $('#city'); 
               $citySelect.find('option:not(:first)').remove(); 
               if (results.length){ 
                  $.each (results, function(i){ 
                     $citySelect.append('<option value="' + results[i].cityid + '">' + results[i].cityname + '</option>'); 
                  }); //each 
               } //if 
          } //success 
       }); //$.ajax 
     }); // state.onchange 
   });//document 
   </script> 
</body> 
</html>

Important notes:

For the above jQuery ajax retrieval to work, please ensure the following:

  • dataType must be json,
  • returnFormat must be json, and
  • query to retrieve list of cities must be converted to an array (do not return a query)

The stateID is passed to the function to retrieve all cities with the selected state. The jQuery ajax post happens when the user selects a state from the first select drop down list. $selStateID represent the ID of the selected state.

When a list of cities is returned from the jQuery ajax post, we then populate the cities select drop down list with this return set of values. cityid and cityname are fields in the cities table.

 

Using jQuery UI Autocomplete with hidden ID in ColdBox

Previously in How to use jQuery UI Autocomplete with ColdBox and Bootstrap the value used was singular and also the record identifier. However what if we want to submit the identifier but show another text value for the auto-suggest to the user, for example, if we want to show the client’s full name for auto suggest, but submit the client’s ID.

The environment used for this example is:

  • ColdBox 3.8
  • BootStrap 3.1.1
  • ColdFusion 10

The steps are essentially the same  (the differences are in the helper function and front end UI), i.e.

  • Reference jQuery UI in the main layout
  • Create a helper function to retrieve the required data
  • Use autocomplete on the view page

1 Reference the jQuery UI in the main layout

Open layouts\Main.cfm and add the following reference to the style sheet and JavaScript for jQuery UI (if not already present), i.e.

<head>
  <link href="/path/to/bootstrap/bootstrap.min.css" rel="stylesheet" type="text/css"/ >
  <link href="/path/to/jquery-ui/themes/smoothness/jquery-ui.min.css" rel="stylesheet" type="text/css" />
</head>
<body>
  <script src="/path/to/jquery/jquery.11.1.js"></script>
  <script src="/path/to/jquery/ui/jquery-ui.min.js"></script>
  <script src="/path/to/bootstrap/bootstrap.min.js"></script>
</body>

2 Create a helper function to retrieve the required data

Load includes\helpers\ApplicationHelper.cfm and add the following function:

<cffunction name="getClients" access="remote" output="no">
  <cfscript>
    var qGet = "";
    var clientsList = [];
    var stClients = "";
  </cfscript>
  <cfquery name="qGet" datasource="clients">
    SELECT
      clientid, title, firstname, lastname
    FROM clients
  </cfquery>
  <cfscript>
    //convert query to array
    for (var row in qGet) {
      stClients.label = row.title & ' ' & row.firstname & ' ' & row.lastname;
      stClients.value = row.clientid;
    }
    ArrayAppend(clientsList, stClients);
    return clientsList;
  </cfscript>
</cffunction>

3 Use jQuery UI Autocomplete on the view page

In the view page, for example, the edit.cfm where autocomplete is needed, add the following code:

//somewhere in the form section, add this:
<input type="text" name="clientname" id="clientname" class="form-control client-suggest" value="">
<input type="hidden" name="clientid" id="clientid" class="form-control" value="">

<script>
<cfoutput>
  //retrieve list of clients from db, getClients is a function in the application helper
  var #toScript(getClients(), "clientsList")#;
</cfoutput>

$(document).ready(function() {
   var suggestInput = function (sourceValues, elemClassName){
      $('input.' + elemClassName).each(function(){
         var formElemName = $(this).attr('name');
         var hiddenElemID = formElemName + 'id';

         //on auto suggest
         $(this).autocomplete({
            source: sourceValues,
            select: function(event, ui) {
               selectedObj = ui.item; 
               $(this).val(selectedObj.label);
               $('#'+ hiddenElemID).val(selectedObj.value); 
               return false; 
            },
            change: function(event,ui) {
               var formElemVal = $(this).val();
               if (formElemVal === ''){
                  //clear id from hidden field if input is blank
                  $('#'+ hiddenElemID).val('');
               } //if
            }//change
         }); //autocomplete

         //clear id from hidden field if input is blank
         $('#' + formElemName).blur(function(){
            if ( $('#' + formElemName).val() == ''){
               $('#' + hiddenElemID).val(''); 
            }
         });
      } //each
   } //suggestInput

   if ($('.client-suggest').length){
      //activate auto suggest for client input box 
      //(i.e. the input with class of 'client-suggest')
      suggestInput(clientsList,'client-suggest');
}); //document.ready
</script>

Here we use the ColdFusion toScript function to convert ColdFusion value to JavaScript saving it as clientsList. getClients is the ColdBox helper function which we added to ApplicationHelper.cfm.

The client name input box will sugget the client’s full name (including title), but upon form submission, the client’s ID will be submitted.

Quick Tip on using ColdFusion cfquery in cfscript

We’re all familiar with using cfquery to query databases in ColdFusion. But what is its equivalent in cfscript? Here are a few query examples to illustrate. For each example we will show the cfquery and its equivalent in cfscript. For these examples we will be performing the CRUD operations on the clients database.

1. Create (i.e. INSERT into the database)

Let’s start with the cfquery tag version:

<cfquery name="qInsert" datasource="clientsDS">
  INSERT INTO clients
  (
    title,
    firstname,
    lastname,
    email,
    phone
  )
  VALUES
  (
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.title#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  )
</cfquery>

Here’s the cfscript equivalent:

<cfscript>
  qService = new query();
  qService.setDatasource("clientsDS");
  qService.setName("qInsert");
  qService.addParam(name="title", value="#trim(form.title)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="firstname", value="#trim(form.firstname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="lastname", value="#trim(form.lastname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar");
  qService.setSql("
    INSERT INTO clients
    (title, firstname, lastname, email, phone)
    VALUES
    (:title, :firstname, :lastname, :email, :phone)
  ");
   qService.execute();
</cfscript>

2 Read (i.e. SELECT from the database)

Let’s start with the cfquery tag version:

<cfquery name="qGet" datasource="clientsDS">
  SELECT 
    title, firstname, lastname, email, phone
  FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

  qService = new query(); 
  qService.setDatasource("clientsDS"); 
  qService.setName("qGet"); 
  qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
  qService.setSql("
   SELECT title, firstname, lastname, email, phone
   FROM clients
   WHERE clientID = :clientID
  ");
  results = qService.execute();

3 Update (i.e. Update the database)

Let’s start with the cfquery tag version:

<cfquery name="qUpdate" datasource="clientsDS">
  UPDATE clients
  SET
    email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    phone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qUpdate");
 qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
 qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar")
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
  UPDATE clients
  SET
   email = :email, 
   phone = :phone
  WHERE clientID = :clientID
 ");
 qService.execute();

4 Delete (i.e. DELETE from the database)

Let’s start with the cfquery tag version:

<cfquery name="qDelete" datasource="clients">
  DELETE FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qDelete");
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
   DELETE FROM clients
   WHERE clientID = :clientID
 ");
 qService.execute();

How to Show Required Icon for Mandatory Fields

Here’s a quick tip on how to show the required icon for mandatory fields when using FormValidationJS for Bootstrap forms.

In your application’s style sheet ensure you add the following:


/* Adjust feedback icon position */
#<your-form-id> .has-feedback .form-control-feedback {
   top: 0;
   right: -15px;
}
/* for required select element, a padding-right of 42.5px is added; we want to over-ride this value */
   .has-feedback .form-control {
   padding-right: 12px;
}
.required .control-label:after {
   color: #d00;
   content: "*";
   position: absolute;
   margin-left: 1px;
   top:6px;
   font-family: 'Glyphicons Halflings';
   font-weight: normal;
   font-size: 10px;
}

Then in the page where you have a form that needs client side validation using FormValidation, ensure you have the following:


$('#<your-form-id>').formValidation({
   framework: 'bootstrap',
   icon: {
      valid: 'glyphicon glyphicon-ok',
      invalid: 'glyphicon glyphicon-remove',
      validating: 'glyphicon glyphicon-refresh'
   },
   fields: {
      firstname: {
         validators: {
            notEmpty: {
               message: 'First Name is required and cannot be empty'
            }
         }
      },//firstname
      lastname: {
         validators: {
            notEmpty: {
               message: 'Last Name is required and cannot be empty'
            }
         }
      }//lastname
   }//fields
});

In the actual form, ensure you have something similar to this, in particular the class=”form-group required” part:


<form id="<your-form-id>" class="form-horizontal" role="form" method="post" action="">
   <div class="form-group required">
      <label for="firstname" class="col-md-3 control-label">First Name:</label>
      <div class="col-md-3">
         <input type="text" name="firstname" class="form-control" placeholder="First Name">
      </div>
   </div>
   <div class="form-group required">
      <label for="lastname" class="col-md-3 control-label">Last Name:</label>
      <div class="col-md-3">
         <input type="text" name="lastname" class="form-control" placeholder="LastName">
      </div>
   </div>
</form>

Lastly, don’t forget to link to the style sheet (including the application’s styles) and scripts for FormValidation JS and jQuery in the appropriate places (in the head section for styles, and body for scripts):


<html>
<head>
<title>Page title</title>
<link href="<path-to-formvalidation>/formValidation.min.css" rel="stylesheet" />
<link href="<path-to-application-stylesheet>/your-app-name.css" rel="stylesheet" />
</head>
<body>
   <script src="<path-to-jquery>/jquery.min.js"></script>
   <script src="<path-to-formvalidation>/formValidation.min.js"></script>
   <script src="<path-to-formvalidation>/framework/bootstrap.min.js"></script>
</body>
</html>

 

ColdBox MailService – Passing Complex Values to the Body

In a previous blog post on using the ColdBox MailService we made use of the service’s SetBodyTokens method to pass through simple values, that is something like this:


/* define the token for use in the email view.
this is the dynamic content for the html email body */

email.setBodyTokens({
  title = emailTitle,
  firstName = arguments.user.getFirstName(),
  url = baseURL & "/reports/list"
});

Then in the view used to display the email body content, any placeholder variables will be replaced with the passed in value. That is @title@ will be replace with the value passed in. So if a value of Cool Programmer was passed in, then @title@ will now show Cool Programmer.

Using the SetBodyTokens method means that we can not passed in complex values like an array or a struct. To overcome this issue we will instead use the MailService’s SetBody method to pass in the value via the args parameter to the email body’s view template.

Firstly, in the handler where the MailService is invoke and where the sendUserDetails function is defined, add the following dependency injections at the top of the file:


property name="mailService" inject="coldbox:plugin:MailService";
property name="renderer" inject="coldbox:plugin:Renderer";
property name="configBean" inject="coldbox:configBean";

Then add the following function to gather the information and send the email in this same file, as follows:


public void function sendUserDetails(event){ 
 var email = MailService.newMail().config(
   from=configBean.getKey('emails').senderEmail,
   to=configBean.getKey('emails').recipientEmail,
   subject= "User Details"
 );

 var stInfo = {};
 stInfo.title = "Cool Programmer";
 stInfo.firstname = "John";
 stInfo.lastname = "Doe";
 stInfo.email = "John.Doe@supercool.com";

 // generate html content for email from template
 email.setBody( renderer.renderView( view='userDetails', args=stInfo ));
 email.setType("HTML");

 // Send the email. MailResult is a boolean.
 mailResult = mailService.send(email);

} //sendUserDetails

In the view for the email body (i.e.  view=”userDetails”), the passed in values are in the args scope, so to access them we refer to each variables as args.variable-name (i.e. replace stInfo with argsargs.firstname). Here’s a partial code snippet to illustrate:


<p>
#args.firstname# #args.lastname#<br>
#args.title#<br>
#args.email#
</p>

A Subquery Using ColdBox’s Detached Criteria Builder

Let’s say we want to create the following SQL query with a subquery using ColdBox’s Detached Criteria Builder:


select *
from reports r
where r.report_id not in (
   select report_id
   from confirmations c
   inner join staffs s on s.staff_id = c.staff_id
   inner join teams t on t.team_id = s.team_id
   where t.teamName = 'Developers'
)

In our objects world we have the following corresponding entities:

  • report
  • confirmation
  • staff
  • team

And the following relationships between them:

  • A report can have one to many confirmations submitted by staff
  • A confirmation belongs to a staff
  • A staff belongs to a team

Then our ORM query for the above SQL query using ColdBox’s Detached Criteria Builder would be as follows:


cr = reportService.newCriteria();
cr.add(
   cr.createSubcriteria('report','subrpt')
   .withProjections(property="report_id")
   .createAlias('subrpt.confirmations','subconf')
   .createAlias('subconf.staffs','substaffs')
   .createAlias('substaffs.teams','subteams')
   .isEq('subteams.teamName','Developers')
   .propertyNotIn("report_id")
);

Notes

  • confirmations is the relationship between the report and confirmation entities
  • staffs is the relationship between the confirmation and staff entities
  • teams is the relationship between the staff and team entities

Further Learning

For more information on ColdBox Criteria Builder and Detached Criteria Builder, please visit these links:

Quick Tip on Using the DataTables Ultimate Date/Time Sorting Plugin

If the date/time column data does not sort correctly using DataTables, then you need to use the ultimate date/time sorting plugin. Here are the steps:

  • Include the moment.js JavaScript library
  • Include the DataTables date/time sorting plugin
  • Define the date/time format to detect

In the page head section, add the following code snippet:


<!doctype html>
<html>
<head>
  <title>DataTables Date/Time Sorting Plugin</title>
  <script src="//cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
  <script src="//cdn.datatables.net/plug-ins/1.10.12/sorting/datetime-moment.js"></script>
</head>
<body>

  //code snippet to define and sort date/time columns goes here

</body>
</html>

Register the date/time format to detect and order using the $.fn.dataTable.moment( format, locale ) method, which accepts up to two parameters:

  • format – The date / time format to detect and order, and is required.
  • locale (optional) – This option can be used to specify a locale.

For format and locale available for use, please refer to the following links below:

Here’s a code snippet example to illustrate:


<!doctype html>
<html>
<head>
  <title>DataTables Date/Time Sorting Plugin</title>
  <script src="//cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
  <script src="//cdn.datatables.net/plug-ins/1.10.12/sorting/datetime-moment.js"></script>
</head>
<body>

<script>
  $(document).ready(function() {
    $.fn.dataTable.moment( 'DD/MM/YYYY' );
    $.fn.dataTable.moment( 'dddd, DD/MM/YYYY' );
    $('#myexample').DataTable();
  } );
</script>

</body>
</html>