Individual parameters or seat assembly

Question

I create an object to search for orders in my database. There are many possible parameters that the user can set, and they can set as many as are required for each search. I created customization methods to collect all the parameters needed for the search.

My question is that. What would be "best practice"

• Saving parameters and constructing sentences WHEREwhen calling a methoddoSearch
• Building a sentence WHEREwhen setting parameters

I would like to understand the reason for any recommendation.

Please note that an object is set for each search, so I don’t have to worry about a second search with different parameters.

Answer 1

You must separate the code to search for your order from the code that SQL creates. SQL must be built in a derived (or Strategy derived) class OrderSearch. Once you have made this separation, it does not really matter when you build SQL.

Make it a little easier. Given a class with a name OrderSearchthat has many customization methods for your search criteria, you would like to have a subclass with a name OrderSearchSQLBuilder. Note that the subclass is dependent on the base class and that the base class is independent of the subclass. It is very important. This independence allows you to ignore whether SQL is built in setter methods or in a search method. See Dependency Inversion Principle (DIP) .

, . , , SQL, in-ram OrderSearch, . , , ..

Answer 2

SQL, . , where SQL-. .

- ...

<cffunction name="getByAttributesQuery" access="public" output="false" returntype="query">
    <cfargument name="id" type="numeric" required="false" />
    <cfargument name="userName" type="string" required="false" />
    <cfargument name="firstName" type="string" required="false" />
    <cfargument name="lastName" type="string" required="false" />
    <cfargument name="createdAt" type="date" required="false" />
    <cfargument name="updatedAt" type="date" required="false" />
    <cfargument name="orderby" type="string" required="false" />

    <cfset var qList = "" />        
    <cfquery name="qList" datasource="#variables.dsn#">
        SELECT  
            id,
            userName,
            firstName,
            lastName,
            createdAt,
            updatedAt
        FROM    users
        WHERE       0=0
    <cfif structKeyExists(arguments,"id") and len(arguments.id)>
        AND id = <cfqueryparam value="#arguments.id#" CFSQLType="cf_sql_integer" />
    </cfif>
    <cfif structKeyExists(arguments,"userName") and len(arguments.userName)>
        AND userName = <cfqueryparam value="#arguments.userName#" CFSQLType="cf_sql_varchar" />
    </cfif>
    <cfif structKeyExists(arguments,"firstName") and len(arguments.firstName)>
        AND firstName = <cfqueryparam value="#arguments.firstName#" CFSQLType="cf_sql_varchar" />
    </cfif>
    <cfif structKeyExists(arguments,"lastName") and len(arguments.lastName)>
        AND lastName = <cfqueryparam value="#arguments.lastName#" CFSQLType="cf_sql_varchar" />
    </cfif>
    <cfif structKeyExists(arguments,"createdAt") and len(arguments.createdAt)>
        AND createdAt = <cfqueryparam value="#arguments.createdAt#" CFSQLType="cf_sql_timestamp" />
    </cfif>
    <cfif structKeyExists(arguments,"updatedAt") and len(arguments.updatedAt)>
        AND updatedAt = <cfqueryparam value="#arguments.updatedAt#" CFSQLType="cf_sql_timestamp" />
    </cfif>
    <cfif structKeyExists(arguments, "orderby") and len(arguments.orderBy)>
        ORDER BY #arguments.orderby#
    </cfif>
    </cfquery>

    <cfreturn qList />
</cffunction>

Answer 3

where, . , , , . , , where.

Answer 4

, , , WHERE, . , , - WHERE.

Answer 5

SQLServer where, . , , . , .

Answer 6

, .

"". , russ . , , , init.

, , , .

Answer 7

Option 1 is your best bet. Option 2 sounds dangerous. What if the parameter is updated? How do you replace it in your WHERE clause?

I would do something like this:

<cffunction name="doSearch" access="public" output="false" returntype="query">        
    <cfset var qList = "" />
    <cfquery name="qList" datasource="#variables.dsn#">
       SELECT
           ...
       FROM
           ...
       WHERE 0=0 
         <cfif len(getID())>
           AND id = <cfqueryparam value="#getID()#" CFSQLType="cf_sql_integer" />
         </cfif>       
         <cfif len(getUserName())>
           AND userName = <cfqueryparam value="#getUserName()#" CFSQLType="cf_sql_varchar" />
         </cfif>
    </cfquery>
    <cfreturn qList />
</cffunction>