sqluniondoctrine-orm

How to write UNION in Doctrine 2.0


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

Solution

  • 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 {
        //...
    }