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');
This should do the trick:
$repository->createQueryBuilder('i')
->innerJoin('i.courses','c')
->where('i.active = TRUE')
->andWhere('i.verified = TRUE')
->orderBy('i.name', 'ASC');