Executing custom mysql query and "Error: Invalid PathExpression. Must be a StateFieldPathExpression"

Question

im 'newbie' in Symfony :) Im trying to execute this sql:

SELECT cat.name, ri.category_id,AVG(ri.value) as avarage FROM company c
JOIN reviews r ON r.company_id = c.id
JOIN reviews_items ri ON ri.review_id = r.id
JOIN category cat ON ri.category_id = cat.id
WHERE c.id = 1
GROUP BY ri.category_id

in Symfony2 doctrine by:

$cats = $this->getEntityManager()
        ->createQuery(
            'SELECT cat.name, ri.category, AVG(ri.value),r as avarage 
            FROM DiligesDiligesBundle:Company c
            JOIN c.reviews r
            JOIN r.reviews ri
            JOIN ri.category cat
            WHERE c.id = 1
            GROUP BY ri.category'
        )->getResult();

But it gives me an error:

[Semantical Error] line 0, col 20 near 'category, AVG(ri.value),r': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Don't know why, can anyone help?

EDIT:

Ive changed SELECTed items toto:

'SELECT c,r,ri,cat.name,AVG(ri.value) as avarage

and I dont get error but I get wrong item (only one - it should be 4 of them). I need select just selected columns of tables, not all of them.

PS. Sorry if my language (english) is not so good :D

np87 np87 · Accepted Answer · 2014-01-13T11:21:12

Not sure you can cast custom SQL directly inside the query builder this way.

The method I use is as follow :

public Function myCustomQuery() {
    $sql = 
    <<<EOF
        MY_SQL_CODE_HERE
    EOF
    ;

    $myQuery = $this->getEntityManager()->getConnection()->prepare($sql);
    $myQuery->execute();
    $result = $myQuery->fetchAll();

    return $result;
}

Executing custom mysql query and "Error: Invalid PathExpression. Must be a StateFieldPathExpression"

3 Answers