Convert SQL to Doctrine 2 Query Builder or DQL by

Question

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)

Tag entity is as follows:

Tag entity

HumanResource entity is as follows:

HumanResource entity

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.

I am using Doctrine version 2.4.2.

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

All hail to FuzzyTree for pointers :)

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 :)

SO THIS IS SOLUTION:

$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();

Answer 1

Select your $hmEntitythat you don't want then use the follow code:

 $em = $this->getDoctrine()->getManager();
 $result= $em->getRepository('HRAPIBundle:Tag')->findByHumanResource(!$hmEntity);

Answer 2

You can achieve this using NOT EXISTS and MEMBER OF

$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
      )');