如何在Doctrine 2.0中编写UNION
如何在Doctrine 2.0中编写这个SQL查询(并获取结果)?如何在Doctrine 2.0中编写UNION
(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
嗯,我发现也许是最好的解决办法:
/**
* @Entity
* @InheritanceType("JOINED")
* @DiscriminatorColumn(name="discr", type="string")
* @DiscriminatorMap({"group" = "NotificationGroup", "event" = "NotificationEvent"})
*/
class Notification {
// ...
}
然后两班(NotificationGroup和NotificationEvent)延伸通知:在不支持
/**
* @Entity
*/
class NotificationGroup extends Notification {
//...
}
/**
* @Entity
*/
class NotificationEvent extends Notification {
//...
}
UNION
在Doctrine,s中不受支持。讨论here。
是的,我知道。是否有任何解决方法,要做到这一点? – 2010-11-11 14:23:24
作为一个中间解决方案,您可以使用VIEW来获得UNION支持。 – 2010-11-11 14:24:28
UNION DQL,但仍然可以编写UNION查询并使用本机查询功能来检索数据:
http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html
但是从你的例子似乎要使用某种形式的每类继承, 它还不支持表。如果你可以改变你的模式,还有另一种形式的继承,(连接表继承)。
视图将是另一个很好的解决方案,但后来它取决于你的数据库供应商,如果它也支持写入操作与否。
链接中断,这里是好的:http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html – 2014-02-26 11:53:45
$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();
该解决方案的解释如何? – ihsan 2014-12-25 01:04:42
@ihsan你可以从基类库('Notification')中选择,并且你可以得到子类型的所有对象('NotificationGroup','NotificationEvent',甚至只是'Notification',如果你还没有声明它是抽象的)。如果你只需要某些类型,那么你可以在WHERE中使用'INSTANCE OF'运算符。 – 2016-02-04 12:44:43