query builder, one to many where many is empty

Question

I have an entity Category, and this category has a recursive relationship with itself where each category can be a parent to several other categories. The relationship looks like this:

  /**
 * @var parent
 * @ORM\ManyToOne(targetEntity="Category")
 * @ORM\JoinColumn(referencedColumnName="id", onDelete="CASCADE") 
 */
private $parent;

/**
 * @ORM\OneToMany(targetEntity="Category", mappedBy="parent")

 */
private $children;

I need to make a query builder query that selects all categories that are either children(have a parent) Or the categories that have no parent and also have no children (in other words all categories except the parents that have children) I can't seem to be able to do it. Please help.

Answer 1

You need next DQL query:

$query = 'SELECT c FROM AcmeBundle:Category c LEFT JOIN c.parent p LEFT JOIN c.children ch WHERE p IS NOT NULL OR (ch IS NULL AND p IS NULL)';

If you need QueryBuilder sequence for this query you can use next code:

$qb = $em->createQueryBuilder();
$query = $qb
    ->select('c')
    ->from('AcmeBundle:Category', 'c')
    ->leftJoin('c.parent', 'p')
    ->leftJoin('c.children', 'ch')
    ->where($qb->expr()->orX(
        $qb->expr()->isNotNull('p'),
        $qb->expr()->andX(
            $qb->expr()->isNull('ch'),
            $qb->expr()->isNull('p'),
        )
    ))
    ->getQuery();