0

IS there a better way to build a complex query in Symfony 2? My real query is quite complex, but can be simplified to something like "A and ((B and C) or (B and D))" (I know math equation as "A and B and (C or D)", but my real query cannot be simplified). I have experience to use andWhere and orX, but my question was how to use 'and' / 'expr()->andX' inside 'orX'.

Example below (Question was on the pseudocode parts inside orX):

$qBuilder = $repo->createQueryBuilder('s')
->select('s.id')
->Where('s.FirstName = :fname')
->Where('s.LastName = :lname')
->andWhere($qBuilder->expr()->orX(
    (':email is not empty AND s.Email = :email'),
    (':phone is not empty AND s.HomePhone = :phone ),
    (':phone is not empty AND s.StudentMobile = :phone ),
    (':mphone is not empty AND s.HomePhone = :mphone),
    (':mphone is not empty AND s.StudentMobile = :mphone)
    ))
->setParameter('fname', strtolower($fname))
->setParameter('lname', strtolower($lname))
->setParameter('email', $email)
->setParameter('phone', $phoneNumber)
->setParameter('mphone', $studentmobile);
Elias Van Ootegem
  • 74,482
  • 9
  • 111
  • 149
Bin Chen
  • 93
  • 1
  • 8

1 Answers1

2

Simply make an andX() expr inside. All expression functions are nestable

->andWhere($qb->expr()->orX(
    $qb->expr()->andX(':email is not empty', 's.Email = :email'),
    $qb->expr()->andX(':phone is not empty', 's.HomePhone = :phone'),
    $qb->expr()->andX(':phone is not empty', 's.StudentMobile = :phone'),
    $qb->expr()->andX(':mphone is not empty', 's.HomePhone = :mphone'),
    $qb->expr()->andX(':mphone is not empty', 's.StudentMobile = :mphone')
))
Emii Khaos
  • 9,983
  • 3
  • 34
  • 57
  • Another question (I start a new question also). Why I always got true from $expr->neq('s.studentEmail', '') when the value is '' or others? – Bin Chen Nov 07 '15 at 13:53