How to select all users who are registered in all the same events as current user

Question

I'm using Symfony 4. I have 3 entities , User, Event, EventRegistration.

class User {
 /**
 * @ORM\OneToMany(targetEntity="App\Entity\EventRegistration", mappedBy="user", cascade={"persist","remove"})
 */
public $eventsRegistrations;

}

class Event{
/**
 * @ORM\OneToMany(targetEntity="App\Entity\EventRegistration ", mappedBy="event")
 * @ORM\JoinColumn(nullable=false)
 */
private $registrations;
}

class EventRegistration {
/**
 * @ORM\ManyToOne(targetEntity="App\Entity\Event", inversedBy="registrations")
 * @ORM\JoinColumn(nullable=false)
 */
private $event;

/**
 * @ORM\ManyToOne(targetEntity="App\Entity\User", inversedBy="eventsRegistrations")
 * @ORM\JoinColumn(nullable=false)
 */
private $user;
}

I'd like to know how to select all users who are registered in all the same events as current user using one query not two as I did.

Right now I have created two queries:

EventRegistrationRepository:

// select all events ids in where the current user has been registered
public function myEventsIds($user)
{
    $arrayEventssIds = array();
    $qb = $this->createQueryBuilder('e')
        ->where('e.user = :user')
        ->setParameter('user', $user)
        ->getQuery()
        ->getResult();
    foreach ($qb as $registration) {
        array_push($arrayEventssIds , $registration->getEvent()->getId());
    }

    return $registration;
}

then in UserRepository , select all users having registrations envent ids in arrayEventsIds of current user:

public function usersRegistredInTheSameEvents($user, $arrayEventsIds)
{
  //arrayEventsIds contains the events ids selected in the query above

    $qb = $this->createQueryBuilder('u')
        ->innerJoin('u.eventsRegistrations', 'er')
        ->where('er.event IN (:arrayEventsIds)')
        ->andWhere('er.user != :user')
        ->setParameter('arrayEventsIds', $arrayEventsIds)
        ->setParameter('user', $user)
        ->addGroupBy('u.id');

    return $qb->getQuery()->getResult();
}}
 

How to combine combine these two queries in just one ?

Answer 1

This is DQL version of the query you need (for user repository);

$query = $this->_em->createQuery(
        "select u from App:EventRegistration er 
         left join App:User u with er.user = u.id
         where er.user != :user_id and 
         er.event in (select identity(er2.event) from App:EventRegistration er2 
         where er2.user = :user_id)")
->setParameter('user_id', $user->getId());

$users = $query->getResult();

Hope it's help.