CODEWITHSUNDEEP
phpsqlsymfonydoctrine-orm
sajili
sajili

Reputation: 53

Doctrine: Multiple orWhere with LIKE condition

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?

Upvotes: 2

Views: 4176

Answers (1)

Jovan Perovic
Jovan Perovic

Reputation: 20201

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 ;)

Upvotes: 5

Related Questions