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?