学说DQL QueryBuilder的扣除实体，其中关系存在 [英] Doctrine DQL QueryBuilder Excluding Entities where relationship exists

问题描述

我有两个entites的

I have two entites

Order
  oneToMany:
    statuses:
       targetEntity: \Status
       mappedBy: order

Status
  manyToOne:
    order:
      targetEntity: \Order
      inversedBy: status
  fields:
    code:
      type:integer

正如你可以看到 - 一个订单可以同时拥有一个以上的状态（如已发货，状态。已付款等'）

As you can see - one order can have more that one status at a time (statuses like 'has been shipped', 'has been paid for etc.').

我想写迎接我的所有订单不具有的6状态的查询。
我发现很难让我的头一轮这一点。

I want to write a query that fetch all my Orders that do NOT have a status of 6. I'm finding it hard to get my head round this.

假设我有三个订单。所有有'1'（新）状态，而这些订单之一还拥有'6'（审查）状态。我只想检索这两个命令不具有的6状态。

Assume that I have three orders. The all have a status of '1' (new), and ONE of those orders additionally has a status of '6' (under review). I want to retrieve only the two orders that do NOT have a status of 6.

编写查询，像这样..

Writing the query like so..

$qb->select('o')
    ->from('MyOrderBundle:Order', 'o')
    ->innerJoin('o.statuses', 'st')
    ->where(
    $qb->expr()->not(
        $qb->expr()->eq('st.code', 6)
        )
);

这生成的SQL排除的6状态行，但仍包括在结果集中的顺序（因为状态行，其中code = 1匹配不等于6的状态）。我需要能够说排除从我的结果集，它有一个像关联{}东西任何实体。是否有一个DQL关键字，可以帮助我在这里？

Excludes the status row of 6 from the SQL generated, but still includes the order in the result set (because the status row where the code=1 matches the condition of not being equal to 6). I need to be able to say "Exclude from my result set any entity that has an association like {something}". Is there a DQL keyword that can help me here?

-Cross张贴在教义用户组，将更新的答案在这两个地方，如果我找到一个解决方案。

-Cross posting on the doctrine user group and will update answers In both place if I find a solution.

推荐答案

我摸索出一个办法做到这一点最终使用子查询，的这感觉有点哈克的，但我想不出任何其他方式（如6,7,8,9是我想从我的结果集排除状态）。

I worked out a way to do this eventually using a subselect, which feels a bit hacky but I couldn't think of any other way (where 6,7,8,9 are statuses that I want to exclude from my result set).

$qb->select('o')
->from('MyOrderBundle:Order', 'o')
->innerJoin('o.statuses', 'st')
->where(
    $qb->expr()->notIn(
        $qb2->select('DISTINCT(o2.id)')
        ->from('MyOrderBundle:Status', 'stat')
        ->innerJoin('stat.order','o2','WITH', $qb2->expr()->in('stat.code', (6,7,8,9)))->getDQL()
    )
);

这篇关于学说DQL QueryBuilder的扣除实体，其中关系存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！