制定复杂的Doctrine2 DQL查询 [英] Formulating complicated Doctrine2 DQL query

问题描述

鉴于我有一个包含许多AttendancePerson的Event($ event)实例，我需要获取属于$ event的所有AttendancePerson对象，其中AttendancePerson.person参加了一个以上的calendar_id匹配$ event- > calendar_id以及AttendancePerson.event.dateTo在上一年结束的位置.

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.

推荐答案

对于您的特定要求，是要求event_attendance_person中的在过去一年中参加同一日历的多个事件到提供的日历的人都是这样您可以加入表的MySQL查询获取每个人ID(即COUNT(DISTINCT e.id))的不同事件的计数，并且提供的事件ID的条件计数可以说我想获取ID为2228的参加过事件的人员，因此对于此诉讼在计数的情况下，您可以这样做COUNT(CASE WHEN e.id = 2228 THEN 1 END)，这将为参加该事件的人提供1的计数，而为错过该事件的人提供0的计数，此条件计数的原因是因为我没有使用事件ID的where过滤器i已经通过使用Have子句克服了这一问题，并且在过去一年中，一个简单的where子句是WHERE e.dateTo < DATE_FORMAT(NOW() ,'%Y-01-01 00:00:00')

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

您可以在Mysql服务器上测试此查询

You can test this query on your Mysql server

现在这是您可以在DQL中将上述查询复制为以下内容的学说部分

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
";

对于上面的DQL，我假设您已经在实体之间映射了关系，例如下面的查询是您实体中必须存在的强制性关系

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

• 加入p.events e 现在，p是实体NamespaceYourBundle:EventAttendencePerson的别名，EventAttendencePerson实体必须指向您的Event实体，以便可以在ON(p.eventId = e.id )部分上达到

• 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

加入e.calandar c 现在，Event实体必须指向您的Calendar实体才能实现ON(e.calendar =c.id)

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

然后您可以通过使用doctrine的分页器类来按以下方式运行DQL

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);

这篇关于制定复杂的Doctrine2 DQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！