unbreak unbreak -5 years ago 376
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()
'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'

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?


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 Source

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 = 

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

    return $result;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download