doctrine2 dql member of on collection subentities

Question

Using dql I tried to check if a entity is member of a collections subentity

entites:

       product
       customer

       customer.orders (collection)
       customer.orders.products (collection, type: product)
       customer.cancellations.productContainers (collection)
       customer.cancellations.productContainers.product (entity, type: product)

A customer has multiple orders. An order has multiple products. A customer has multiple cancellations. A cancellations has multiple productContainers. A productContainer has a product.

problem

I want to get all ordered products which are not cancelled.

       $qb->select('c, d, p')
       ->from('XyzBundle:Customer', 'c')
       ->leftJoin('c.orders', 'co')
       ->leftJoin('co.products', 'cop')
       ->leftJoin('c.cancellation', 'ca')
       ->leftJoin('ca.productContainers', 'cap')
       ->leftJoin('cap.product', 'capp')
       ->andWhere('cop NOT MEMBER OF capp')

however this does not work, because ca.productContainers is the collection field and not its supentity ca.productContainers.product. thus I get the following error:

       CRITICAL - Uncaught PHP Exception Doctrine\ORM\Query\QueryException: 
       "[Semantical Error] line 0, col 414 near 'product': Error: Invalid PathExpression. 
       Must be a CollectionValuedAssociationField." at 
       /vagrant/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php line 4 9

Any suggestions how to solve this?

Paniagua

i solved it using two queries, but do not remember exactly. – Paniagua

Answer 1

I want to get all ordered products which are not cancelled.

Assumptions

• You want these products along with customer name.
• An order has multiple products
• A productContainer has a product not multiple products.

-

By taking care of above assumptions you can write following DQL to get your products.

SELECT c,cop.product /* cop.product => refers to property which has an association with product entity */
FROM XyzBundle:Customer c
    LEFT JOIN c.orders co
    LEFT JOIN co.products cop
    LEFT JOIN c.cancellation ca
    LEFT ca.productContainers cap
    LEFT cap.product capp WITH capp.product = cop.product
WHERE capp.product IS NULL

Following your query builder you can add a WITH part which will behave as another condition for JOIN clause like ON a=b AND (WITH) c=d So for WITH capp.product = cop.product it joins cancellation productContainers product with additional clause and associate a record of product. So to filter out cancelled products you can write it as cap.product i.e ( cancellation productContainers) should be null.

If you need only products information not other details you can use the simpler version

SELECT p
FROM XyzBundle:Product p
    LEFT JOIN XyzBundle:ProductContainers pc WITH p.id = pc.product
WHERE pc.product IS NULL

Above DQL will generate following equivalent SQL

SELECT p.*
FROM product p
    LEFT JOIN product_containers pc ON p.id = pc.product_id
WHERE pc.product_id IS NULL

Another way to rewrite your DQL as

SELECT p,c
FROM XyzBundle:Product p
    LEFT JOIN XyzBundle:ProductContainers pc WITH p.id = pc.product
    INNER JOIN XyzBundle:OrderProducts op WITH p.id = op.product
    INNER JOIN op.order o /* property in XyzBundle:OrderProducts which has a reference to order entity */
    INNER JOIN o.customer c /* property in order entity which has a reference to customer entity */
WHERE pc.product IS NULL

Ciceronian