= <> SQL operators in components

[cf_matt]

If i want to change the "=" of this SQL syntax to "<>" I have to pass the
operator as an argument is there a better way of doing this? its just its a bit
messy

<cfif isdefined("arguments.page_name")>
AND web_page_name = <cfqueryparam value="#arguments.page_name#"
cfsqltype="CF_SQL_VARCHAR">
</cfif>

Hope you can help

Thanks

Matt

[OldCFer]

Well, you have to do an 'if' somewhere. You could always pass 'EQ' or 'NEQ' and
depending on the value, change the operator. It's a little more user friendly.

[cf_matt]

At the moment I have:

<cfif isdefined("arguments.page_id")>
AND web_page_id = <cfqueryparam value="#arguments.page_id#"
cfsqltype="CF_SQL_INTEGER">
</cfif>
<cfif isdefined("arguments.page_id_neq")>
AND web_page_id <> <cfqueryparam value="#arguments.page_id_neq#"
cfsqltype="CF_SQL_INTEGER">
</cfif>

It is a bit more user friendly but it's still a bit messy in my opinion. I
guess you could pass the operator and argument as a structure but its still a
bit clunky.
The problem with passing the operator on its own is you need to know which
field it relates to. I might want web_page_name to equal 'test' but not equal
to 'cheese'.

I just think this is something fairly common and would have thought there was
a standard way of doing it.

Matt

[Adam Cameron]

You could pass each argument in as a struct of operator / value:

arguments.page_name.operator = "=";
arguments.page_name.value = "foo";

Possibly accept either a simple value or a struct. If it's a simple value,
it's just the value. If it's a struct, then expect operator/value pairs.

But both situations are ugly.

I'm guessing you're writing a CFC wrapper for a DB table? Our approach
here was to have separate arguments for equality filtering (which is what
you're doing most of the time), and then have an addition
arguments.whereExpressions, which was free-form.

--

Adam

[Ro]

If you just want to keep your query cleaner and easier to see, I would
create variables containing each AND string before the actual query
and then, I would output them through the query

Like so:

<cfscript>
// AND statement 1
AND1="";
if(variable1 EQ "Yes"){
AND1=" AND Columname = 'value' ";
}

// AND statement 2
AND2="";
if(Condition){
AND2=" AND Columname = 'value' ";
}

// AND statement 3
AND3="";
if(Condition){
AND3=" AND Columname = 'value' ";
}

// ORDER BY statement
ORDER="";
if(Condition){
ORDER=" ORDER BY Columname1,Columname2 ASC ";
}

</cfscript>

<cfquery datasource="test" name="q_name">

SELECT *
FROM table
WHERE 0=0 <!--- To Escape the "Where" statement --->
#AND1#
#AND2#
#AND3#
#ORDER#

</cfquery>

On Mon, 16 May 2005 08:43:39 +0000 (UTC), "cf_matt"
<webforumsuser@macromedia.com> wrote:

>If i want to change the "=" of this SQL syntax to "<>" I have to pass the
>operator as an argument is there a better way of doing this? its just its a bit
>messy
>
> <cfif isdefined("arguments.page_name")>
> AND web_page_name = <cfqueryparam value="#arguments.page_name#"
>cfsqltype="CF_SQL_VARCHAR">
> </cfif>
>
> Hope you can help
>
> Thanks
>
> Matt