Subqueries in Doctrine Query Language

Question

I have an SQL query that I was trying to translate into DQL. Seems like DQL does not like to use subquery in a FROM clause. Can you please guide me in the riqht direction?

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.

jkavalik jkavalik · Accepted Answer · 2015-05-18T10:52:49

Doctrine does support subqueries only in limited way (as column, where in, exists etc.) and unfortunatelly derived table (subquery in from) is not one of them. You have to restructure entire query to make it into dql.

The equivalent query might be something along (sql version):

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;

(It should count unique addresses per counry which is how I read your original query)

Other possibility is to use native query

Subqueries in Doctrine Query Language

1 Answers