如何在我的教义搜索中查找实体的自我引用实体？

Question

查看用户实体时，您可以看到它是一个自我引用的关系。用户有很多朋友自己是用户。如何在用户朋友的朋友中进行完全相同的搜索？（在此查询中，我只是在寻找填补这些条件的用户，但我想在$用户朋友中执行它）

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select( 'USER', '' )
    ->from( 'EntityUser',  '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 )
        )
    );

这是用户实体（仅出于清晰的属性）：

class User
{
    /**
     * @var integer
     *
     * @ORMColumn(name="id", type="integer", nullable=false, unique=true)
     * @ORMId
     * @ORMGeneratedValue(strategy="IDENTITY")
     */
    private $id;
    /**
     * @var integer
     *
     * @ORMColumn(name="fb_id", type="bigint", nullable=false, unique=true)
     */
    private $fb_id;
    /**
     * @var string
     *
     * @ORMColumn(name="firstname", type="string", length=100, nullable=false, unique=false)
     */
    private $first_name;
    /**
     * @var string
     *
     * @ORMColumn(name="lastname", type="string", length=100, nullable=true, unique=false)
     */
    private $last_name;
    /**
     * @var string
     *
     * @ORMColumn(name="email", type="string", length=255, nullable=true, unique=true)
     */
    private $email;
    /**
     * @var integer
     *
     * @ORMColumn(name="notation", type="integer", nullable=true, unique=true)
     */
    private $notation;
    /**
     * Bidirectional - Many users have Many favorite comments (OWNING SIDE)
     *
     * @var DoctrineCommonCollectionsCollection
     *
     * @ORMManyToMany(targetEntity="EntityCategory", inversedBy="userInterests")
     */
    private $interests;
    /**
     * @var DoctrineCommonCollectionsCollection
     *
     * @ORMManyToMany(targetEntity="EntityUser", cascade={"persist"})
     * @ORMJoinTable(name="friends",
     *      joinColumns={@ORMJoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORMJoinColumn(name="friend_user_id", referencedColumnName="id")}
     *      )
     **/
    private $friends;
    /**
     * @var DoctrineCommonCollectionsCollection
     *
     * @ORMOneToMany(targetEntity="EntityRequest", mappedBy="user", cascade={"remove"}, orphanRemoval=true)
     * @ORMJoinColumn(nullable=true)
     */
    private $requests;
    /**
     * @var DoctrineCommonCollectionsCollection
     *
     * @ORMOneToMany(targetEntity="EntityRequest", mappedBy="friend", cascade={"remove"}, orphanRemoval=true)
     * @ORMJoinColumn(nullable=true)
     */
    private $notifications;
    /**
     * Bidirectional - Many users have notified they want to go to different events (OWNING SIDE)
     *
     * @var DoctrineCommonCollectionsCollection
     *
     * @ORMManyToMany(targetEntity="EntityEvent", inversedBy="userNotified", cascade={"persist"})
     */
    private $eventNotified;
    /**
     * @var integer
     *
     * @ORMColumn(name="age", type="integer", length=3, nullable=true, unique=false)
     */
    private $age;
    /**
     * @var string
     *
     * @ORMColumn(name="description", type="text", nullable=true, unique=false)
     */
    private $description;
    /**
     * @var string
     *
     * @ORMColumn(name="picture", type="string", length=300, nullable=true, unique=false)
     */
    private $picture;
    /**
     * @var string
     *
     * @ORMColumn(name="genre", type="string", length=10, nullable=true, unique=false)
     */
    private $genre;
    /**
     * @var boolean
     *
     * @ORMColumn(name="isregistered", type="boolean", length=1, nullable=false, unique=false)
     */
    private $registered;
    /**
     * @var string
     *
     * @ORMColumn(name="latitude", type="decimal", length=64, precision=25, scale=20, nullable=true, unique=false)
     */
    private $latitude;
    /**
     * @var string
     *
     * @ORMColumn(name="longitude", type="decimal", length=64, precision=25, scale=20, nullable=true, unique=false)
     */
    private $longitude;
    /**
     * @var EntitySecurity_Key
     *
     * @ORMOneToOne(targetEntity="EntitySecurity_Key", cascade={"persist","remove"}, orphanRemoval=true)
     * @ORMJoinColumns({
     *   @ORMJoinColumn(name="private_key_id", referencedColumnName="id", unique=true, onDelete="SET NULL")
     * })
     */
    private $private_key;
    /**
     * @var boolean
     *
     * @ORMColumn(name="isavailable", type="boolean", length=1, nullable=false, unique=false)
     */
    private $available = 0;
    /**
     * Constructor
     */
    public function __construct()
    {
        $this->friends = new DoctrineCommonCollectionsArrayCollection();
    }

谢谢

Answer 1

您可以使用WITH关键字向JOIN添加其他条件。

$qb->select( 'USER' )
   ->from( 'EntityUser',  '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 )
       )
   )

编辑以显示将用户ID放在哪里。