php 使用没有实体类的本机 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6860838/
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
Using native SQL query without entity class
提问by Ondrej Slinták
I need to create native SQL query with couple of unions and subqueries. It'll look approximately like this:
我需要使用几个联合和子查询创建本机 SQL 查询。它看起来大约是这样的：
SELECT res.id, COUNT(*) as count_ids
FROM (
SELECT a.id FROM ... a WHERE ... LIKE ('%:param%')
UNION ALL
SELECT b.id FROM ... b WHERE ... LIKE ('%:param%')
UNION ALL
...
) res
GROUP BY res.id
ORDER BY count_ids asc
Result won't match any Entity I use in my application. Is it possible to create ResultSetMappingwith "anonymous" object? Or is it, at least, possible to create an Entity that wouldn't create table next time I update schema, so I can map results to it?
结果与我在应用程序中使用的任何实体都不匹配。是否可以ResultSetMapping使用“匿名”对象创建？或者，至少可以创建一个在下次更新架构时不会创建表的实体，以便我可以将结果映射到它？
Or is there any other Doctrine-friendly way to deal with such query? Making changes to database isn't possible though, as I'm dealing with legacy stuff that cannot be touched. I'd also strongly prefer if I did everything on database side, not involving much of PHP in it.
或者有没有其他对 Doctrine 友好的方式来处理这样的查询？但是，不可能对数据库进行更改，因为我正在处理无法触及的遗留内容。如果我在数据库方面做所有事情，而不涉及太多 PHP，我也非常喜欢。
回答by Problematic
Do you have a particular need to map results to a domain object? If not, you could use the DBAL to make a plain old query, which will return an array, as detailed in the Symfony2 cookbookand the Doctrine DBAL documentation:
您是否特别需要将结果映射到域对象？如果没有，您可以使用 DBAL 进行简单的旧查询，它将返回一个数组，如Symfony2 说明书和Doctrine DBAL 文档中所述：
$conn = $this->container->get('database_connection');
$sql = 'SELECT res.id, COUNT(*)...';
$rows = $conn->query($sql);
回答by illia permiakov
Use addScalarResultmethod of ResultSetMapping
使用ResultSetMapping 的addScalarResult方法
$rsm = new ResultSetMapping();
$rsm->addScalarResult('cnt', 'cnt');
$rsm->addScalarResult('id', 'id');
$query = $this->em->createNativeQuery('SELECT count(*) AS cnt, id_column as id FROM your_table group by id', $rsm);
$result = $query->getResult();
var_dump($result);
Result array:
结果数组：
array (size=1)
0 =>
array (size=2)
'cnt' => int 1
'id' => int 15
