原则 2 子查询

发布于 2024-11-05 20:36:32 字数 641 浏览 10 评论 0原文

我想使用查询生成器实现子查询，但我不理解语法。我正在处理一个位置表，其中的条目可以是城市、州或邮政编码，具体取决于设置的位置类型。我想获取某个州的所有位置，并删除任何城市类型且人口低于一定数量的位置。

$qb->select('l')
->from('Entity\Location', 'l')
->where('l.state = :state')
->setParameter('state', 'UT')
->andWhere('...don't know what to put here');

在 andWhere 中我基本上需要说的是

并且 id 不在（从 location_type = 1 且人口 < 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)');

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心病无药医 2024-11-12 20:36:32

在 Doctrine 的文档中，我发现了这一点：

// 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。我自己从未使用过它，但根据文档它应该看起来像这样。

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

我不能 100% 确定上面的示例是否正确，但请尝试一下。

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.

回复收藏 0 原文

~没有更多了~