DQL Complicated query, nested query

Question

i am trying to pull some statistics, and i need to construct a DQL query, what i have so far would look something like this.

'SELECT COUNT(l) FROM AffiliateBundle:Lead l WHERE l.campaign = :campaign AND
l.customer.subscription IS NOT NULL AND
l.createdAt BETWEEN :date1 AND :date2'

Now, the issue is that i cannot do this: l.customer.subscription

I need to count all the leads that have a subscription associated for a specific campaign.

The relevant entity markup looks like this:

class Lead extends BaseEntity
{
    /**
     * @ORM\OneToOne(targetEntity="Customer", inversedBy="", cascade={"persist", "remove"})
     * @ORM\JoinColumn(name="customer_id", referencedColumnName="id", nullable=true, onDelete="CASCADE")
     */
    private $customer;
}

class Customer extends BaseEntity
{
   /**
    * @ORM\OneToOne(targetEntity="Subscription", inversedBy="", cascade={"persist"})
    * @ORM\JoinColumn(name="subscription_id", referencedColumnName="id", nullable=true, onDelete="SET NULL")
    */
    private $subscription;
}

Subscription is a separate entity, but that is not relevant.

Answer 1

You should do it with a JOIN

'SELECT COUNT(l) FROM AffiliateBundle:Lead l 
JOIN AffiliateBundle:Customer c
WHERE l.campaign = :campaign AND
c.subscription IS NOT NULL AND
l.createdAt BETWEEN :date1 AND :date2'