使用 null 和列表进行查询

Question

我有

QueryExecute(
SELECT *
FROM dbo.pages
WHERE ID IN ( :id )
...
,
{
id : { value = rc.id, cfsqltype : "cf_sql_integer, list : true }
}

几乎所有时间都有效。事情是rc.id可以是空白的。如果为空，则应匹配所有 ID。我被困在如何做到这一点

Answer 1

这里有两个不同的选项。

1( 您可以操作查询字符串以仅在变量不为空时包含 IN 语句。

您如何执行此操作实际上实际上取决于查询的其余部分以及您希望如何执行此操作。

<cfset sqlstr = "SELECT * FROM dbo.pages" />
<cfif len(rc.id)>
<cfset sqlstr &= " WHERE ID IN ( :id )" />
</cfif>

您可以使用条件输出更干净地构建...

WHERE #len(rc.id) ? "ID IN ( :id )" : ""#

为了避免担心是否包含 WHERE/AND/OR 一个小技巧是包含一个始终通过的子句(或者在 ORing 的情况下永远不会通过(

WHERE 1=1 
#len(rc.id) ? "AND ID IN ( :id )" : ""#
AND ...

2( 参数传递两次并在数据库中执行检查

SELECT *
FROM dbo.pages
WHERE (len(':id') = 0 OR ID IN ( :id ))
...
-- note: this may be problematic if CF is passing null when blank

或只是长度

QueryExecute(
SELECT *
FROM dbo.pages
WHERE (:idlen > 0 OR ID IN ( :id ))
...
,
{
id : { value = rc.id, cfsqltype : "cf_sql_integer, list : true },
idlen : { value = len(rc.id), cfsqltype : "cf_sql_integer" }
}

Answer 2

在这里，我也给出了有关使查询参数成为条件的示例代码。出于测试目的，我创建了一些默认参数和值

<cfparam name="id" default="1,2,3,4,5,6">

将查询详细信息设置为字符串，并将其保存到另一个变量，如上所述@danRoberts。

<cfset MyQry = "SELECT * FROM pages ">

检查rc.id值是否为空。如果不是，请附加IN子句。

<cfif len(rc.id) >
<cfset MyQry &= " WHERE id IN ( :id ) ">
</cfif>

作为参考，我还添加了一些额外的运算符。

<cfset MyQry &= " limit 10 "> 

接下来，基于新运算符创建新查询

<cfset qry = new Query( datasource ='yourDataSource' , sql = MyQry) >

然后，如果rc.id不为空，则为:ID添加一个查询参数

<cfif len(rc.id) >
<cfset qry.addParam( name="id", value="#rc.id#",cfsqltype="cf_sql_varchar",list='true') >
</cfif>

注意：使用IN运算符时，应设置list='true'for the queryParam。

然后执行查询，如下所示

<cfset Result = qry.execute().getResult() >
<cfdump var="#Result#" />

仅当 rc.ID 包含值时，带有 queryParam 的WHERE子句才会执行。