I need to do a sub-select in DQL

Question

I have this query:

    $query = $this->createQueryBuilder('l')
        ->select('l')
        ->leftJoin('l.processedLeads', 'pl')
        ->where('pl.company <> :company')
        ->andWhere('pl.company IS NULL')
        ->setParameters(array('company' => $company))
        ->getQuery();

But i need it formed like the following:

SELECT * FROM leads WHERE NOT IN 
( SELECT * FROM processed_leads WHERE lead = :lead AND company = :company)

Can i do a sub-select in a join where 2 parameters of the join (lead_id and company) do not exist?

As in, only select the leads that do not exist in processedLeads with the specific company?

Answer 1

I'm not familiar with the QueryBuilder, but I would recommend something like this in DQL:

$query = $em->createQuery('SELECT l FROM Project\model\leads l WHERE l NOT IN
    (SELECT p.lead FROM Project\model\processed_leads p WHERE p.lead = ? AND company = ?)')
    ->setParameters(array($lead, $company))
    ->getResult;

Hopefully, this helps you. I would use the NOT INstatement. In case the sub query returns a null value, there is just no lead in the processed_leads for this company and you'll get all leads - which should be ok in this case.