Skip to main content
N
namtax
Known Participant
February 24, 2010
Question

CFC Best Practise : Using cfif statements in Where Clause

  • February 24, 2010
  • 2 replies
  • 1535 views

Hi there

Wondering if anyone can advise on the best route to take for handlings cfcs...

I have a function set up as so

<!--- GRAB SONG INFORMATION --->
    <cffunction name="getSong" access="remote" output="false" returntype="query" hint="return song information, filtered by user id">
       
    <!---Optional Artist Name Argument--->
    <cfargument name="nameArt" type="string" required="false" hint="artist we want to grab song for">       
    <!---Filter by user ID--->
    <cfargument name="idUsr" type="any" required="false" hint="if provided, filter by user id">
    <!---Optional Filter by user name--->
    <cfargument name="nameUsr" required="false" type="string" hint="if supplied, filter by user name">
     <!---Optional song id argument--->
    <cfargument name="idSng" required="false" type="numeric" hint="if supplied, grab information for song with this id">
        
    <!--- grab song from database --->
     <cfquery name="song" datasource="#APPLICATION.mx#">

         // SELECT WHICHEVER FIELDS NECESARRY
         SELECT.....


         <!---If user id is supplied--->
         <cfif isDefined('ARGUMENTS.idUsr')>
        <!---Filter results by user id--->
         WHERE s.userId = <cfqueryparam value = '#ARGUMENTS.IdUsr#' cfsqltype='CF_SQL_INTEGER'>
         </cfif>

         <!---If user name is supplied--->
          <cfif isDefined('ARGUMENTS.nameUsr')>
        <!---Filter results by user id--->
         WHERE s.userId =     (SELECT iUserID
                            FROM users
                            WHERE name = <cfqueryparam value = '#ARGUMENTS.nameUsr#' cfsqltype='CF_SQL_VARCHAR' maxLength="12">)
         </cfif>
        <cfif isDefined('ARGUMENTS.idSng')>
         WHERE s.iSongID = <cfqueryparam value = '#ARGUMENTS.idSng#' cfsqltype='CF_SQL_INTEGER'>
         </cfif>
         ORDER BY s.iSongID DESC
    </cfquery>
   
    <!---Return query output--->
        <cfreturn song>
    </cffunction>

Now im wondering, is it best to have multiple cfifs in the same function, filtering data dependant on what arguments are supplied, or is it best to seperate the function into seperate functions with different filters, without the cfifs?

Many thanks

    This topic has been closed for replies.

    2 replies

    Owainnorth
    Owainnorth
    Inspiring
    February 24, 2010

    As Dan rightly says there's not really any right or wrong way, and if it works then it's a successful solution.

    Only one small thing I might point out, some people prefer to bodge a WHERE clause (or sometimes check for a deleted flag etc), then use the arguments to define AND conditions rather than WHERE's - this makes sure that a valid SQL statement is constructed no matter what combination of arguments are supplied.

    Say, for example, you provide the idUsr and nameUsr arguments to your function. You'd end up with:

    SELECT *

    FROM  table

    WHERE  userId = x

    WHERE  userId = ( SELECT userID FROM... )

    WHERE...

    Which is obviously invalid. Therefore you need to bodge your WHERE clause. Some people simply put in "WHERE 1=1", which I'm not a massive fan of, but it works. If you have a deleted flag or data against your table, use that for the WHERE clause.All your other conditions are then ANDs rather than WHEREs. Say you gave it all the arguments, you then get:

    SELECT  *

    FROM   table

    WHERE  1=1

    AND  userID = x

    AND  userID = (select userid..(

    AND  title = 'x'...

    You've then got a completely future-proof function that you can chuck any combination of arguments at and it'll keep narrowing down your search.

    Think that makes sense...

    O.

    N
    namtaxAuthor
    Known Participant
    February 24, 2010

    Yeah I have seen that method used by a guy at work Owainnorth, thanks for your assistance

    D
    Dan_Bracuk
    Inspiring
    February 24, 2010

    One of the purposes of cfc's is to maximise the re-useability of it's code.  Having a single function will probably do that better.

    We all have our own programming styles and, for what it's worth, I try to avoid if/else logic inside queries.  Instead, I apply that logic beforehand and use the resulting variables inside the query.  The biggest advantage to doing it this way comes during development.  You get to dump the variables and look at them before you even think of visiting the db.

    N
    namtaxAuthor
    Known Participant
    February 24, 2010

    Hi there dan

    Could I get an example of one of your cffunctions implementing this method?

    Thanks

    D
    Dan_Bracuk
    Inspiring
    February 24, 2010


    <cffunction name="MoreData" access="private" returntype="string">
    <cfargument name="table" required="yes" type="string">
    <cfargument name="values" required="yes" type="string">
    <cfscript>
    var field1 = "unit";
    var field2 = "unit_code";
    var datatype = "cf_sql_char";
    var TheQuery = QueryNew("a");
    if (arguments.table is "service") {
    field1 = "service_mcase";
    field2 = "service_code";
    datatype = "cf_sql_integer";
    }
    </cfscript>

    <cfquery name="TheQuery" datasource="dw">
    select #field1# myfield
    from #arguments.table#
    where #field2# in (<cfqueryparam cfsqltype="#datatype#" value="#arguments.values#" list="yes">)
    </cfquery>

    <cfreturn ValueList(TheQuery.myfield)>
    </cffunction>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices