Doctrine2: Create query for ManytoMany without inversed side

Question

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();

Answer 1

Unfortunately, whilst it does tend to carry on working anyway in some cases, both sides of the relationship should always be defined - you do get warnings in the Doctrine console if it sees that they are not.

The owning side of a bidirectional relationship must refer to its inverse side by use of the inversedBy attribute of the OneToOne, ManyToOne, or ManyToMany mapping declaration. The inversedBy attribute designates the field in the entity that is the inverse side of the relationship.

http://docs.doctrine-project.org/en/2.0.x/reference/association-mapping.html#owning-side-and-inverse-side

I would say however that the problem you raise is a larger one anyway, but there are some solutions:

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.

Yes, which is exactly why your generic user entity should not relate to a course at all. If you want truly re-usable bundles (you should), you need to take on a different strategy, allowing you to abstract relationships such as this.

Lets say you have a UserBundle, with a User entity in it, this should only ever relate directly to entities that you intend to be present in all of your applications, there's probably not a lot of these, so you can expect it to be a lightweight description of the user, name, username, password, email etc.

For a specific application where you have additional relationships you want to add in, you can achieve this using one or more of the following concepts, that whilst a tad cumbersome in some respects, should improve your various codebases structurally, as well as allowing you to achieve your end goal(s).

• Bundle/Object inheritance - you will need to extend your core entity classes so you can add in additional column and relation annotations, properties and methods, you could do this in a project namespace of the same name e.g. ProjectName/UserBundle extends CompanyName/UserBundle or if more appropriate, ProjectName/AppBundle or ProjectName/SchoolBundle, this is more a design choice I would gauge based on what you intend to relate your entity to.

• Single table inheritence - this adds a discriminator column field to the database table that describes which entity the particular row represents, and Doctrine will hydrate the correct object type accordingly. The downside of discriminator mapping is that the mapping is defined on the parent class, which would involve describing your course oritentated user entity in your parent user bundle - I got around this problem with code derived from this Gist, which allows you to define these mappings in parameters - meaning your parent class only need declare itself as discriminator mapped, and itself as one of the possible mappings; which means it can be extended or used in isolation.

  http://docs.doctrine-project.org/en/2.0.x/reference/inheritance-mapping.html#single-table-inheritance

• Resolve target entity - I have less experience with this, but it may meet your needs, I think it may even be necessary in some cases when you are using discriminator mapping. In short, it involves defining relationships using interface classes (or parent classes, I think) for your models and Doctrine will resolve these interfaces to the appropriate entity classes.

  http://symfony.com/doc/current/cookbook/doctrine/resolve_target_entity.html

These are quite significant architectural decisions, and to be honest, need some playing with to get the feel for what they're all about and how to do this best for your particular needs. Single table inheritance is probably the main concept to get your head around, and how to do it in a logical way across bundles in your application.