如何在symfony2中加入3个桌子

Question

我有3个表，我试图将其加入。我可以加入" paskureordorproductsstatus"表和"产品"表，而不是" putaway"表，但我不确定如何加入另一个...

paskureordorproductsstatus表

id  | product_id | 
------------------
10  | 1          |

产品表

id  | Name      | 
-----------------
1   | Acme      |

putaway表

id  | product_id | 
-----------------
100 | 1

在我的产品实体中，我与Putaway桌有一致的关系。Putaway表和产品表与产品ID一起连接。

/**
 * @ORMOneToMany(targetEntity="WICInventoryBundleEntityPutAway", mappedBy="product", fetch="EAGER")
 */
protected $putAway;
public function __construct()
{
    $this->putAway = new ArrayCollection();
}

在我的paskureorderproductsstatus自定义查询中，我能够加入产品表，我只是无法检索Putaway结果的集合。我该怎么做？

这是我的查询：

$query = $this->getEntityManager()
->createQuery('
SELECT      p, pr
FROM        WICPurchaseOrderBundleEntityPurchaseOrderProductsStatus p
JOIN        p.product pr
WHERE       p.inventoryLocation = :id
AND         p.account = :account_id
GROUP By    p.product
')
->setParameter('id', $id)
->setParameter('account_id', $account_id);

这是我的树枝模板：

{% for action in productActions %}
    <tr>
        <td>{{ action.product.id }}</td>
        <td>{{ action.product.sku }}</td>
        <td>{{ action.product.name }}</td>
        <td>{{ action.qty }}</td>
        <td>0</td>
    </tr>
    <tr>
        <td colspan="5">
            <div class="row-fluid">
                <div class="span2">
                    <table class="table table-striped table-bordered">
                        <thead>
                        <tr>
                            <th>Purchase Order</th>
                        </tr>
                        </thead>
                        <tbody>
                        <tr>
                            <td></td>
                        </tr>
                        </tbody>
                    </table>
                </div>
                <div class="span10">
                    <table class="table table-bordered" id="put_away_{{ action.product.id }}">
                        <thead>
                        <tr>
                            <th>Put Away Location</th>
                            <th>Quantity</th>
                            <th>Date</th>
                            <th>Entered By</th>
                        </tr>
                        </thead>
                        <tbody>
                        {% for putAway in action.product.putAway %}
                            <tr class="info">
                                <td>{{ putAway.inventoryLocation.name }}</td>
                                <td>{{ putAway.qty }}</td>
                                <td>{{ putAway.created|date("m/d/Y") }}</td>
                                <td>{{ putAway.createdBy.firstName }} {{ putAway.createdBy.lastName }}</td>
                            </tr>
                        {% endfor %}
                        </tbody>
                    </table>
                </div>
            </div>
        </td>
    </tr>
{% endfor %}

我有以下错误：

Key "putAway" for array with keys "id, archived_id_number, name, alternative_name, case_qty, description, sku, upc, unit_cost, retail_price, map_price, productWeightBig, productWeightSmall, productWeightUnit, productLength, productWidth, productHeight, productDimensionUnit, shippingWeightBig, shippingWeightSmall, shippingWeightUnitBig, shippingWeightUnitSmall, shippingLength, shippingWidth, shippingHeight, shippingDimensionUnit, path, created, updated, deletedAt" does not exist in 

Answer 1

我相信错误是要读取学说返回的对象。您正在做一个混合查询。看看：http：//docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html

无论如何，我会做的完全不同，您应该想到从哪个对象进行查询：paskureordorproductsstatus有一个产品，这是不错的，但是此加入具有多个" putaway"。我将首先查询收藏对象：

SELECT      pa, pr, pops
FROM        WICPurchaseOrderBundleEntityPutAway pa
LEFT JOIN        pa.product pr
LEFT JOIN        pr.PurchaseOrderProductsStatus pops
WHERE       p.inventoryLocation = :id AND p.account = :account_id

并非完全可以玩一点。树枝文件将需要修改。

仍然，如果DB查询的数量合理，我不会通过自定义查询进行操作，而只是使用标准Symfony，您编写的twig就应该在控制器操作中工作：

$id= the id that you used on your query
$account= the object whose id you used on your query
$em = $this->getDoctrine()->getManager();
$entities = $em->getRepository('WICPurchaseOrderBundle:PurchaseOrderProductsStatus')
->findAby(array('id'=>$id, 'account'=>$account));

$实体应该是树枝中的产品。但是，在此情况下，查询数是合理的。