学说:多或具有LIKE条件 [英] Doctrine: Multiple orWhere with LIKE condition
问题描述
使用结构表:
id | count
/ string / id1 | 3
/ string / id1 / r1 | 2
/ string / id1 / r2 | 1
/ string / id2 / r1 | 2
/ string / id2 | 3
/ string / id2 / r1 | 2
/ string / id3 / r1 | 5
,我想选择 id 中需要的子串的所有行。
我需要所有在id中有子字符串的行: / string / id1 和 / string / id2
查询应该很简单 - 简单的sql:
select * from table_name其中id LIKE'/ string / id1%'OR id LIKE'/ string / id2%';
结果应该是:
id | count
/ string / id1 | 3
/ string / id1 / r1 | 2
/ string / id1 / r2 | 1
/ string / id2 / r1 | 2
/ string / id2 | 3
/ string / id2 / r1 | 2
不幸的是,如果您尝试在symfony和doctrine2中使用相同的查询:
$ ids = array('/ string / id1','/ string / id2');
$ query = $ this-> createQueryBuilder('r')
- > select('r');
foreach($ ids as $ id){
$ query-> orWhere(r.linkedId LIKE:id)
- > setParameter('id',$ id。 %);
}
$ plainQuery = $ query-> getQuery() - > getSQL();
$ results = $ query-> getQuery() - > getResult();
普通查询看起来像 select * from table_name其中id LIKE'/ string / id1%'OR id LIKE'/ string / id2%';
,但结果不是。
结果仅包含最后一行项目在ids - / string / id2
id | count
/ string / id2 / r1 | 2
/ string / id2 | 3
/ string / id2 / r1 | 2
如何解决?我的错误在哪里你有任何建议吗?
我无法确定,但这似乎与我的参数标识符冲突。 >
这是你想要做的:
- 构造基本的
SELECT * FROM table_name
语句 - 追加
WHERE r.linkedId LIKE:id
- 将
id
参数的值设置为/ string / id1%
- 追加
OR r.linkedId LIKE:id
- 设置
id
参数到/ string / id2%
(覆盖上一个值)< - AN ERROR
基本上,你正在告诉Doctrine用新的值覆盖以前定义的 id 参数的值。
您可以轻松克服这个问题。只需将 $ i
添加到参数名称
foreach($ ids as $ i => $ id){
// $ i这里的值为0,1,2,...
$ query-> orWhere(r.linkedId LIKE:id $ i)// append $ i
- > setParameter(id $ i,$ id。%); //但也附加在这里
}
请务必使用双引号,或连接(id。$ i
))
Use table with structure:
id | count
/string/id1 | 3
/string/id1/r1 | 2
/string/id1/r2 | 1
/string/id2/r1 | 2
/string/id2 | 3
/string/id2/r1 | 2
/string/id3/r1 | 5
and I want to select all rows which have needed substring in id.
i.e.
I need all rows which have substring in id: /string/id1 and /string/id2
The query should be easy - plain sql:
select * from table_name where id LIKE '/string/id1%' OR id LIKE '/string/id2%';
Result should be:
id | count
/string/id1 | 3
/string/id1/r1 | 2
/string/id1/r2 | 1
/string/id2/r1 | 2
/string/id2 | 3
/string/id2/r1 | 2
Unfortunately, if you try to use the same query in symfony and doctrine2:
$ids = array('/string/id1', '/string/id2');
$query = $this->createQueryBuilder('r')
->select('r');
foreach ($ids as $id) {
$query->orWhere("r.linkedId LIKE :id ")
->setParameter('id', $id."%");
}
$plainQuery = $query->getQuery()->getSQL();
$results = $query->getQuery()->getResult();
Plain query looks the same like select * from table_name where id LIKE '/string/id1%' OR id LIKE '/string/id2%';
, but results are not.
results contains only rows of last item in ids - /string/id2
id | count
/string/id2/r1 | 2
/string/id2 | 3
/string/id2/r1 | 2
How to solve it? Where is my mistake? Do you have any suggestion?
I cannot be sure but this seems to me like a conflict with parameter identifiers.
This is what you're trying to do:
- Construct the basic
SELECT * FROM table_name
statement - Append
WHERE r.linkedId LIKE :id
- set the value of
id
parameter to/string/id1%
- Append
OR r.linkedId LIKE :id
- set the value of
id
parameter to/string/id2%
(override the previous value) <-- AN ERROR
Basically, you are telling Doctrine to override previously defined value of id
parameter with new one.
You could easily overcome this issue. Just add $i
to parameter name
foreach ($ids as $i => $id) {
// $i here has the value of 0,1,2, etc...
$query->orWhere("r.linkedId LIKE :id$i" ) // append $i
->setParameter("id$i", $id."%"); // but also append it here
}
Be sure to use double quotes, or concatenate ("id" . $i
) instead ;)
这篇关于学说:多或具有LIKE条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!