Doctrine 1.2 query join many to many

Question

Say I have the following many to many with jobs and users

 Assignment:
  columns:
    job_id:
      ....
    user_id:
      ....
 relations:
    user:
      local: user_id
      foreign: id
    job:
      local: job_id
      foreign: id

is it possible to query Job and then join job.assignment so that the result returns all jobs and then also returns additional rows for each job.assignment?

Answer 1

Doctrine, when using the default Record hydration, returns objects and relations to other objects. In this mode, you can write a number of queries to get what you want, depending on what you are going to do with them. To get a list of Jobs, and grab all of their associated Assignments and Users, use:

$jobs = Doctrine_Query::create()
  ->from('Job j')
  ->leftJoin('j.Assignments a')
  ->innerJoin('a.user u')
  ->execute();
foreach($jobs as $job) {
  foreach($job->Assignments as $assignment) {
    $user = $assignment->user;
    // do something with $job and $user here
  }
}

In this case, you'll need to add the relation from Job to Assignment. If you want to do the reverse, get a list of users and join to their jobs, use:

$users = Doctrine_Query::create()
  ->from('User u')
  ->leftJoin('u.Assignments a')
  ->innerJoin('a.job j')
  ->execute();
foreach($users as $user) {
  foreach($user->Assignments as $assignment) {
    $job = $assignment->job;
    // do something with $job and $user here
  }
}

In this case, you'll need the relation from User to Assignment.

You can even start with Assignments, if that is what you're looking for:

$assignments = Doctrine_Query::create()
  ->from('Assignment a')
  ->innerJoin('a.user u')
  ->innerJoin('a.job j')
  ->execute();
foreach($assignments as $assignment) {
    $user = $assignment->user;
    $job = $assignment->job;
    // do something with $job and $user here
}

The first query will give you all jobs, even ones without assignments. The second, all users, also ones without assignments. The third, assignments, which will have users and jobs, if they are required.