How to write UNION in Doctrine 2.0

Question

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

Iškuda · Accepted Answer · 2010-11-11T16:45:43.797

14

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

edited Nov 11 '10 at 16:45

answered Nov 11 '10 at 15:30

Iškuda

635
1
7
13

29

And how is the explanation to this solution? – ihsan Dec 25 '14 at 01:04
@ihsan you select from the base class repository (`Notification`) and you get all the objects of that are of subclass type (`NotificationGroup`, `NotificationEvent`, and even just `Notification` if you haven't declared it abstract). If you need only certain types, then you can use the `INSTANCE OF` operator in WHERE. – Radu C Feb 04 '16 at 12:44
Would this approach in fact create a UNION SELECT query? – Daniel Böttner Mar 05 '20 at 08:53
1

@Daniel Böttner, no. It will use table joins. https://www.doctrine-project.org/projects/doctrine-orm/en/2.7/reference/inheritance-mapping.html#class-table-inheritance – Jekis Jun 25 '20 at 13:33

score 12 · Answer 2 · edited Aug 14 '23 at 09:03

UNION is not supported in DQL, but you can still write a UNION query and use the Native Query capabilities to retrieve the data:

https://www.doctrine-project.org/projects/doctrine-orm/en/2.16/reference/native-sql.html#native-sql

However from your example it seems you want to use some form of table per class inheritance, which is not yet supported. There is another form of inheritance, (Joined Table Inheritance) that works though, if you can change your schema.

https://www.doctrine-project.org/projects/doctrine-orm/en/2.16/reference/inheritance-mapping.html#class-table-inheritance

A view would be another good solution, but then it depends on your database vendor if it also supports write operations or not.

@FrancescoCasula also broken :) – Allart Oct 11 '21 at 06:53 — Allart, Oct 11 '21 at 06:53

score 2 · Answer 3 · answered Jun 29 '16 at 11:59

$connection = $em->getConnection();
$query = $connection->prepare("SELECT field1, field2 FROM table1 
                                UNION
                                SELECT field3, field4 FROM table2 
                                UNION 
                                SELECT field5, field6 FROM table3
                                ");
$query->execute();
$result = $query->fetchAll();

score 2 · Answer 4 · edited Dec 31 '17 at 06:32

2

UNION is not supported in Doctrine, s. the discussion here.

edited Dec 31 '17 at 06:32

automatix

14,018
26
105
230

answered Nov 11 '10 at 14:22

Haim Evgi

123,187
45
217
223

2

As an interm solution you could use a VIEW to get UNION support. – Haim Evgi Nov 11 '10 at 14:24

How to write UNION in Doctrine 2.0

4 Answers4

Linked