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.

In leftJoin method you have to specify the entity as a first parameter. You are joining the same table, so you have to join Folder::class, not folder.subFolders. Hopefully, helpfull :) — Vladislav, Aug 08 '17 at 08:34
thanks @Vladislav, but actually the join works, since I've been able to test this by just doing the SQL. The answer by tmas is exactly what I needed. Thanks for trying though! — Pjetr, Aug 08 '17 at 08:41

score 2 · Accepted Answer · answered Aug 08 '17 at 08:36

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

restricting Doctrine annotated joins

1 Answers1