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...

score 8 · Accepted Answer · answered Jun 03 '14 at 13:18

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();

Ummmm, actually, this is not really i was looking for (especially "Yeah, that's what functions are for" ) but clone is more closer what i was looking for so +1 for clone. — Canser Yanbakan, Jun 03 '14 at 13:26
Actually, it's looking like i will not find any better solutions, so thank you, accepting your answer as solution. — Canser Yanbakan, Jun 03 '14 at 13:31
@R.CanserYanbakan Well, I doubt if there is any other solution for this case. — VisioN, Jun 03 '14 at 13:39

score 0 · Answer 2 · answered Jun 15 '22 at 09:12

In Javascript-TypeORM you can clone the existing query builder as shown below.

let baseQuery=this.Repository.createQueryBuilder("user");
//Add conditions 
if(user_type==="admin"){ 
baseQuery.where("user.user_type = : user_type",{user_type:"admin"})
}
//Clone query builder
let count_query= new SelectQueryBuilder(baseQuery);
const count= count_query.select('COUNT(*)', 'count').getRawOne();
let sum_query= new SelectQueryBuilder(baseQuery);
const sum= sum_query.select('SUM(user.amount)', 'amount').getRawOne();

Doctrine QueryBuilder Re-Use Parts

2 Answers2

Linked