Formulating complicated Doctrine2 DQL query

Question

Given I have an instance of Event ($event) that has many AttendancePerson, I need to get all of the AttendancePerson objects belonging to $event where the AttendancePerson.person attended more than one event that has a calendar_id matching $event->calendar_id and where the AttendancePerson.event.dateTo ends in the previous year.

The schema minus irrelevant column names:

event_attendance_person
    - id
    - event_id
    - person_id
event
    - id
    - calendar_id
    - dateTo
person
    - id
event_calendar
    - id

The purpose is to find old members of any given event. Any event attendance person who attended an event sharing the same calendar more than once in the previous year is an "old member" of the event.

I read through many relevant questions. None of them helped. Thank you to anyone who can help on this.

Answer 1

Assuming that (person_id,event_id) is unique in event_attendance_person.

1 get all persons belonging to given event
2 for each person get all their other events having the same calendar id and some end date
3 group by person id
4 filter by persons having more than 1 other event

---

In SQL (updated column names to match example data)

select p.id
from event_event e
join event_attendance_person eap on eap.eventId = e.id
join person p on eap.personId = p.id
join event_attendance_person eap2 on eap2.personId = p.id
join event_event e2 on e2.id = eap2.eventId
where e.id = 2230
and e2.id <> 2230
and e2.calendar = e.calendar
and e2.dateTo between '2013-01-01' and '2014-12-31'
group by p.id
having count(e2.id) > 1

Using QueryBuilder

$qb->select('p')
    ->from('MyBundleNameSpace\Entity\Event', 'e')
    ->innerJoin('e.person','p')
    ->innerJoin('p.event','e2')
    ->where('IDENTITY(e) = :event_id')
    ->andWhere('IDENTITY(e2) != :event_id')
    ->andWhere('IDENTITY(e2.calendar) = IDENTITY(e.calendar)')
    ->andWhere('e2.dateTo BETWEEN :start AND :end')
    ->groupBy('p')
    ->having('count(e2.id) > 1')
    ->setParameter('event_id',$event->getId())
    ->setParameter('start','2012-01-1')
    ->setParameter('end','2013-12-31');

Answer 2

For your specific requirement of having persons from event_attendance_person who have attended more than 1 event in past year of same calendar to the calendar of provided event so in plain Mysql query you can join your tables get the count of distinct events per person id i.e COUNT(DISTINCT e.id) and a conditional count for the provided event id lets say i want to get the persons who have attended event with id 2228 so for this suing case in count you can do so COUNT(CASE WHEN e.id = 2228 THEN 1 END) this will give you the count 1 for the person who attended this event and 0 for persons who misses that event, reason for this conditional count is because i am not using where filter for event id i have overcome this one by using having clause and for the past year a simple where clause is WHERE e.dateTo < DATE_FORMAT(NOW() ,'%Y-01-01 00:00:00')

SELECT p.*,COUNT(DISTINCT e.id) total_events,
COUNT(CASE WHEN e.id = 2228 THEN 1 END) count_event
FROM `event_attendance_person` p
JOIN `event_event` e ON(p.`eventId` = e.id )
JOIN `event_calendar` c ON(e.`calendar` =c.`id`)
WHERE e.`dateTo` < DATE_FORMAT(NOW() ,'%Y-01-01 00:00:00')
GROUP BY p.`personId`
HAVING count_event = 1 AND total_events > 1
ORDER BY total_events DESC

You can test this query on your Mysql server

---

Now here comes the doctrine part you can replicate above query in DQL as

$DQL="SELECT p,COUNT(DISTINCT e.id) AS total_events,
 COUNT(CASE WHEN e.id = 2228 THEN 1 END) AS count_event
 FROM NamespaceYourBundle:EventAttendencePerson p
 JOIN p.events e
 JOIN e.calandar c
 WHERE e.dateTo < :dateTo
 GROUP BY p.personId
 HAVING total_events = 1 AND count_event >1
 ORDER BY c DESC
";

For above DQL i assume you have already mapped your relations among your entities like for above query below are the mandatory relations which must exist in your entities

• JOIN p.events e Now p is alias for entityNamespaceYourBundle:EventAttendencePerson, EventAttendencePerson entity must point to your Event entity so that the on ON(p.eventId = e.id ) part can be achieved

• JOIN e.calandar c Now Event entity must point to your Calendar entity in order to achieve ON(e.calendar =c.id)

---

And then you can run your DQL as below by using doctrine's paginator class

use Doctrine\ORM\Tools\Pagination\Paginator;
$query = $DM->createQuery($DQL)
         ->setParameter('dateTo', date("Y-01-01 00:00:00"))
         ->setFirstResult(0)->setMaxResults(100);
$Persons = new Paginator($query, $fetchJoinCollection = true);

Answer 3

Why does event_attendance_person need to be it's own object? Wouldn't a many to many relationship with a join table like event_person suffice?

Anycase assuming you have your doctrine entities set up correctly, you'd probably want to split this up into two separate DQL queries, the first query is where you get the list of people who attended the event and then you pass the id list of those people into your second query which does a where person_id IN (person_ids) and WHERE event_id != event.id AND calendar.id = event.calendar.id AND event.dateTo > calculated_date

the DQL for those two separate queries should be easy enough to write.