如何在Doctrine 2.0中写UNION [英] How to write UNION in Doctrine 2.0

问题描述

 （SELECT'group'AS type， CONCAT（u.firstname，，u.surname）作为fullname，g.name AS subject，user_id，who_id，group_id AS subject_id，创建FROM group_notification JOIN users u ON（who_id = u.id）JOIN groups g ON（group_id = g.id））
 UNION 
（SELECT'event'AS类型，CONCAT（u.firstname，，u.surname）作为全名，e.name AS主题，user_id，who_id，event_id AS subject_id，创建FROM event_notification JOIN用户u ON（who_id = u.id）JOIN事件e ON（event_id = e.id））
 ORDER BY创建
  

解决方案

嗯，我发现也许最好的解决方案：

  / ** 
 * @Entity 
 * @InheritanceType（JOINED）
 * @DiscriminatorColumn（name =discr，type =string） 
 * @DiscriminatorMap（{group=NotificationGroup，event=NotificationEvent}）
 * / 
类通知{
 // ... 
} 
  

然后两个类（ NotificationGroup 和 NotificationEvent ）扩展通知：

  / ** 
 * @Entity 
 * / 
 class NotificationGroup extends Notification {
 // ... 
} 
 
 / ** 
 * @Entity 
 * / 
 class NotificationEvent extends Notification {
 // ... 
} 
  

How to write this SQL query in Doctrine 2.0 (and fetch results)?

(SELECT 'group' AS type, CONCAT(u.firstname, " ", u.surname) as fullname, g.name AS subject, user_id, who_id, group_id AS subject_id, created FROM group_notification JOIN users u ON(who_id = u.id) JOIN groups g ON(group_id = g.id))
   UNION 
(SELECT 'event' AS type, CONCAT(u.firstname, " ", u.surname) as fullname, e.name AS subject, user_id, who_id, event_id AS subject_id, created FROM event_notification JOIN users u ON(who_id = u.id) JOIN events e ON(event_id = e.id))
 ORDER BY created

解决方案

Well, I found maybe the best solution:

/**
 * @Entity
 * @InheritanceType("JOINED")
 * @DiscriminatorColumn(name="discr", type="string")
 * @DiscriminatorMap({"group" = "NotificationGroup", "event" = "NotificationEvent"})
 */
class Notification {
   // ...
}

And then two classes (NotificationGroup and NotificationEvent) extending Notification:

/**
 * @Entity
 */
class NotificationGroup extends Notification {
    //...
}

/**
 * @Entity
 */
class NotificationEvent extends Notification {
    //...
}

这篇关于如何在Doctrine 2.0中写UNION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！