title: Doctrine2 - select from multiple tables with no direct relations
body: <p>This question is about selecting data from multiple tables, joins, Doctrine2, ResultSetMapping, DQL and such stuff.</p>
  
  <p>I have 4 tables:</p>
  
  <ul>
  <li>user</li>
  <li>contact</li>
  <li>contact_phone</li>
  <li>call</li>
  </ul>
  
  <p>With relations as shown on the image: <a href="http://i.stack.imgur.com/762Jw.png" rel="nofollow">http://i.stack.imgur.com/762Jw.png</a></p>
  
  <p>Every user can have many contacts, each contact can have many phones and each user can have many calls to/from his contacts. Just like in the real world... I've limited the number of fields in each table just for clarity.</p>
  
  <p>So my problem is that I don't know how exactly to map call numbers to contact names when showing a list of calls for a specific user.</p>
  
  <p>If I want to list all calls of user <em>1</em> I do:</p>
  
  <pre><code>    $callRepository = $this-&gt;getDoctrine()-&gt;getRepository('MyBundle:Call');
      $calls = $callRepository-&gt;findAll(array('user' =&gt; 1));
  </code></pre>
  
  <p>But this will give me just the list of all calls for this user and will not associate number (call.number) with names (contact.name).</p>
  
  <p>I can achieve what I want with plain SQL with this query:</p>
  
  <pre><code>SELECT
      c.number,
      contact.name
  FROM
       `call` c 
  JOIN contact_phone cp ON 
      cp.number = c.number 
  JOIN contact ON 
      contact.id = cp.contact_id 
  WHERE
      c.user_id = contact.user_id
      AND c.user_id = 1
  </code></pre>
  
  <p>Please note that I don't want to select all calls (with SQL) and then map numbers to names with another query from the PHP layer because this way I won't be able to search the calls by name for example.</p>
  
  <p>I was thinking that ResultSetMapping could help me in this case but I have no luck putting the SQL query and the ResultSetMapping together. </p>
  
  <p>Please help,
  Thanks!</p>