unbreak unbreak - 5 months ago 45
SQL Question

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

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

Answer

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;
}