2
\$\begingroup\$

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();
}
\$\endgroup\$
4
\$\begingroup\$

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();
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.