Doctrine subquery on FROM statement, Symfony 3

Question

New to Doctrine and trying to convert the MySQL query below to queryBuilder.

SELECT COUNT(t2.user_id) FROM(
    SELECT t2.* FROM logins t2
        WHERE t2.login_time >= DATE_SUB(NOW(),INTERVAL 5 HOUR)
        LIMIT 10
    ) t2
WHERE t2.is_success = 1

I've seen a few examples of subquery on the WHERE clause here on SO and attempted to adapt it as per below, but that does not really work.

        $sub = $this->createQueryBuilder('l')
        ->select('l')
        ->where('l.loginTime >= :date
            and l.userId = :user_id')
        ->setParameters( $parameters )
        ->getDQL();

    $qb = $this->createQueryBuilder('a')
        ->select('count(a.id)')
        ->from( $sub, 'a' )
        ->where('a.isSuccess = 0'); 
    return $qb->getQuery()->getSingleScalarResult();

Thanks

https://stackoverflow.com/questions/9831985/selecting-from-subquery-in-dql — colburton, Jul 08 '17 at 17:49

Viraj Amarasinghe · Answer 1 · 2017-07-08T17:55:19.283

0

Use this query,

$date = new Doctrine_Expression('DATE_SUB(NOW() , INTERVAL 5 HOUR)');
$query
->select('count(t2.userId)')
->from('Login', 't2')
->where('t2.loginTime >= :date')
->andWhere('t2.isSuccess = 1')
->setParameter('date',$date);
->setMaxResults(10);

edited Jul 08 '17 at 17:55

answered Jul 08 '17 at 17:44

Viraj Amarasinghe

911
10
20

I changed the script – Viraj Amarasinghe Jul 08 '17 at 17:59
thanks. I am getting an error on the from clause -> class Login not defined – BernardA Jul 08 '17 at 18:02
:) nice, then fine. – Viraj Amarasinghe Jul 08 '17 at 18:03
ok solved the not defined issue, but the query does not produce the same results as the mySQL query. It should result in a number between 0-10, as this is the limit on the sub query, but it is not. – BernardA Jul 08 '17 at 18:34

score 0 · Answer 2 · answered Jul 09 '17 at 16:02

As @colburton pointed out, it seems that Doctine isn't able to handle subqueries as such. Check this post.

So, I am posting the possible solutions I've found and hopefully somebody will correct me or improve on it.

Alternative 1 - Though I did not verify it, it seems that Native SQL would be able to handle it. Downside is that it seems quite complex, particularly when there are more sensible alternatives.

Alternative 2 - Run plain old mySQL query. Given that the result is a scalar that would be just fine. This is how I set it up.

UsedBundle/Repository/LoginRepository.php

public function countAllFailedMysqlAction()
{
    $ulink = new \PDO("mysql:host=$hostname;dbname=$database;charset=utf8", $username, $password);
    $sql = "SELECT COUNT(t2.user_id) FROM(
        SELECT t2.* FROM logins t2
        WHERE t2.login_time >= DATE_SUB(NOW(),INTERVAL 5 HOUR)
        LIMIT 10
        ) t2
        WHERE t2.is_success = 0";
        $stmt = $ulink->query($sql); 
        return $stmt->fetchColumn();
}

Alternative 3 - In this particular instance, one could just run the inner or subquery using regular Entity, return an array and iterate through it to get the result.

UsedBundle/Repository/LoginRepository.php

public function countAllFailedAction()
{
    $date = new \DateTime();
    $date->modify('-24 hour');

    $qb = $this->createQueryBuilder('l')
        ->select('l.id,l.isSuccess')
        ->where('l.loginTime >= :date')
        ->setParameter('date', $date)
        ->setMaxResults(10);
    $logins = $qb->getQuery()->getArrayResult();

    $ct_failed = 0;
    foreach( $logins as $login ){
        if($login['isSuccess'] == false ){ $ct_failed++;}
    }
    return $ct_failed;        
}

Doctrine subquery on FROM statement, Symfony 3

2 Answers2