主义2子查询 [英] Doctrine 2 subquery
问题描述
我想使用查询构建器实现子查询,但我不了解语法。我正在处理一个位置表,其中有可能是城市,州或邮政编码的条目,具体取决于所设置的位置类型。我想让所有处于某种状态的所有地点都可以取出任何城市类型,而且人口低于一定数量。
$ qb-> select('l')
- > from('Entity\Location','l')
- > where('l.state = ')
- > setParameter('state','UT')
- > andWhere('...不知道该放在哪里)
在我基本需要说的
< blockquote>
,其中id不在(从location_type = 1和population< 1000的位置选择id)
更新:我可以使用直接DQL进行此操作,但使用查询构建器可以很好的了解如何使用它。
$ qb-> andWhere('l.id NOT IN(SELECT l2.id FROM Entity\Location AS l2 WHERE l2.location_type = 1 AND l2.population< 1000)' );
在Doctrine的文档中我发现: p>
//示例 - $ qb-> expr() - > in('u.id',array(1,2 ,3))
//确保你不要使用类似于$ qb-> expr() - > in('value',array('stringvalue')的东西,因为这将导致Doctrine抛出异常
//相反,在('value',array('?1')中使用$ qb-> expr() - >并将参数绑定到?1(见上文)
($ x,$ y)中的public函数; //返回Expr\Func实例
//示例 - $ qb-> expr() - > notIn('u.id','2')
public function notIn ($ x,$ y); //返回Expr\Func实例
应该可以在此函数中放置一个子查询。我从来没有使用过,但是根据文档应该是这样的。
$ qb-> select('l ')
- > from('Entity\Location','l')
- >其中('l.state =:state')
- > setParameter('状态','UT')
- > andWhere($ qb-> expr() - > notIn('u.id',
$ qb-> select('l2.id ')
- > from('Entity\Location','l2')
- >其中(l2.location_type =?1 AND l2.population<?2)
- > setParameters(array(1 => 1,2,> 1000))
));
我不是100%肯定上面的例子是正确的,但试一试。 p>
I want to implement a subquery using the query builder but I'm not understanding the syntax. I'm dealing with a locations table that has entries that can be cities, states or zip codes depending on the location type set. I want to get all locations that are in a certain state and take out any that are city type and have a population below a certain amount.
$qb->select('l')
->from('Entity\Location', 'l')
->where('l.state = :state')
->setParameter('state', 'UT')
->andWhere('...don't know what to put here');
In the andWhere I basically need to say
and where id not in (select id from location where location_type = 1 and population < 1000)
Update: I was able to do this with straight DQL but it would be nice to see how to do it using the query builder.
$qb->andWhere('l.id NOT IN (SELECT l2.id FROM Entity\Location AS l2 WHERE l2.location_type = 1 AND l2.population < 1000)');
In the documentation of Doctrine I found this:
// Example - $qb->expr()->in('u.id', array(1, 2, 3))
// Make sure that you do NOT use something similar to $qb->expr()->in('value', array('stringvalue')) as this will cause Doctrine to throw an Exception.
// Instead, use $qb->expr()->in('value', array('?1')) and bind your parameter to ?1 (see section above)
public function in($x, $y); // Returns Expr\Func instance
// Example - $qb->expr()->notIn('u.id', '2')
public function notIn($x, $y); // Returns Expr\Func instance
It should be possible to put a subquery in this function. I never used it myself, but according to the documentation it should look like this.
$qb->select('l')
->from('Entity\Location', 'l')
->where('l.state = :state')
->setParameter('state', 'UT')
->andWhere($qb->expr()->notIn('u.id',
$qb->select('l2.id')
->from('Entity\Location', 'l2')
->where(l2.location_type = ?1 AND l2.population < ?2)
->setParameters(array(1=> 1, 2 => 1000))
));
I'm not 100% sure the example above is correct, but give it a try.
这篇关于主义2子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!