Doctrine 实体管理器中的 Symfony2 子查询 [英] Symfony2 subquery within Doctrine entity manager

查看:23
本文介绍了Doctrine 实体管理器中的 Symfony2 子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要执行这个查询:

SELECT * FROM (SELECT * FROM product WHERE car = 'large' ORDER BY onSale DESC) AS product_ordered GROUP BY type

在 Symfony2 中使用实体管理器.

In Symfony2 using the entity manager.

我的基本查询构建器是:

My basic query builder would be :

 $query = $em->getRepository('AutomotiveBundle:Car')
        ->createQueryBuilder('p')
        ->where('pr.car = ?1')
        ->andWhere('pr.status = 1')
        ->orderBy('pr.onSale', 'DESC')
        ->setParameter(1, $product->getName())
        ->groupBy('p.type')
        ->getQuery();

但我不知道如何在其中添加子查询.

But I cannot work out how to add in a subquery to this.

我尝试进行单独的查询并像这样加入它:

Ive tried making a separate query and joining it like:

 ->andWhere($query->expr()->in('pr.car = ?1',$query2->getQuery()));

但我明白了:

Call to undefined method DoctrineORMQuery::expr()

推荐答案

一个技巧是构建两个查询,然后使用 getDQL() 将第一个查询提供给第二个查询.

One trick is to build two queries and then use getDQL() to feed the first query into the second query.

例如,此查询返回不同的游戏 ID 列表:

For example, this query returns a distinct list of game ids:

    $qbGameId = $em->createQueryBuilder();

    $qbGameId->addSelect('distinct gameGameId.id');

    $qbGameId->from('ZaysoCoreBundle:Event','gameGameId');

    $qbGameId->leftJoin('gameGameId.teams','gameTeamGameId');

    if ($date1) $qbGameId->andWhere($qbGameId->expr()->gte('gameGameId.date',$date1));
    if ($date2) $qbGameId->andWhere($qbGameId->expr()->lte('gameGameId.date',$date2));

现在使用 dql 获取有关游戏本身的更多信息:

Now use the dql to get additional information about the games themselves:

    $qbGames = $em->createQueryBuilder();

    $qbGames->addSelect('game');
    $qbGames->addSelect('gameTeam');
    $qbGames->addSelect('team');
    $qbGames->addSelect('field');

    $qbGames->addSelect('gamePerson');
    $qbGames->addSelect('person');

    $qbGames->from('ZaysoCoreBundle:Event','game');

    $qbGames->leftJoin('game.teams',   'gameTeam');
    $qbGames->leftJoin('game.persons', 'gamePerson');
    $qbGames->leftJoin('game.field',   'field');

    $qbGames->leftJoin('gameTeam.team',     'team');
    $qbGames->leftJoin('gamePerson.person', 'person');

    // Here is where we feed in the dql
    $qbGames->andWhere($qbGames->expr()->in('game.id',$qbGameId->getDQL()));

有点长的例子,但我不想编辑掉一些东西,也许会破坏它.

Kind of a long example but i didn't want to edit out stuff and maybe break it.

这篇关于Doctrine 实体管理器中的 Symfony2 子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆