How to look for an entity's self referenced entity in my Doctrine search?

Question

When looking at the User entity, you can see its a manytomany self referencing relation. A user has many friends who are users themselves. How do I make this exact same search in within a user's friends? (in this query, I'm only looking for users who fill those conditions, but I would like to perform it in a $user friends)

$qb = $this->getEntityManager()->createQueryBuilder();

$qb->select( 'USER', '' )
    ->from( 'Entity\User',  'USER' )
    ->innerJoin( 'USER.eventNotified' )
    ->where( 
        $qb->expr()->andX(
            $qb->expr()->gt( 'USER.latitude', ':minLat' ),
            $qb->expr()->lt( 'USER.latitude', ':maxLat' ),
            $qb->expr()->gt( 'USER.longitude', ':minLng' ),
            $qb->expr()->lt( 'USER.longitude', ':maxLng' ),
            $qb->expr()->eq( 'USER.available', 1 )
        )
    );

Here is the User entity (only properties for clarity):

class User
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false, unique=true)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var integer
     *
     * @ORM\Column(name="fb_id", type="bigint", nullable=false, unique=true)
     */
    private $fb_id;

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

    /**
     * @var string
     *
     * @ORM\Column(name="lastname", type="string", length=100, nullable=true, unique=false)
     */
    private $last_name;

    /**
     * @var string
     *
     * @ORM\Column(name="email", type="string", length=255, nullable=true, unique=true)
     */
    private $email;

    /**
     * @var integer
     *
     * @ORM\Column(name="notation", type="integer", nullable=true, unique=true)
     */
    private $notation;

    /**
     * Bidirectional - Many users have Many favorite comments (OWNING SIDE)
     *
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Entity\Category", inversedBy="userInterests")
     */
    private $interests;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Entity\User", cascade={"persist"})
     * @ORM\JoinTable(name="friends",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="friend_user_id", referencedColumnName="id")}
     *      )
     **/
    private $friends;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="Entity\Request", mappedBy="user", cascade={"remove"}, orphanRemoval=true)
     * @ORM\JoinColumn(nullable=true)
     */
    private $requests;

    /**
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\OneToMany(targetEntity="Entity\Request", mappedBy="friend", cascade={"remove"}, orphanRemoval=true)
     * @ORM\JoinColumn(nullable=true)
     */
    private $notifications;

    /**
     * Bidirectional - Many users have notified they want to go to different events (OWNING SIDE)
     *
     * @var \Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Entity\Event", inversedBy="userNotified", cascade={"persist"})
     */
    private $eventNotified;

    /**
     * @var integer
     *
     * @ORM\Column(name="age", type="integer", length=3, nullable=true, unique=false)
     */
    private $age;

    /**
     * @var string
     *
     * @ORM\Column(name="description", type="text", nullable=true, unique=false)
     */
    private $description;

    /**
     * @var string
     *
     * @ORM\Column(name="picture", type="string", length=300, nullable=true, unique=false)
     */
    private $picture;

    /**
     * @var string
     *
     * @ORM\Column(name="genre", type="string", length=10, nullable=true, unique=false)
     */
    private $genre;

    /**
     * @var boolean
     *
     * @ORM\Column(name="isregistered", type="boolean", length=1, nullable=false, unique=false)
     */
    private $registered;

    /**
     * @var string
     *
     * @ORM\Column(name="latitude", type="decimal", length=64, precision=25, scale=20, nullable=true, unique=false)
     */
    private $latitude;

    /**
     * @var string
     *
     * @ORM\Column(name="longitude", type="decimal", length=64, precision=25, scale=20, nullable=true, unique=false)
     */
    private $longitude;

    /**
     * @var \Entity\Security_Key
     *
     * @ORM\OneToOne(targetEntity="Entity\Security_Key", cascade={"persist","remove"}, orphanRemoval=true)
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="private_key_id", referencedColumnName="id", unique=true, onDelete="SET NULL")
     * })
     */
    private $private_key;

    /**
     * @var boolean
     *
     * @ORM\Column(name="isavailable", type="boolean", length=1, nullable=false, unique=false)
     */
    private $available = 0;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->friends = new \Doctrine\Common\Collections\ArrayCollection();
    }

Thank you

Answer 1

You can add additional conditions to a join, by using the WITH keyword.

$qb->select( 'USER' )
   ->from( 'Entity\User',  'USER' )
   ->where( 'USER.id = :userId' )
   ->leftJoin('USER.friends', 'f', 'WITH', $qb->expr()->andX(
        $qb->expr()->gt( 'f.latitude', ':minLat' ),
        $qb->expr()->lt( 'f.latitude', ':maxLat' ),
        $qb->expr()->gt( 'f.longitude', ':minLng' ),
        $qb->expr()->lt( 'f.longitude', ':maxLng' ),
        $qb->expr()->eq( 'f.available', 1 )
       )
   )

Edited to show where to put the userId.