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