Doctrine实体经理中的Symfony2子查询 [英] Symfony2 subquery within Doctrine entity manager
问题描述
我需要执行这个查询:
SELECT * FROM(SELECT * FROM product WHERE car ='large'ORDER BY onSale DESC)AS product_ordered GROUP BY type
在Symfony2中使用实体管理器。
我的基本查询构建器将是:
$ query = $ em-> getRepository '汽车Bundle:Car')
- > createQueryBuilder('p')
- > where('pr.car =?1')
- > andWhere('pr.status = 1')
- > orderBy('pr.onSale','DESC')
- > setParameter(1,$ product-> getName())
- > groupBy('p.type')
- > getQuery();
但是我无法解决如何添加一个子查询。
Ive尝试单独查询并加入,如:
- > andWhere($ query-> expr() - > in('pr.car =?1',$ query2-> getQuery()));
但是我得到:
调用未定义的方法Doctrine\ORM\Query :: expr()
一个窍门是构建两个查询,然后使用getDQL()将第一个查询提供到第二个查询。
例如,该查询返回一个独特的游戏ID列表:
$ 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获取有关游戏本身的其他信息:
$ 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');
//这里是我们在($ game_id)$ qbGameId中的dql
$ qbGames->和Where($ qbGames-> expr() - > - > getDQL()));
一个很长的例子,但我不想编辑出来的东西,也许打破它。 / p>
I need to perform this query:
SELECT * FROM (SELECT * FROM product WHERE car = 'large' ORDER BY onSale DESC) AS product_ordered GROUP BY type
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()));
But I get:
Call to undefined method Doctrine\ORM\Query::expr()
One trick is to build two queries and then use getDQL() to feed the first query into the second query.
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));
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屋!