Using alias in sub queries with PHQL

I am using this query in raw sql and it returns correct result:

<?php 
$query = "SELECT 
FROM_UNIXTIME(timestamp,'%b %d %Y') as day, 
( 
SELECT COUNT(id)*10 
FROM screenshots a1 
WHERE user_id=24 
AND day = FROM_UNIXTIME(timestamp,'%b %d %Y') 
) as Stefan 
FROM screenshots b 
WHERE b.timestamp BETWEEN (UNIX_TIMESTAMP()-604800) AND UNIX_TIMESTAMP() 
GROUP BY day";

However when I try to translate it to PHQL it returns: Column 'day' doesn't belong to any of the selected models (1), when preparing. Final PHQL looks something like this:

<?php
$final_query=$this->modelsManager->createQuery("SELECT FROM_UNIXTIME(timestamp,'%b %d %Y') as day, ( SELECT COUNT(id)*10 FROM Screenshots a1 WHERE user_id=24 AND day = FROM_UNIXTIME(timestamp,'%b %d %Y') ) as Stefan FROM Screenshots b WHERE b.timestamp BETWEEN (UNIX_TIMESTAMP()-604800) AND UNIX_TIMESTAMP() GROUP BY day");

Sure I can do raw query, but why it should be like this?



950

Cmon guys, anybody?

You haven't used the aliased table name for columns (probably)

SELECT
    FROM_UNIXTIME(timestamp,'%b %d %Y') as day,
    (SELECT COUNT(a1.id)*10 FROM Screenshots a1 WHERE a1.user_id=24 AND a1.day = FROM_UNIXTIME(a1.timestamp,'%b %d %Y') ) as Stefan
FROM Screenshots b
WHERE b.timestamp BETWEEN (UNIX_TIMESTAMP()-604800) AND UNIX_TIMESTAMP() GROUP BY b.day


950

Unfortunatelly it is not this. I did try it by any possible way I know... it always trow an error on PHQL and almost ever work on regular sql.