mysql - Doctrine 查询语言中的子查询

Question

我有一个 SQL 查询，我试图将其转换为 DQL。似乎 DQL 不喜欢在 FROM 子句中使用子查询。你能引导我走向正确的方向吗？

SQL:

select count(x.remote_addr), ipc.country
from
(
select distinct(remote_addr) from update_feature_requests ufr
where ufr.request_datetime BETWEEN '2015-05-14' AND '2015-05-15'
)
as x
join ip_geolocation_cache ipc ON ipc.ip_address = x.remote_addr
group by ipc.country;

DQL:

$dql = "select
            count(x.remoteAddr), ipc.country
            from
            (
              select distinct(remoteAddr) from " . UpdateFeatureRequest::class . " ufr
              where ufr.requestDatetime BETWEEN '2015-05-14' AND '2015-05-15'
            )
            as x
            join " . IpGeolocationCache::class . " ipc ON ipc.ipAddress = x.remoteAddr
            group by ipc.country";

Doctrine \ ORM \ Query \ QueryException HELP [Semantical Error] line 0, col 82 near '( ': Error: Class '(' is not defined.

Answer 1

Doctrine 仅以有限的方式支持子查询(如列、where in、exists 等)，不幸的是派生表(from 中的子查询)不是其中之一。您必须重构整个查询以使其成为 dql。

等效查询可能是(sql 版本):

select count(distinct ufr.remote_addr), ipc.country
from update_feature_requests ufr
join ip_geolocation_cache ipc ON ipc.ip_address = ufr.remote_addr
where ufr.request_datetime BETWEEN '2015-05-14' AND '2015-05-15'
group by ipc.country;

(它应该计算每个国家/地区的唯一地址，这就是我阅读您的原始查询的方式)

另一种可能性是使用 native query