使用没有实体类的本机 SQL 查询

2022-01-03 00:00:00 php symfony doctrine-orm

我需要使用几个联合和子查询创建本机 SQL 查询.大致如下所示:

I need to create native SQL query with couple of unions and subqueries. It'll look approximately like this:

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

结果与我在应用程序中使用的任何实体都不匹配.是否可以使用匿名"对象创建 ResultSetMapping?或者，至少可以创建一个在下次更新架构时不会创建表的实体，以便我可以将结果映射到它?

Result won't match any Entity I use in my application. Is it possible to create ResultSetMapping with "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?

或者有没有其他对 Doctrine 友好的方式来处理这样的查询?但是，不可能对数据库进行更改，因为我正在处理无法触及的遗留内容.如果我在数据库方面做所有事情，而不涉及太多 PHP，我也非常喜欢.

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.

推荐答案

您是否有将结果映射到域对象的特殊需求?如果没有，您可以使用 DBAL 进行简单的旧查询，该查询将返回一个数组，详情请参见 Symfony2 手册 和 Doctrine DBAL 文档:

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 cookbook and the Doctrine DBAL documentation:

$conn = $this->container->get('database_connection');
$sql = 'SELECT res.id, COUNT(*)...';
$rows = $conn->query($sql);

相关文章