Home > front end >  Coldfusion/Lucee multiple conditional select via AJAX
Coldfusion/Lucee multiple conditional select via AJAX

Time:12-10

I'm currently teaching (re-teaching) myself JavaScript and jQuery in order to rewrite a legacy application that runs on Lucee.

Part of the rewrite requires several additions to the basic functionality. One specific addition is adding a "Street Name" select field to an existing "County Name" -> "City Name" select fields.

I found this StackOverflow question: Coldfusion conditional select option does not work in IE9, Chrome and Firefox

Using the basics of that question; I wrote the following:

<!--- county.cfm --->
<cfif structKeyExists(url,'work_area_county_id')>
    <cfset loadState() />
</cfif>

<cfif structKeyExists(url,'work_area_city_id')>
    <cfset loadStreet() />
</cfif>

<cfset loadCounty() />

<cffunction name="loadCounty">
    <cfquery name="qCounty">
        SELECT work_area_county_id, work_area_county_name FROM work_area_county
    </cfquery>
</cffunction>   

<cffunction name="loadState">
    <cfset variables.work_area_county_id = url.work_area_county_id />
    <cfquery name="qCity">
        SELECT work_area_city_id, work_area_city_name, work_area_county_id FROM work_area_city WHERE work_area_county_id = <cfqueryparam value="#variables.work_area_county_id#" cfsqltype="cf_sql_int">
    </cfquery>
    <cfoutput>
        <select name="state" >
            <option value="">Select City</option>
            <cfloop query="qCity">
                <option value="#work_area_city_id#">#work_area_city_name#</option>
            </cfloop>
        </select>
    </cfoutput>
</cffunction>

<cffunction name="loadStreet">
    <cfset variables.work_area_city_id = url.work_area_city_id />
    <cfquery name="qStreet">
        SELECT work_area_street_id, work_area_street_name, work_area_city_id FROM work_area_street WHERE work_area_city_id = <cfqueryparam value="#variables.work_area_city_id#" cfsqltype="cf_sql_int">
    </cfquery>
    <cfoutput>
        <select name="state" >
            <option value="">Select Street</option>
            <cfloop query="qStreet">
                <option value="#work_area_street_id#">#work_area_street_name#</option>
            </cfloop>
        </select>
    </cfoutput>
</cffunction>
<!--- display.cfm --->
<cfinclude template="includes/county.cfm">

<cfoutput>
    <form name="state_populate">
        <select name="county" id="county">
            <option value="0">Select</option>
            <cfloop query="qCounty">
                <option value="#work_area_county_id#">
                    #work_area_county_name#
                </option>
            </cfloop>
        </select>
        <div id="city">
            <select name="city" >
                <option value="">Select</option>
             </select>
        </div>
        <div id="street">
            <select name="street" >
                <option value="">Select</option>
             </select>
        </div>
    </form>
</cfoutput>
// The JS

$('#county').change(function() {
    var value = $('#county').val();
    $.ajax({
        type: "get",
        url:'includes/county.cfm?work_area_county_id=' value,
        success: function(response) {
            $('#city').html(response);
        },
        error: function(jqXHR, status, error) {
            console.log(status   ": "   error);
        }
    });
});

The code above works well when selecting "County Name". It displays the "City Name" in the second select field properly. I then tried to add in the third select field "Street Name" and added what I "think" should work for the CFML and HTML pieces. When I got to the JS part of the code I hit a brick wall head on. I can't seem to find, perhaps for lack of the right search terms, how to add an additional AJAX call.

I found this question: Parallel asynchronous Ajax requests using jQuery

There were multiple answers but the closest one I "think" that's relevant to my question is this:

$.whenAll({
    val1: $.getJSON('/values/1'),
    val2: $.getJSON('/values/2')
})
    .done(function (results) {
        var sum = results.val1.value   results.val2.value;

        $('#mynode').html(sum);
    });

I believe they are called "promise"?

The code needs to keep the second select field "City Name" from changing and to properly use the AJAX "url" parameter with multiple values. I know I probably need something "similar" to this for the URL part in reference to the above code:

url:'includes/county.cfm?work_area_county_id=' value '&work_area_city_id=' value

For consideration:

  1. I know my code is not great and I know it could be written better. I'm working hard to improve.
  2. I'm not a full time coder but I have been coding off and on for many years. I still consider myself a newbie with CFML/JavaScript/jQuery so a lot of the methodology goes over my head.
  3. The code will be written in cfscript and converted to a CFC in the future. For now I made it simple and used tags.
  4. I removed the irrelevant HTML code from the display.cfm.

Any input or guidance on the above would be greatly, greatly appreciated! :D

CodePudding user response:

I'm going to show you an example of how I'd deal with such a task. My answer is probably not the best or cleanest solution: I had terrible coding practices in the past (I still tend to write spaghetti and I constantly struggle against doing it). But, I'm in the process of changing that bad habbits and that is fun.

My solution is written with an OOP approach. I'd really recommend everyone trying to go that path, because that quickly begins to feel way more natural: It just feels right, especially when you need to fix or extend your code.

I'd also try using cfscript instead of tags then, because writing OOP with components and functions is simplier, especially if you have some experience with JavaScript (which is somehow similar). However, I'm providing the tag approach, because I think that is what you'd like.

This solution basically consists of 4 files:

  1. display.cfm: The entry template/page.
  2. components/CountiesDAO.cfc: A component that acts as a data access object for your counties with corresponding getters. Note that I'm mimicking the DB request with QoQ. You should be able to use your datasource instead with the real data there.
  3. countyForm.js: The JavaScript file with the jQuery ajax function to obtain the data deferred objects and populate the html containers with the response data (for more information please see my comments in the code).
  4. ajaxAPI.cfm: The template that echos/outputs all the data as JSON for your jQuery ajax requests.

The main problem is that you need to retrieve more JavaScript promises, for each ajax request one. In jQuery that happens with defered object.

Here are the files:

1. display.cfm:

<!DOCTYPE html>
<html lang="en">
<meta charset="UTF-8">
<title>Page Title</title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<body>

<cfset CountiesDAO= new components.CountiesDAO() />
<cfset queryWorkAreaCounties=CountiesDAO.getWorkAreaCounties()>


<cfoutput>
    <select id="county">
        <option value="0">Select</option>
        <cfloop query="queryWorkAreaCounties">
            <option value="#queryWorkAreaCounties.work_area_county_id#">
                #queryWorkAreaCounties.work_area_county_name#
            </option>
        </cfloop>
    </select>
    <div id="cityContainer"></div>
    <div id="streetContainer"></div>
</cfoutput>

<!-- embedded jquery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="countyForm.js"></script>
</body>
</html>

2. components/CountiesDAO.cfc

<cfcomponent>

    <cffunction name="init">
        <cfset variables.queriesQoQ["work_area_counties"]=createWorkAreaCounties()>
        <cfset variables.queriesQoQ["Work_area_cities"]=createWorkAreaCities()>
        <cfset variables.queriesQoQ["Work_area_streets"]=createWorkAreaStreets()>
        <cfreturn this>
    </cffunction>

    <!--- functions to create Data to emulate a DB with QoQ --->
    <cffunction name="createWorkAreaCounties" 
                access="private" 
                returntype="query"
                hint="create query to simulate a DB data table for QoQ"> 

        <cfset work_area_countyTable= queryNew(
            "work_area_county_id, work_area_county_name",
            "integer,varchar",
            [ 
                {"work_area_county_id":1,"work_area_county_name":"Los Angeles County"}, 
                {"work_area_county_id":2,"work_area_county_name":"Cook County"},
                {"work_area_county_id":3,"work_area_county_name":"Harris County"},
                {"work_area_county_id":4,"work_area_county_name":"Maricopa County"}
            ])/>

        <cfreturn work_area_countyTable> 
    </cffunction> 


    <cffunction name="createWorkAreaCities" 
                access="private" 
                returntype="query"
                hint="create query to simulate a DB data table for QoQ"> 

        <cfset work_area_cityTable= queryNew(
            "work_area_city_id, work_area_city_name, work_area_county_id",
            "integer,varchar,integer",
            [ 
                {"work_area_city_id":1,"work_area_city_name":"Agoura Hills" , "work_area_county_id":1}, 
                {"work_area_city_id":2,"work_area_city_name":"Alhambra" , "work_area_county_id":1}, 
                {"work_area_city_id":3,"work_area_city_name":"Bradbury" , "work_area_county_id":1}, 
            
                {"work_area_city_id":4,"work_area_city_name":"Arlington Heights" , "work_area_county_id":2}, 
                {"work_area_city_id":5,"work_area_city_name":"Bellwood" , "work_area_county_id":2}, 
                {"work_area_city_id":6,"work_area_city_name":"Bridgeview" , "work_area_county_id":2}, 
            
                {"work_area_city_id":7,"work_area_city_name":"Baytown" , "work_area_county_id":3}, 
                {"work_area_city_id":8,"work_area_city_name":"Cove" , "work_area_county_id":3}, 
                {"work_area_city_id":9,"work_area_city_name":"The Woodlands" , "work_area_county_id":3}, 
            
                {"work_area_city_id":10,"work_area_city_name":"Avondale" , "work_area_county_id":4}, 
                {"work_area_city_id":11,"work_area_city_name":"Phoenix" , "work_area_county_id":4}, 
                {"work_area_city_id":12,"work_area_city_name":"Glendale" , "work_area_county_id":4}, 
            ])/>

        <cfreturn work_area_cityTable> 
    </cffunction> 

    <cffunction name="createWorkAreaStreets" 
                access="private" 
                returntype="query"
                hint="create query to simulate a DB data table for QoQ"> 

        <cfset work_area_streetTable= queryNew(
            "work_area_street_id, work_area_street_name, work_area_city_id",
            "integer,varchar,integer",
            [ 
                {"work_area_street_id":1,"work_area_street_name":"Street One Agoura Hills", "work_area_city_id": 1 },
                {"work_area_street_id":2,"work_area_street_name":"Street Two Agoura Hills", "work_area_city_id": 1 }, 
                {"work_area_street_id":3,"work_area_street_name":"Street Three Agoura Hills", "work_area_city_id": 1 }, 

                {"work_area_street_id":4,"work_area_street_name":"Street One Alhambra", "work_area_city_id": 2 },
                {"work_area_street_id":5,"work_area_street_name":"Street Two Alhambra", "work_area_city_id": 2 }, 
                {"work_area_street_id":6,"work_area_street_name":"Street Three Alhambra", "work_area_city_id": 2 },

                {"work_area_street_id":7,"work_area_street_name":"Street One Bradbury", "work_area_city_id": 3 },
                {"work_area_street_id":8,"work_area_street_name":"Street Two Bradbury", "work_area_city_id": 3 }, 
                
                {"work_area_street_id":9,"work_area_street_name":"Street One Arlington Heights", "work_area_city_id": 4 },
                {"work_area_street_id":10,"work_area_street_name":"Street Two Arlington Heights", "work_area_city_id": 4 }, 
                
                {"work_area_street_id":11,"work_area_street_name":"Street One Bellwood", "work_area_city_id": 5 },
                {"work_area_street_id":12,"work_area_street_name":"Street Two Bellwood", "work_area_city_id": 5 }, 
                
                {"work_area_street_id":13,"work_area_street_name":"Street One Bridgeview", "work_area_city_id": 6 },
                {"work_area_street_id":14,"work_area_street_name":"Street Two Bridgeview", "work_area_city_id": 6 }, 
                
                {"work_area_street_id":15,"work_area_street_name":"Street One Baytown", "work_area_city_id": 7 },
                {"work_area_street_id":16,"work_area_street_name":"Street Two Baytown", "work_area_city_id": 7 }, 
                
                {"work_area_street_id":17,"work_area_street_name":"Street One Cove", "work_area_city_id": 8 },
                {"work_area_street_id":18,"work_area_street_name":"Street Two Cove", "work_area_city_id": 8 }, 
                
                {"work_area_street_id":19,"work_area_street_name":"Street One The Woodlands", "work_area_city_id": 9 },
                {"work_area_street_id":20,"work_area_street_name":"Street Two The Woodlands", "work_area_city_id": 9 }, 
                
                {"work_area_street_id":21,"work_area_street_name":"Street One Avondale", "work_area_city_id": 10 },
                {"work_area_street_id":22,"work_area_street_name":"Street Two Avondale", "work_area_city_id": 10 }, 
                
                {"work_area_street_id":23,"work_area_street_name":"Street One Phoenix", "work_area_city_id": 11 },
                {"work_area_street_id":24,"work_area_street_name":"Street Two Phoenix", "work_area_city_id": 11 }, 
                
                {"work_area_street_id":25,"work_area_street_name":"Street One Glendale", "work_area_city_id": 12 },
                {"work_area_street_id":26,"work_area_street_name":"Street Two Glendale", "work_area_city_id": 12 },
            ])/>
        <cfreturn work_area_streetTable>
    </cffunction>


    <cffunction name="getWorkAreaCounties"  
                access="public" 
                returntype="query"
                hint="function to return all counties">  
        
        <cfset work_area_county=queriesQoQ["work_area_counties"]>
        
        <cfquery name="qCity" dbtype="query" >
            SELECT * FROM work_area_county
        </cfquery>
        
        <cfreturn qCity> 
    
    </cffunction>


    <cffunction name="getWorkAreaCitiesByCountyID"  
                access="public" 
                returntype="query"
                hint="function to return all cities of a county"> 
        
        <cfargument type="numeric" name="countyid" required="true">
        
        <cfset work_area_city=queriesQoQ["work_area_cities"]>
        
        <cfquery name="qCity" dbtype="query" >
            SELECT  work_area_city_id, work_area_city_name, work_area_county_id 
            FROM    work_area_city 
            WHERE   work_area_county_id = <cfqueryparam value="#arguments.countyid#" cfsqltype="cf_sql_int">
        </cfquery>
        
        <cfreturn qCity> 
    
    </cffunction>


    <cffunction name="getWorkAreaStreetsByCityID"  
                access="public" 
                returntype="query"
                hint="function to return all streets of a city">  
        
        <cfargument type="numeric" name="cityid" required="true">
        
        <cfset work_area_street=queriesQoQ["work_area_streets"]>
        
        <cfquery name="qStreet" dbtype="query" >
            SELECT  work_area_street_id, work_area_street_name, work_area_city_id 
            FROM    work_area_street 
            WHERE   work_area_city_id = <cfqueryparam value="#arguments.cityid#" cfsqltype="cf_sql_int">
        </cfquery>
        
        <cfreturn qStreet> 
    
    </cffunction> 

</cfcomponent>

3. countyForm.js

// ajax function
function sendAjaxAndUpdateForm( 
                url, 
                selectorForValue,  
                selectorForHTMLResponse ,  
                callbackFunction ){

    let value = $( selectorForValue ).val();
    
    /* return the ajax request as deferred object with done()/fail(). For more information, please see:
    *  https://api.jquery.com/jquery.ajax/ 
    *  https://stackoverflow.com/questions/10931836/should-i-use-done-and-fail-for-new-jquery-ajax-code-instead-of-success-and
    */
    return $.ajax({
            method: "GET",
            url: url   value,
            }).done( function( result ) {
                
                //populate HTML div with returned html
                $( selectorForHTMLResponse ).html( result.contentHTML );

                // invoke callback if a callback has been submitted
                if ( callbackFunction && typeof( callbackFunction ) === "function") { 
                    callbackFunction();
                }
            
            }).fail( function( e ) { 
                    
                //log some info and alert about fail
                console.dir( e.responseText );
                alert('Ops! Something went wrong!');

            });
}


$( document ).ready(function() {

    // add listeners to HTML container and make use of callbacks
    $('#county').change(
        function() {
            sendAjaxAndUpdateForm(
                url='ajaxAPI.cfm?work_area_county_id=',
                selectorForValue='#county',
                selectorForHTMLResponse='#cityContainer',
                callbackFunction= function(){ $('#city').change(
                        function() {
                            sendAjaxAndUpdateForm(
                                url='ajaxAPI.cfm?work_area_city_id=',
                                selectorForValue='#city',
                                selectorForHTMLResponse='#streetContainer',
                                callbackFunction=function(){ $('#street').change( 
                                    function(){ alert( 'Street ID:'   $('#street').val()   'for \''   $( '#street option:selected' ).text()   '\' selected.' )}  )
                                }
                            );
                     })
                }
            );  
        });
        
});

4. ajaxAPI.cfm

<!--- Function to output content as JSON for a response of the ajax request --->
<cffunction name="outputJSON"  
            access="private" 
            returntype="void"
            hint="function to output data as application/json"> 
    <cfargument type="struct" name="contentStruct" required="true">
    <cfcontent reset = "true">
    <cfheader name="content-type" value="application/json">
    <cfoutput>#serializeJSON( contentStruct )#</cfoutput>
    <cfabort>
</cffunction> 
 
<!--- instantiate Data Access Object Component--->
<cfset CountiesDAO = new components.CountiesDAO() />


<cfif structKeyExists(url, "work_area_county_id") 
      and len( work_area_county_id ) gt 0>

    <cfset queryWorkAreaCities=CountiesDAO.getWorkAreaCitiesByCountyID( url.work_area_county_id )>
    
    <cfsavecontent variable="result.contentHTML">
        <cfoutput>
            <select name="city" id="city" >
                <option value="">Select City</option>
                <cfloop query="queryWorkAreaCities">
                    <option value="#queryWorkAreaCities.work_area_city_id#">#queryWorkAreaCities.work_area_city_name#</option>
                </cfloop>
            </select>
        </cfoutput> 
    </cfsavecontent>
        
     <!--- echo json --->
     <cfset outputJSON( result )>

</cfif>



<cfif structKeyExists( url, "work_area_city_id" ) and len( work_area_city_id ) gt 0>
        
    <cfset queryWorkAreaStreets=CountiesDAO.getWorkAreaStreetsByCityID( url.work_area_city_id )>
    
    <cfsavecontent variable="result.contentHTML">
        <cfoutput>
        <select name="street" id="street" >
            <option value="">Select Street</option>
            <cfloop query="queryWorkAreaStreets">
                <option value="#queryWorkAreaStreets.work_area_street_id#">#queryWorkAreaStreets.work_area_street_name#</option>
            </cfloop>
        </select>
        </cfoutput>
    </cfsavecontent>

    <!--- echo json --->
    <cfset outputJSON( result )>

</cfif>

The above solution is far from finished, but it should just give you an option to start playing around and have some fun.

  • Related