douyi5822 2015-06-23 01:29
浏览 53
已采纳

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

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?

展开全部

  • 写回答

1条回答 默认 最新

  • douji6667 2015-06-23 23:02
    关注

    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;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 MATLAB解决问题
  • ¥20 哪位专业人士知道这是什么原件吗?哪里可以买到?
  • ¥15 关于#c##的问题:treenode反序列化后获取不到上一节点和下一节点,Fullpath和Handle报错
  • ¥15 一部手机能否同时用不同的app进入不同的直播间?
  • ¥15 没输出运行不了什么问题
  • ¥20 输入import torch显示Intel MKL FATAL ERROR,系统驱动1%,: Cannot load mkl_intel_thread.dll.
  • ¥15 点云密度大则包围盒小
  • ¥15 nginx使用nfs进行服务器的数据共享
  • ¥15 C#i编程中so-ir-192编码的字符集转码UTF8问题
  • ¥15 51嵌入式入门按键小项目