restricting Doctrine annotated joins

Question

I have a class Folder with these fields:

/**
 * @var int
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=100)
 */
private $name;

/**
 * @var User
 *
 * @ORM\ManyToOne(targetEntity="UserBundle\Entity\User")
 * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
 */
private $user;

/**
 * @var Folder
 *
 * @ORM\ManyToOne(targetEntity="FileBundle\Entity\Folder", inversedBy="subFolders", cascade={"persist"})
 * @ORM\JoinColumn(name="parent_folder_id", referencedColumnName="id")
 *
 */
private $parentFolder;

/**
 * @var ArrayCollection
 *
 * @ORM\OneToMany(targetEntity="FileBundle\Entity\Folder", mappedBy="parentFolder")
 * @ORM\OrderBy("name")
 */
private $subFolders;

I have a function in my Repository that selects folders where the user is the current user or Null.
I would like to have the same rules for my subfolders. I tried this, which didn't work, obviously.

$qb = $this->createQueryBuilder('folder');
$qb
    ->leftJoin(
        'folder.subFolders',
        'folders',
        'with',
        $qb->expr()->orX(
            $qb->expr()->isNull('folders.user'),
            $qb->expr()->eq('folders.user', ':user')
        )
    )
    ->add('where', $qb->expr()->andX(
        $qb->expr()->isNull('folder.parentFolder'),
        $qb->expr()->orX(
            $qb->expr()->isNull('folder.user'),
            $qb->expr()->eq('folder.user', ':user')
        )
    ))
    ->orderBy('folder.name')
    ->setParameter('user', $user->getId());

return $qb;

Well, the query works, but this doesn't affect my subfolders, because I still get subfolders created by another user.

If you could nudge me in the correct direction, any help will be greatly appreciated.

Answer 1

add ->addSelect('folders') to the query If you don't do the addSelect the relation won't be initialized in your entity. So if you later on do getSubFolders() the relation won't be initialized and it will just do a regular select without your requirements from the join. With the addSelect all the folders will be eager loaded with your requirements.

$qb = $this->createQueryBuilder('folder');
$qb
    ->addSelect('folders')
    ->leftJoin(
        'folder.subFolders',
        'folders',
        'with',
        $qb->expr()->orX(
            $qb->expr()->isNull('folders.user'),
            $qb->expr()->eq('folders.user', ':user')
        )
    )
    ->add('where', $qb->expr()->andX(
        $qb->expr()->isNull('folder.parentFolder'),
        $qb->expr()->orX(
            $qb->expr()->isNull('folder.user'),
            $qb->expr()->eq('folder.user', ':user')
        )
    ))
    ->orderBy('folder.name')
    ->setParameter('user', $user->getId());