Geek Answers Handbook

How to create a Docterine Inner Join Query in Symfony

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

USER table: columns - NICK_NAME

OVERVIEW Table: Columns - USER ID, OVERVIEW, CREATED_AT

thank

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 the following:

500 | Internal Server Error | Doctrine_Exception Couldn't find class `USER`
+4
source share
2 answers

Without using complex DQL in Symfony, you can use simple SQL. Try this feature in the controller where you want to run 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);
    }
+2
source

You must specify the name of the entity, not the name of the table in DQL, as shown below. YourBundleName:EntityName

Use it as follows:

$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');

, :

$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');

, Inner join :

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

internal Doctrine2 :

-

$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($join, $alias, $conditionType = null, $condition = null, $indexBy = null);

, .

+1

More articles:


All Articles