使用NOT IN将SQL转换为Doctrine 2 Query Builder或DQL？ [英] Convert SQL to Doctrine 2 Query Builder or DQL by using NOT IN?

问题描述

如何将SQL转换为Doctrine 2 Query Builder或DQL？

How would I convert the SQL bellow to Doctrine 2 Query Builder or DQL?

SELECT tags.* 
FROM tags 
WHERE tags.id NOT IN (
                     SELECT tag_id AS totalTags 
                     FROM human_resource_tags 
                     WHERE human_resource_id=1)

标签实体如下：

标签实体

HumanResource实体如下：

HumanResource entity is as follows:

< a href =http://pastebin.com/gPccQ6pi =nofollow> HumanResource实体

基本上我想做的是选择所有的HumanResource实体的所有标签实体都没有这个HumanResource实体。

Basically what I want to do is to select all Tag entities for one HumanResource entity that that HumanResource entity does not have already.

我真的很努力，所以任何帮助都不胜感激。

I am really struggling here so any help is appreciated.

我使用的是Doctrine版本2.4.2。

I am using Doctrine version 2.4.2.

================= ================================================== =====

==========================================================================

所有的冰雹给FuzzyTree指针：）

All hail to FuzzyTree for pointers :)

我稍微修改它，它的作用就像一个魅力:)
所以这样就可以得到所有不被添加到
HumanResource实体的人力资源实体的标签实体：）

I have slightly modified it and it works like a charm :) So this will get you all Tag entities for particular HumanResource entity that are not added to HumanResource entity yet :)

这是解决方案：

$q = $this->createQueryBuilder('t')
      ->where('t.name LIKE :name')
      ->andWhere('NOT EXISTS (
                    SELECT h
                    FROM HRAPIBundle:HumanResource h 
                    WHERE h.id = ' . $humanResource->getId() .
                    'AND h MEMBER of t.human_resources
                )')
      ->setParameter('name', "%".$query."%")
      ->getQuery();

推荐答案

您可以使用不存在和会员

$qb->select("t")
    ->from('HardCoreMore\HRAPIBundle\Entity\Tag', 't')
    ->where('NOT EXISTS (
        SELECT 1 
        FROM HardCoreMore\HRAPIBundle\Entity\HumanResource h 
        WHERE h.id = 1 
        AND h MEMBER of t.human_resources
      )');

这篇关于使用NOT IN将SQL转换为Doctrine 2 Query Builder或DQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！