Doctrine/Symfony WHERE clause for reports

Question

I'm trying to generate a report from two doctrine entities.

The fields to filter the report are optional. This is the code I've got now and it looks really bad to me. How can I improve this?

public function filter($accountNumber, $companyName, $branchName, $branchManager)
{
    $em = $this->managerRegistry->getManager();
    $whereClause = 'WHERE ';

    if (!empty($accountNumber)) {
        $whereClause .= ' u.accountNumber = :acc_number';
    }
    if (!empty($companyName)) {
        $whereClause .= ' AND um.companyName = :company_name';
    }
    if (!empty($branchName)) {
        $whereClause .= ' AND um.branchName = :branch_name';
    }
    if (!empty($branchManager)) {
        $whereClause .= ' AND um.branchAccountManager = :branch_manager';
    }

    $query = $em->createQuery(
        'SELECT u FROM CarbonUserBundle:User u
        JOIN CarbonUserBundle:UserMeta um WITh u.meta=um.id
       ' . $whereClause
    );

    if (!empty($accountNumber)) {
        $query->setParameter('acc_number', $accountNumber);
    }
    if (!empty($companyName)) {
        $query->setParameter('company_name', $companyName);
    }
    if (!empty($branchName)) {
        $query->setParameter('branch_name', $branchName);
    }
    if (!empty($branchManager)) {
        $query->setParameter('branch_manager', $branchManager);
    }

    return $query->getResult();
}

Answer 1

I would suggest using the QueryBuilder

$em = $this->managerRegistry->getManager();
$qb = $em->createQueryBuilder()
    ->select('u')
    ->from(User::class, 'u')
    ->join('u.meta', 'um'); // doctrine generates the join condition automatically


if (!empty($accountNumber)) {
    $qb
        ->andWhere('u.accountNumber = :acc_number')
        ->setParameter('acc_number', $accountNumber)
}
if (!empty($companyName)) {
    $qb
        ->andWhere('u.companyName = :company_name')
        ->setParameter('company_name', $companyName)
}

// and so on...

return $qb->getQuery()->getResult();