Skip to main content
D
Dr_Data
Participant
October 20, 2009
Question

What's your opinion on way to go

  • October 20, 2009
  • 1 reply
  • 380 views

Hi folks,

I am expanding an internal search engine (search based upon tables of characteristics such as size, color, etc.) from three to six possible search fields.  The current search routine is one fairly long file that is a series of queries nested in a CFIF...ELSEIF...ENDIF structure, such as:

CFIF A eq 1 and B eq 2

     Select all from tablex where A eq 1 and B eq 2

ELSEIF

     select all from tablex

CFIF

Adding all the additional queries possible from the expansion from 3 to 6 search criteria could yield a file which is 3-4000 lines of code (the queries are pretty long and involve mutltiple tables). I am considering two options:

(1)  One long file

(2)  A shorter file containing the CFIF structure which then calls a query from a separate file, with each possibility having a query file, sort of like:

CFIF A eq 1 and B eq 2

     CFhttp:  query12,cfm

CFELSEIF

CFIF

What are your opnions/ suggestions regarding run-time speed and "user experience"?

Appreciate any and all opinions

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    October 20, 2009

    From a development and maintenance perspective, many small files would be preferable to one long one.  However, there is another option, dynamic sql.

    Using your simple example,

    SelectClause = "select all from tablex";

    WhereClause = "where 1 = 1";

    if (a is 1 )

    WhereClause = WhereClause & " and field1 = 1";

    etc

    Then you only need one query

    <cfquery>

    #selectclause#

    #preservesinglequotes(whereclause)#

    </cfquery>

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