How to build a Docterine Inner Join Query in Symfony

Question

Im working on a Symfony project and I want to create Doctrine Query for this SQL.

USER table : columns - NICK_NAME

REVIEWS table: columns - USER_ID , REVIEW, CREATED_AT

Thank you

SELECT
`USER`.NICK_NAME,
REVIEWS.REVIEW,
REVIEWS.CREATED_AT
FROM
REVIEWS
INNER JOIN `USER` ON REVIEWS.USER_ID = `USER`.ID
WHERE
REVIEWS.MOVIE_ID = 625
GROUP BY
REVIEWS.USER_ID

I tried something like this

    $q = Doctrine_Query::create()
            ->select("u.NICK_NAME,r.REVIEW,r.CREATED_AT")
            ->from('REVIEWS r')
            ->innerJoin('`USER` ON REVIEWS.USER_ID = `USER`.ID')
            ->where('REVIEWS.MOVIE_ID = 625')
            ->groupBy('REVIEWS.USER_ID');

and got this:

500 | Internal Server Error | Doctrine_Exception Couldn't find class `USER`

Answer 1

Without using complex DQL in Symfony you can use simple SQL. Try this function in the controller where you want to run the DQL.

 function getUserReviews($params) {
        $query = "SELECT REVIEWS.REVIEW, `USER`.NICK_NAME, REVIEWS.CREATED_AT FROM REVIEWS INNER JOIN `USER` ON REVIEWS.USER_ID = `USER`.ID WHERE REVIEWS.MOVIE_ID ='".$params."'";
        return Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);
    }