我目前正在使用symfony2进行一个项目。我正在为我的项目做一个搜索功能,我遇到了这个问题。在我的WHERE语句中有多个条件。第一个条件很好,但第二个条件不行。下面是我的WHERE语句代码:
WHERE (a.refNumber LIKE :refNumber OR a.poNumber LIKE :refNumber
OR a.invoiceNumber LIKE :refNumber
OR a.asnNumber LIKE :refNumber
OR a.adjNumber LIKE :refNumber )
AND b.gsNumber LIKE :gsNumber
AND b.senderId LIKE :senderId
AND b.receiverId LIKE :receiverId
AND c.docType LIKE :docType
AND c.direction LIKE :direction
AND (b.transactionDate <= :startDate AND b.transactionDate >= :endDate)
ORDER BY b.transactionDate desc
解释:
我的refNumber
可以是poNumber, invNumber, asnNumber or adjNumber
,它可以搜索它们中的任何一个。而GsNumber
则可以留空。它只能用于指定的docType。
DocType
无论搜索什么都不能留空。
对于交易日期,它必须搜索任何属于开始日期和结束日期查询的交易。遗憾的是,它不起作用,我找不到日期。Start date
必须大于或等于交易日期。End date
必须小于或等于某个交易日期。它必须返回属于该查询的事务。但是,它不工作,它返回空白。但如果我删除开始日期和结束日期,一切都很好。我不知道该怎么办了。
任何帮助都是感激的。
如果搜索参数是可选的,则需要在该参数为空时使使用该参数的测试成功。
WHERE (:refNumber = '' OR a.refNumber LIKE :refNumber OR a.poNumber LIKE :refNumber
OR a.invoiceNumber LIKE :refNumber
OR a.asnNumber LIKE :refNumber
OR a.adjNumber LIKE :refNumber )
AND (:gsNumber = '' OR b.gsNumber LIKE :gsNumber)
AND (:senderId = '' OR b.senderId LIKE :senderId)
AND (:receiverId = '' OR b.receiverId LIKE :receiverId)
AND (:docType = '' OR c.docType LIKE :docType)
AND (:direction = '' OR c.direction LIKE :direction)
AND (:startDate = '' OR :endDate = '' OR b.transactionDate BETWEEN :startDate AND :endDate)
更常见的处理方法是动态地构建WHERE
子句,因此如果搜索字段为空,则不放入gsNumber
测试。参见带有一个或多个参数的搜索表单
在您的原始查询中,您的:startDate
和:endDate
被调换了。
BETWEEN
在这里绝对是更好的方法,但是在更新查询中,语法是关闭的。改为:
WHERE (a.refNumber LIKE :refNumber OR a.poNumber LIKE :refNumber
OR a.invoiceNumber LIKE :refNumber
OR a.asnNumber LIKE :refNumber
OR a.adjNumber LIKE :refNumber )
AND b.gsNumber LIKE :gsNumber
AND b.senderId LIKE :senderId
AND b.receiverId LIKE :receiverId
AND c.docType LIKE :docType
AND c.direction LIKE :direction
AND b.transactionDate BETWEEN :startDate AND :endDate
ORDER BY b.transactionDate desc