SQL 如何在 Doctrine 2.0 中编写 UNION
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4155288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write UNION in Doctrine 2.0
提问by I?kuda
How to write this SQL query in Doctrine 2.0 (and fetch results)?
如何在 Doctrine 2.0 中编写此 SQL 查询（并获取结果）？
(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
采纳答案by I?kuda
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 (NotificationGroupand NotificationEvent) extending Notification:
然后是扩展Notification 的两个类（NotificationGroup和NotificationEvent）：
/**
* @Entity
*/
class NotificationGroup extends Notification {
//...
}
/**
* @Entity
*/
class NotificationEvent extends Notification {
//...
}
回答by beberlei
UNION is not supported in DQL, but you can still write a UNION query and use the Native Query capabilities to retrieve the data:
DQL 不支持 UNION，但您仍然可以编写 UNION 查询并使用本机查询功能来检索数据：
http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html
http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html
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.
但是，从您的示例来看，您似乎想为每个类继承使用某种形式的表，但尚不支持。如果您可以更改架构，则还有另一种形式的继承（联接表继承）可以工作。
A view would be another good solution, but then it depends on your database vendor if it also supports write operations or not.
视图将是另一个不错的解决方案，但是这取决于您的数据库供应商是否还支持写入操作。
回答by Milan Vidovic
$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();
