How to write this query in query builder?

Question

I have the following (exemplary) query:

SELECT *
FROM User u
LEFT JOIN section_user su ON (su.user_id = u.id AND su.section_id = 3)
WHERE section_id is null

I am trying to rebuild it in Symfony2 on a many-to-many relationship between users and sections with query builder but I am failing.

I thought it would look like this, which proves to be wrong:

$er->createQueryBuilder('u')
    ->leftJoin('u.sections', 's', 'WITH', 's = :section')
    ->where('s is null')
    ->setParameter('section', $section);

Help is very appreciated!

Khiva

Answer 1

First, WHERE section_id is null doesn't make much sense,
where does section_id comes from?
If it's from section_user, then it cannot be 3 and null at the same time.. ;)
If it's from User then it's a one to many relation instead of many to many...

Maybe a bit clarification on what you are trying to accomplish.

Basically it works like this: (left Doctrine, right Sql)

$er->createQueryBuilder('u')      -> SELECT * FROM user u
   ->leftJoin('u.sections', 'us') -> LEFT JOIN section_user su ON u.id = su.user_id
                                     LEFT JOIN section s ON s.id = su.section_id 
   ->where('us.id = :section')    -> WHERE s.id = :section
   ->setParameter('section', 4);

Doone

Sorry, this is not what I am looking for. It's a left join. Therefore the value will be NULL if it's not 3. – Khiva

Answer 2

# Receive Doctrine query builder
$qb = $this->getDoctrine()->getEntityManager()->createQueryBuilder(); 

$qb
   ->select('u')
   ->from('NameBundle:User', 'u')
   ->leftJoin('u.section_user', 'su', \Doctrine\ORM\Query\Expr\Join::WITH, 'su.user_id = u.id AND (su.section_id IS NULL OR su.section_id = :section)'); # Join condition
   ->setParameter('section', $section);

Such

This results in User has no association named section_userwhich seems like a logical error to me. – Khiva

Doctrine 2 mapping use camelcase nontation, so use u.sectionUser(look right field name in your entity) – Such

Answer 3

I think this is the same situation as in here But I see you require 2 conditions in leftJoin statement, try this (I am assuming the identity key in both the Entities are id )

$qb->select('u')
 ->from('NameBundle:User','u')
 ->leftJoin('u.sections','su','ON'
    $qb->expr()->andx($qb->expr()->eq('u.id', 'su.user_id'), $qb->expr()->eq('su.id', ':section'))
   )
 ->having('COUNT(su.id) = 0')
 ->groupBy('u.id')
 ->setParameter('section',$section_id);

This will get all the user who are not yet related to section of id $section_id

Edit: I just noticed your example query does not make any sense as it will always return nothing. But I guess what you want are the users that are not assigned to section $section_id

Nip

I will try this out as soon as I am back on the corresponding project. Just as a sidenote: My example query does make sense as I have thoroughly tested it and it actually does return the correct data. It is a LEFT JOIN. So every user with no correspondence to section id 3 will have a section_id of NULL. And those are exactly the ones I am looking for. – Khiva

Well I am not sure.. you are joining on su.section_id = 3 and just returning rows where section_id is null – Nip