学说QueryBuilder重用部分 [英] Doctrine QueryBuilder Re-Use Parts

问题描述

我想计算所有符合条件的字段，并使用学问查询构建器逐页获取它们。

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

第二部分使用相同的过滤器通过计算页面获取记录，只是更改了select和final部分（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...

推荐答案

是的，功能是这样的：

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

这篇关于学说QueryBuilder重用部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！