Doctrine QueryBuilder Re-Use Parts

Question

I want to count all fields that fits my conditions and get them page by page with doctrine query builder.

I'm generating the query depends my filter fields.

First section is counting the records so i can calculate the pages.

$qb = $em->createQueryBuilder();

$qb
    ->select('COUNT(m.id)')
    ->from('CSMediaBundle:MediaItem', 'm')
    ->where(
        $qb->expr()->eq('m.media', $media->getId())
    );

$filters = $request->request->get('filter');

if(!empty($filters['size'])) {
    foreach($filters['size'] as $key => $value) {
        if(!empty($value)) {
            $qb->andWhere(
                $qb->expr()->eq('m.'.$key, ':'.$key)
            )->setParameter($key, $value);
        }
    }
}

if(!empty($filters['sliders'])) {

    $qb
        ->leftJoin('CSSliderBundle:SliderItem', 's', 'ON', 'm.id = s.media_id')
        ->andWhere(
            $qb->expr()->in('s.sliders', $filters['sliders'])
        );
}

$media_count = $qb->getQuery()->getSingleScalarResult();

Second section is getting records by calculated page using same filters, just changing the select and final parts (getSingleScalarResult to getResult)

I wonder if is there any way to just change the select and the result parts so i would not use the filters again and again...

Answer 1

Yeah, that's what functions are for:

function filter($qb, $filters) {
    if (!empty($filters['size'])) {
        foreach($filters['size'] as $key => $value) {
            if (!empty($value)) {
                $qb->andWhere(
                    $qb->expr()->eq('m.'.$key, ':'.$key)
                )->setParameter($key, $value);
            }
        }
    }

    if (!empty($filters['sliders'])) {
        $qb
            ->leftJoin('CSSliderBundle:SliderItem', 's', 'ON', 'm.id = s.media_id')
            ->andWhere(
                $qb->expr()->in('s.sliders', $filters['sliders'])
            );
    }

    return $qb;
}

$filters = $request->request->get('filter');

// count
$qb = $em->createQueryBuilder();
$qb
    ->select('COUNT(m.id)')
    ->from('CSMediaBundle:MediaItem', 'm')
    ->where(
        $qb->expr()->eq('m.media', $media->getId())
    );

$media_count = filter($qb, $filters)->getQuery()->getSingleScalarResult();

// entities
$qb = $em->createQueryBuilder();
$qb
    ->select('m')
    ->from('CSMediaBundle:MediaItem', 'm')
    ->where(
        $qb->expr()->eq('m.media', $media->getId())
    );

$media_entities = filter($qb, $filters)->getQuery()->getResult();

Another way is to clone the query builder object:

$qb = $em->createQueryBuilder();
$qb->from('CSMediaBundle:MediaItem', 'm')
    ->where(
        $qb->expr()->eq('m.media', $media->getId())
    );

$filters = $request->request->get('filter');
if (!empty($filters['size'])) {
    foreach($filters['size'] as $key => $value) {
        if (!empty($value)) {
            $qb->andWhere(
                $qb->expr()->eq('m.'.$key, ':'.$key)
            )->setParameter($key, $value);
        }
    }
}

if (!empty($filters['sliders'])) {
    $qb
        ->leftJoin('CSSliderBundle:SliderItem', 's', 'ON', 'm.id = s.media_id')
        ->andWhere(
            $qb->expr()->in('s.sliders', $filters['sliders'])
        );
}

$qb2 = clone $qb;

$qb->select('COUNT(m.id)')
$media_count = $qb->getQuery()->getSingleScalarResult();

$qb2->select('m')
$media_entities = $qb2->getQuery()->getResult();