Monday, 26 August 2013

Doctrine2: Create query for ManytoMany without inversed side

Doctrine2: Create query for ManytoMany without inversed side

I'm new to Doctrine and having a hard time trying to figure out howto
write the query below with Doctrine2 in Symfony2 which gives me a list of
User objects as result.
Query description: A teacher must get a list of users that are assigned to
the courses he has been assigned to as teacher
Select * from fos_user_user as user
LEFT JOIN course_assigned_students as cas ON cas.student_id = user.id
WHERE cas.course_id IN
(SELECT cat.course_id from course_assigned_teachers where
teachers_id = 1)
GROUP BY user.id
Or similar
Select * from fos_user_user as user
LEFT JOIN course_assigned_students as cas ON cas.student_id = user.id
LEFT JOIN course_assigned_teachers as cat ON cat.course_id =
cas.course_id
WHERE cat.teachers_id = 1
GROUP BY user.id
tables:
fos_user_user: id
course_assigned_students: student_id, course_id
course_assigned_teachers: teachers_id, course_id
course: id
Course Entity
/**
* @var User $teachers
*
* @ORM\ManyToMany(targetEntity="Application\Sonata\UserBundle\Entity\User")
* @ORM\JoinTable(name="course_assigned_teachers",
* joinColumns={@ORM\JoinColumn(name="course_id",
referencedColumnName="id")},
* inverseJoinColumns={@ORM\JoinColumn(name="teachers_id",
referencedColumnName="id")}
* )
*/
protected $teachers;
/**
* @var User $students
*
* @ORM\ManyToMany(targetEntity="Application\Sonata\UserBundle\Entity\User")
* @ORM\JoinTable(name="course_assigned_students",
* joinColumns={@ORM\JoinColumn(name="course_id",
referencedColumnName="id")},
* inverseJoinColumns={@ORM\JoinColumn(name="student_id",
referencedColumnName="id")}
* )
*/
protected $students;
My problem is that I don't want my User Entity to have references to
Course, because not every application will make use of the CourseBundle.
Do I have to create CourseAssignedStudents and CourseAssignedTeachers
entities which represents the join tables?
So I can do something like:
$users = $this->getDoctrine()->getEntityManager()
->createQuery('SELECT user FROM ApplicationSonataUserBundle:User user
LEFT JOIN CourseBundle:CourseAssignedStudents cas WITH
cas.student_id = user.id
WHERE cas.course_id IN (SELECT cat.course_id FROM
CourseBundle:CourseAssignedTeachers cat where
cat.teachers_id = :uid)
GROUP BY user.id')
->setParameter('uid', $this->getUser()->getId())
->execute();

No comments:

Post a Comment