CODEWITHSUNDEEP
phpsymfonydoctrine-ormmany-to-manyquery-builder
Muhammad Taqi
Muhammad Taqi

Reputation: 5424

Symfony Doctrine Query For Many to Many Releationships

I have Many to Many relationship between Institutes and Courses. I want to build query that returns only the institutes list whom some courses has been assigned. I have wrote queries in this situation for one to many. but for not many to many. here is the relationships, 

class Institutes { 

     /**
     * @ORM\ManyToMany(targetEntity="Courses", inversedBy="institutes")
     * @ORM\JoinTable(name="institute_courses",
     *      joinColumns={@ORM\JoinColumn(name="institute_id", referencedColumnName="id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="course_id", referencedColumnName="id")}
     *      )
     */
    protected $courses;
}

class Courses {

    /**
     * @ORM\ManyToMany(targetEntity="Institutes", mappedBy="courses")
     */
    protected $institutes;
}

here is the query that i have written, but didn't work properly.

$repository->createQueryBuilder('s')
                        ->leftJoin('CoursesBundle:Courses','c', 'ON c.institutes = s.courses')
                        ->where('s.active = :active')
                        ->andWhere('s.verified = :active')
                        ->setParameter('active', true)
                        ->orderBy('s.name', 'ASC');

Upvotes: 1

Views: 71

Answers (2)

Terenoth
Terenoth

Reputation: 2598

This should do the trick:

$repository->createQueryBuilder('i')
    ->innerJoin('i.courses','c')
    ->where('i.active = TRUE')
    ->andWhere('i.verified = TRUE')
    ->orderBy('i.name', 'ASC');

Upvotes: 1

Shira
Shira

Reputation: 6560

You can use a JOIN as you would with other kinds of associations. The following query will find all courses which have been assigned at least to one institute:

SELECT c FROM SomeBundle:Courses c JOIN c.institutes i

You can filter the results further by adding a join condition:

SELECT c FROM SomeBundle:Courses c JOIN c.institutes i WITH i.something = :someParam

Upvotes: 0

Related Questions