Doctrine 2 DQL Subquery

Question

I'm trying to use a Subquery in the FROM Block via Doctrine 2.3 DQL.

Everytime im run this, getting following error

[Semantical Error] line 0, col 839 near '(SELECT ': Error: Class '(' is not defined.

This is my current DQL Code

$query = $this->getEntityManager()->createQuery('
SELECT 
        taS2.netDueDate,
        SUM(taS2.amountInThousands)                     AS SumAmountInThousands,
        SUM(taS2.netDAO1)                                       AS SumnetDAO1,
        SUM(taS2.netDAO2)                                               AS SumnetDAO2,
        SUM(taS2.netDAO3)                                           AS SumnetDAO3,
        SUM(taS2.netDAO4)                                               AS SumnetDAO4,
        SUM(taS2.netDueDate * taS2.amountInThousands)   AS SumNetDueDateInThousands,
        SUM(taS2.critical)  
FROM 
    (SELECT 
        ta.id,
        ta.documentDate,
        SUBSTRING(CONCAT(ta.netDueDate, \'\'), 9, 2) AS netDueDate,
        ta.discountRate, 
        ta.amountInThousands, 
        ta.netDAO1, 
        ta.netDAO2, 
        ta.netDAO3, 
        ta.netDAO4,
        ta.netDueDate,
        1 AS counter,
        CASE 
             WHEN (ta.clearingDate > ta.netDueDate) AND 
                     SUBSTRING(CONCAT(ta.clearingDate, \'\'), 6, 2) <> SUBSTRING(CONCAT(ta.netDueDate, \'\'), 6, 2)
             THEN ta.amountInThousands ELSE 0
        END AS critical
    FROM 
        AcmeBundle:Transaction ta
    WHERE
        ta.discountRate = 0) taS2

GROUP BY taS2.netDueDate
ORDER BY SUM(taS2.amountInThousands) DESC;
);

Has anybody a idea how i do this properly?

ferdynator · Answer 1 · 2013-08-29T08:49:55.450

-2

Your query looks more like plain SQL with some DQL features. You can execute SQL-queries with $this->getEntityManager()->createNativeQuery();

Doctrine 2 DQL Subquery

1 Answers1