CODEWITHSUNDEEP
symfonyquery-builder
Bin Chen
Bin Chen

Reputation: 103

Symfony querybuilder compare string when not empty

I am new to CreateQueryBuilder and I have a quite complex query with nested and->or->and condition. It almost works (thanks Patrik Karisch) but I am blocked by a simply and "stupid" question. I want to compare a string column only when the value from column or parameter is not empty. (Note: I cannot use if and add, I have to use logic inside orX).

$expr->neq('s.studentEmail', '') always returns true when the vale is '', but I am expecting false

$queryBuilder = $repo->createQueryBuilder('s');
$expr = $queryBuilder->expr();
->andWhere($expr->orX(
$expr->andX($expr->neq('s.studentEmail', ''), $expr->isNotNull('s.studentEmail'), $expr->like('lower(s.studentEmail)', 'lower(:email)'),
    $expr->......

Upvotes: 0

Views: 2340

Answers (1)

Cerad
Cerad

Reputation: 48865

I think you might be over complicating things. You can test your parameter outside of the query and simply skip adding the condition at all;

$qb = $studentRepo->createQueryBuilder('student');
if ($email) {
    $qb->andWhere('student.email LIKE %:email%');
    $qb->setParameter('email',$email);
}

I also don't see the need for the lower functions but put them in if you like.

I started using expressions because that is the way the documentation shows but not really got comfortable with them. Very verbose. Now I just write the DQL out as shown above. Seems to work the same and is a bit easier to read.

Finally, you should define your email column to either be NOT NULL in which case all empty emails are blank strings. Or allow it to be NULL and ensure that empty strings are always entered as NULL. Mixing the two together can over complicate things.

Upvotes: 1

Related Questions