Symfony将DQL表达式与“NOT IN”连接在一起

Question

I'm using Symfony2 and i'm trying to get an array of languages not associated to a specified client.

I have a Client entity indicating the Client, ClientLanguage that has the following structure:

• id_menu_language PRIMARY KEY
• language the association with the Language entity
• client the association with the Client entity
• sequence tells the order the language should be shown (not used here)

and Language Entity.

To get an array of languages not associated to the client i want to proceed in the following way:

• Get the languages that the client has already associated (and I'm getting the correct DQL in the $clientLanguagesDQL variable)
• Retrieve a list of all the available languages
• Exclude from that list all the languages already associated to the client (by using NOT IN (...) ).

This is the function I wrote to accomlish that:

<?php

namespace AppBundle\Repository;

use AppBundle\Entity\Client;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;

class ClientRepository extends EntityRepository
{

    /**
     * @param $client Client to check
     * @return array
     */
    public function getLanguagesNotAssociatedToClient($client)
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        $clientLanguagesDQL = $qb
            ->select('lang')
            ->from('AppBundle:Language', 'lang')
            ->join('AppBundle:ClientLanguage', 'languages_assoc', Join::WITH, 'languages_assoc.language = lang')
            ->join('AppBundle:Client', 'client', Join::WITH, 'languages_assoc.client = client')
            ->where('client.idClient = :client_id')
            ->getQuery()
            ->getDQL();

        $languages = $qb->select('language')
            ->from('AppBundle:Language', 'language')
            ->where($qb->expr()->notIn('language', $clientLanguagesDQL))
            ->setParameter('client_id', $client->getIdClient())
            ->getQuery()
            ->getResult();

        return $languages;

    }
}

However, when I run this, Symfony complains about a : [Semantical Error] line 0, col 293 near 'lang INNER JOIN': Error: 'lang' is already defined. It also tells me that there is a QueryException, and it shows me the following query:

SELECT language 
FROM AppBundle:Language lang 
    INNER JOIN AppBundle:ClientLanguage languages_assoc 
        WITH languages_assoc.language = lang 
    INNER JOIN AppBundle:Client client 
        WITH languages_assoc.client = client, AppBundle:Language language WHERE language NOT IN(
    SELECT lang 
    FROM AppBundle:Language lang 
        INNER JOIN AppBundle:ClientLanguage languages_assoc 
            WITH languages_assoc.language = lang 
        INNER JOIN AppBundle:Client client 
            WITH languages_assoc.client = client 
    WHERE client.idClient = :client_id
)  

And this is definitely not what I want to do. Why there appeared to be two joins with AppBundle:ClientLanguage and AppBundle:Client? I use this association only in my first subquery.

If it can help, when I run this:

    $clientLanguagesDQL = $qb
        ->select('lang')
        ->from('AppBundle:Language', 'lang')
        ->join('AppBundle:ClientLanguage', 'languages_assoc', Join::WITH, 'languages_assoc.language = lang')
        ->join('AppBundle:Client', 'client', Join::WITH, 'languages_assoc.client = client')
        ->where('client.idClient = :client_id')
        ->getQuery()
        ->getDQL();

This is the returned DQL stored in $clientLanguageDQL:

SELECT lang 
FROM AppBundle:Language lang 
    INNER JOIN AppBundle:ClientLanguage languages_assoc 
        WITH languages_assoc.language = lang 
    INNER JOIN AppBundle:Client client 
        WITH languages_assoc.client = client 
WHERE client.idClient = :client_id

What's wrong with this query?

Answer 1

My mistake was very stupid, all I had to do is creating a new query builder for each query instead of reusing the first one:

    $qb1 = $this->getEntityManager()->createQueryBuilder();

    $clientLanguagesDQL = $qb1
        ->select('lang')
        ->from('AppBundle:Language', 'lang')
        ->join('AppBundle:ClientLanguage', 'languages_assoc', Join::WITH, 'languages_assoc.language = lang')
        ->join('AppBundle:Client', 'client', Join::WITH, 'languages_assoc.client = client')
        ->where('client.idClient = :client_id')
        ->getQuery()
        ->getDQL();

    $qb2 = $this->getEntityManager()->createQueryBuilder();
    $languages = $qb2->select('language')
        ->from('AppBundle:Language', 'language')
        ->where($qb2->expr()->notIn('language', $clientLanguagesDQL))
        ->setParameter('client_id', $client->getIdClient())
        ->getQuery()
        ->getResult();

    return $languages;