制定复杂的 Doctrine2 DQL 查询

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.

解决方案

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 DoctrineORMToolsPaginationPaginator;
$query = $DM->createQuery($DQL)
         ->setParameter('dateTo', date("Y-01-01 00:00:00"))
         ->setFirstResult(0)->setMaxResults(100);
$Persons = new Paginator($query, $fetchJoinCollection = true);