CODEWITHSUNDEEP
doctrine-orm
Jason Swett
Jason Swett

Reputation: 45164

Many-to-many query in Doctrine 2

I'm trying to figure out how to do a many-to-many query in Doctrine 2 and I can't seem to find the answer. I know precisely how I'd do it in straight SQL:

      SELECT ma.id,
             ma.name
        FROM user u
        JOIN user_media_area uma ON uma.user_id = u.id
        JOIN media_area ma ON uma.media_area_id = ma.id

How would I do this same thing with Doctrine?

Upvotes: 6

Views: 9273

Answers (2)

Cerad
Cerad

Reputation: 48893

The manual has some good examples: http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/query-builder.html

In your case it would look something like:

    // Build query
    $em = $this->getEntityManager();
    $qb = $em->createQueryBuilder();

    $qb->addSelect('user');
    $qb->addSelect('mediaArea');

    $qb->from('SomeBundle:User','user');

    $qb->leftJoin('user.userMediaArea','userMediaArea');
    $qb->leftJoin('userMediaArea.mediaArea','mediaArea');

    $query = $qb->getQuery();
    $users = $query->getResult();
    echo $users[0]->getUserMediaArea()->getName();

You did not post your entity code so I had to do some guessing on how you defined the relations. If you just have a simple ManyToMany between User and MediaArea then you can skip the UserMediaArea join. D2 will figure it out. And since you really only want MediaArea info I would actually reverse the query and do a select from MediaArea so User info would not need to be returned. But I was trying to follow your original query.

Upvotes: 5

Tuong Le
Tuong Le

Reputation: 19230

SELECT ma.id, ma.name
FROM User u 
JOIN u.media

User is the User entity, and u.media is the Media entity.

Upvotes: -1

Related Questions