Doctrine query with join table

Question

I'm trying to create this SQL query in a doctrine QueryBuilder

SELECT count(DISTINCT d.id)
FROM `discount` d
JOIN `order_discount_relationships` od ON od.discount_id = d.id
JOIN `orders` o ON o.id = od.order_id
WHERE o.status = 3

My two entity are those:

/**
 * @ORM\Table
 */
class Discount
{
    // ...
}

And

/**
 * @ORM\Table
 */
class Order
{
    // ...

    /**
     * @var Discount[]|ArrayCollection
     * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Discount")
     * @ORM\OrderBy({"id" = "ASC"})
     * @ORM\JoinTable(name="order_discount_relationships",
     *      joinColumns={@ORM\JoinColumn(name="order_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="discount_id", referencedColumnName="id")}
     * )
     */
    private $discounts;

    // ...
}

I'd like to count Discount which are already used (in a order with status 3)

$qb = $this->createQueryBuilder('d');
$qb->select('count(DISTINCT d)')
    ->join('AppBundle:Order', 'o', 'WITH', 'o.discountCodes = d')
    ->where('o.status = :status')
    ->setParameter('status', Order::STATUS_SUCCESS)
;

But I got this error:

Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected

Do you know how do that? Thanks for helping

score 2 · Accepted Answer · answered Apr 02 '17 at 06:42

2

Try to use member of

$qb->select('count(DISTINCT d)')
    ->join('AppBundle:Order', 'o', 'WITH', 'd member of o.discounts')
    ->where('o.status = :status')
    ->setParameter('status', Order::STATUS_SUCCESS)
;

answered Apr 02 '17 at 06:42

Max P.

5,579
2
13
32

Doctrine query with join table

1 Answers1