Doctrine filtering by multiple columns

Question

I need to filter in doctrine through multiple fields like the following:

SELECT company , state  
FROM employees
WHERE 
(company, state) <> ('xxxxx', 'xxxx')
AND
(company, state) <> ('xxxx', 'xxxx')
GROUP BY company, state

I tried the following way:

 $qb->andWhere($qb->expr()->andX($qb->expr()->neq('b.company',"'".$i['description']."'"), $qb->expr()->neq('b.state', "'".$i['state']."'")));

But the result is not the desired:

(company <> 'xxxxx' AND state <> 'xxxx') AND (company  <> 'xxxxx' AND state  <> 'xxxxx') 

How can i do the first via doctrine? regards!

Answer 1

That query above could work as an OR of two lists which will make things simpler for you.

E.g.

WHERE state NOT IN states OR company NOT IN companies

As if either is true then that employee's combination of company + state is not an excluded one.

Given that, you could run this as below:

    $qb = $this->getEntityManager()->createQueryBuilder();

    $excludedCompanies = ['company1', 'another company', 'company etc'];
    $excludedStates = ['state1', 'another state', 'state etc'];

    return $qb->select('e.company, e.state')
        ->from('YourBundle:Employee', 'e')
        ->where($qb->expr()->notIn('e.company', ':excludedCompanies'))
        ->orWhere($qb->expr()->notIn('e.state', ':excludedStates'))
        ->setParameter('excludedCompanies', $excludedCompanies)
        ->setParameter('excludedStates', $excludedStates)
        ->groupBy('e.company')
        ->addGroupBy('e.state')
        ->getQuery()
        ->getResult()
    ;