CODEWITHSUNDEEP
phpmysqlsymfonydql
Mayura Wijewickrama
Mayura Wijewickrama

Reputation: 749

How to build a Docterine Inner Join Query in Symfony

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`

Upvotes: 4

Views: 1461

Answers (2)

user4611583
user4611583

Reputation:

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

Upvotes: 2

Ashish Awasthi
Ashish Awasthi

Reputation: 1502

You Should Specify Entity name not table name in DQL like this YourBundleName:EntityName

Use it like this:

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

Alternative Solution if above solution doesn’t work:

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

If that doesn't work, use Inner join in following manner:

->InnerJoin('YourBundleName:Entity', '<alias>', Expr\Join::ON, $qb->expr()->eq('IDENTITY(<alias.<clummn_name>)', '<comapring_column>'))

inner Join from Doctrine2 Documentation:

Example - 

$qb->innerJoin('u.Group', 'g', Expr\Join::WITH, qb->expr()->eq('u.status_id', '?1'))

$qb->innerJoin('u.Group', 'g', 'WITH', 'u.status = ?1')

$qb->innerJoin('u.Group', 'g', 'WITH', 'u.status = ?1', 'g.id')

innerJoin Method Prototype:

innerJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null);

Find More Here

Hope this will help you.

Upvotes: 1

Related Questions