How can I modify this doctrine query builder?

Vote count:

0

The profiler shows this query:

SELECT
  count(DISTINCT c0_.id) AS sclr0
FROM
  customers c0_
  LEFT JOIN customers_addresses c1_ ON c0_.id = c1_.customer_id
  LEFT JOIN customers_phones c2_ ON c0_.id = c2_.customer_id
WHERE
(
    c0_.is_lead = ?
    AND c0_.firstname LIKE ?
  )
  OR c0_.lastname LIKE ?
  OR c0_.email LIKE ?
  OR c0_.company LIKE ?
  OR c1_.street_address1 LIKE ?
  OR c1_.street_address2 LIKE ?
  OR c1_.city LIKE ?
  OR c1_.state = ?
  OR c1_.zipcode = ?
  OR c2_.phone LIKE ?

And clearly is not what I want, I want it to be like this:

SELECT
  count(DISTINCT c0_.id) AS sclr0
FROM
  customers c0_
  LEFT JOIN customers_addresses c1_ ON c0_.id = c1_.customer_id
  LEFT JOIN customers_phones c2_ ON c0_.id = c2_.customer_id
WHERE
(
    c0_.is_lead = ?

  )
  AND 
  (c0_.firstname LIKE ?
  OR c0_.lastname LIKE ?
  OR c0_.email LIKE ?
  OR c0_.company LIKE ?
  OR c1_.street_address1 LIKE ?
  OR c1_.street_address2 LIKE ?
  OR c1_.city LIKE ?
  OR c1_.state = ?
  OR c1_.zipcode = ?
  OR c2_.phone LIKE ?
  )

My query builder looks like this:

 public function search($keywords = null, $lead=0){
    if ($keywords === null) return $this->findAllOrderedByName($lead);

    $qb = $this->createQueryBuilder ('c')
        ->addSelect('a')
        ->addSelect('p')
        ->leftJoin('c.addresses', 'a')
        ->leftJoin('c.phones', 'p');
    $qb->where('c.isLead = :lead');

    $qb->andWhere('c.firstname like :firstname');
    $qb->orWhere('c.lastname like :lastname');
    $qb->orWhere('c.email like :email');
    $qb->orWhere('c.company like :company');

    $qb->orWhere('a.streetAddress1 like :streetAddress1');
    $qb->orWhere('a.streetAddress2 like :streetAddress2');
    $qb->orWhere('a.city like :city');
    $qb->orWhere('a.state = :state');
    $qb->orWhere('a.zipcode = :zipcode');
    $qb->orWhere('p.phone like :phone');

    $qb->setParameter('lead', $lead);
    $qb->setParameter('firstname', '%' . $keywords . '%');
    $qb->setParameter('lastname',  '%' . $keywords . '%');
    $qb->setParameter('email',  '%' . $keywords . '%');
    $qb->setParameter('company',  '%' . $keywords . '%');
    $qb->setParameter('streetAddress1',  '%' . $keywords . '%');
    $qb->setParameter('streetAddress2',  '%' . $keywords . '%');
    $qb->setParameter('city',  '%' . $keywords . '%');
    $qb->setParameter('state',  $keywords);
    $qb->setParameter('zipcode',  $keywords);
    $qb->setParameter('phone',  '%' . $keywords . '%');
    $qb->orderBy('c.lastname', 'ASC');
    return $qb;
}

The way I currently have my query builder returns results even when the search keywords do not match any records because of the joins, I don't know how I can modify the query builder method, any ideas?

asked 15 secs ago

MikeGA

370

How can I modify this doctrine query builder?